How to Speed up the Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to Speed up the Query

Author

Topic

sagitariusmzi
Posting Yak Master

113 Posts

Posted - 2009-11-16 : 06:50:42

Hi,

How to speed up this Query

SELECT DISTINCT YEAR(vch_date) FROM Voucher

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2009-11-16 : 12:32:42

how many rows in the table? I'm assuming this is doing a table or clustered index scan on you since you're using a function. How about a computed column on the table that holds the year. Index the column, see how it goes. You might also try group by instead of distinct. not sure if that would make a difference, you'd have to check the query plan.

Mike
"oh, that monkey is going to pay"

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 13:31:29

What does this give you (post the result)

SELECT COUNT(DISTINCT YEAR(vch_date)) FROM Voucher

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 13:32:24

quote:
Originally posted by mfemenel

how many rows in the table? I'm assuming this is doing a table or clustered index scan on you since you're using a function. How about a computed column on the table that holds the year. Index the column, see how it goes. You might also try group by instead of distinct. not sure if that would make a difference, you'd have to check the query plan.

Mike
"oh, that monkey is going to pay"

Well is there an index on vch_date????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Pradip
Starting Member

32 Posts

Posted - 2009-11-17 : 05:51:44

Mike, I think Computed column will be better solution, but indexing computed column will not make much difference as same years will repeat no of times, as it is voucher table. also one can try group by but I think will not make any difference.

www.silicus.com

pradipjain

sagitariusmzi
Posting Yak Master

113 Posts

Posted - 2009-11-17 : 06:32:01

The query gives this result

2001
2002
2003
2005
2004
2006
2007
2009
2008

Yes there is an index on vch_date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources