Query Tuning - 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

Transact-SQL (2005)

Query Tuning

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-07-07 : 13:42:06

A large table STUDENTS have a dateID column in yyyymmdd format in varchar data type

i am writing a procedure which will take monthid as parameter in format yyyymm

so i m writing the query like

select Name, sum(attendance)
from Students
where DateID/100 = @monthID --200907 for July

But performance will be degraded as it uses an expression in where condition and wont use the indexes on it.

Any work around here to avoid using expression.

Chandragupta Mourya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 13:45:44

1. Always use DATETIME/SMALLDATETIME as datatype for dates.
2. See #1.

SELECT Name, SUM(Attendance)
FROM Students
WHERE DateID > 100 * @MonthID AND DateID < 100 * (@MonthID + 1)

N 56°04'39.26"
E 12°55'05.63"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-07 : 13:45:57

did u try using LIKE?

SELECT   name, 
         Sum(attendance) 
FROM     students 
WHERE    dateid LIKE @monthID + '%' 
GROUP BY name

konark
Yak Posting Veteran

60 Posts

Posted - 2009-07-07 : 14:02:27

1) The date is varchar as designed by the architect team. I cant change that.
2) Do you think DateID > 100 * @MonthID AND DateID < 100 * (@MonthID + 1) is better than using the arithmentic on columns.

Chandragupta Mourya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 14:12:04

1. Tell architect team they need some improvement and some up-to-date skills.
2. Yes. I wouldn't have suggested it otherwise. Now you can use index on DateID column to get a SEEK.

To avoid implicit conversion, use this

SELECT Name, SUM(Attendance)
FROM Students
WHERE DateID > convert(char(8), 100 * @MonthID) AND DateID < convert(char(8), 100 * (@MonthID + 1))

N 56°04'39.26"
E 12°55'05.63"

konark
Yak Posting Veteran

60 Posts

Posted - 2009-07-07 : 14:37:07

SELECT Name, SUM(Attendance)
FROM Students
WHERE DateID > convert(char(8), 100 * @MonthID) AND DateID < convert(char(8), 100 * (@MonthID + 1))
-----------
whats the use of avoiding it internally by sql server , when you your self are explicitly converting.

Chandragupta Mourya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 15:32:09

Otherwise SQL Server might consider converting the left side of operator since it can be done to INT, since right side is INT. And this would defeat the whole purpose to get a SEEK instead of SCAN.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources