SQL Server Performance Forum – Threads Archive
PLEASE HELP ME TO OPTIMIZE QUERY
Hello friends, I#%92m facing performance related problem while running following query on SQL Server 2000. This query is basically used to find last location of each unit that are passed. Here I am passing data like “‘26′,’27’,’28’,’29’,’30’,’31’,’32’,’33’,’34’,’35’,’36’,’37’,’38’,’39’,’40’,’41’,’42’,’43’,’44’,’45’,’46’,’47’,’48’,’49’,’50’" in @Units variable. But it takes too much time and I don#%92t get output. Table is having around 5 Million records. Query: SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ‘ ‘ + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ‘ ‘ + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryidFROM tblUnit INNER JOIN
tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID
WHERE tblUnitHistory.UnitHistoryDate
IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (‘ + @Units + ‘) GROUP BY tblUnitHistory.UnitID)
AND tblUnit.UnitID in (‘ + @Units + ‘)
ORDER BY tblUnit.UnitID
Table Structure: UnitHistoryIDint Primary Key
UnitIDint
Locationvarchar(200)
Latitudedecimal9
Longitudedecimal9
Speeddecimal 5
BatteryVoltagedecimal 5
ReasonCodeint
DistanceFromLastLocationdecimal 9
UnitHistoryDatedatetime Indexes: 1. Clustered Index on Column UnitID
2. Non-clustered Index on Column UnitHistoryDate
3. Non-clustered Index on Column UnitHistoryID Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance. Regards,
Raj
Check this out:
http://www.sommarskog.se/arrays-in-sql.html
http://www.sommarskog.se/dynamic_sql.html —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Your query statement looks like dynamic SQL, only without the varchar variable in which you concatenate the query statement. What you now have will filter for UnitID values that exist in the literal expression between the single quotes. That literal expression is ‘ + @Units + ‘. This literal expression does not list any valid UnitId value, so no rows will be returned. The basic syntax for dynamic SQL is: DECLARE @SQL VARCHAR(8000) SET @SQL = ‘SELECT UnitId FROM tblUnit WHERE UnitId IN (‘ + @Units + ‘)’
EXEC (@SQL) The use of an IN clause like this will often lead to poor performance. To avoid the dynamic SQL, you can also parse the list of values, and insert them one-by-one into a table variable with a PK on the UnitID column. Now add the table variable with an inner join to your main query, and performance might improve. Are you aware that your subquery does not match the individual UnitId to its own max UnitHistoryDate? It looks up the max UnitHistoryDate for all of the listed UnitId’s, then will return only the rows where the UnitHistoryDate is equal to the max date for the whole group.
Hi Adriaan, First of all many many thanks for your quick reply. Can u please make corrections in query to make it faster? Thanks,
Raj
quote:Originally posted by Adriaan
Your query statement looks like dynamic SQL, only without the varchar variable in which you concatenate the query statement.
What you now have will filter for UnitID values that exist in the literal expression between the single quotes. That literal expression is ‘ + @Units + ‘. This literal expression does not list any valid UnitId value, so no rows will be returned.
The basic syntax for dynamic SQL is:
DECLARE @SQL VARCHAR(8000)
SET @SQL = ‘SELECT UnitId FROM tblUnit WHERE UnitId IN (‘ + @Units + ‘)’
EXEC (@SQL) The use of an IN clause like this will often lead to poor performance. To avoid the dynamic SQL, you can also parse the list of values, and insert them one-by-one into a table variable with a PK on the UnitID column. Now add the table variable with an inner join to your main query, and performance might improve. Are you aware that your subquery does not match the individual UnitId to its own max UnitHistoryDate? It looks up the max UnitHistoryDate for all of the listed UnitId’s, then will return only the rows where the UnitHistoryDate is equal to the max date for the whole group.
EXEC (@SQL) The use of an IN clause like this will often lead to poor performance. To avoid the dynamic SQL, you can also parse the list of values, and insert them one-by-one into a table variable with a PK on the UnitID column. Now add the table variable with an inner join to your main query, and performance might improve. Are you aware that your subquery does not match the individual UnitId to its own max UnitHistoryDate? It looks up the max UnitHistoryDate for all of the listed UnitId’s, then will return only the rows where the UnitHistoryDate is equal to the max date for the whole group.
We can’t do your work for you.[<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]<br /><br />Take the suggestions and get to work with them – you will learn something, rather than just borrow a bit of knowledge.
Hi Adriaan,<br /><br />Thank you very much for your technical (and other) comments. Thank you for your positive criticism. <br /><br />Finally it worked[<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]. I used the temporary table and made the join with primary table and it works. <br /><br />Thanks,<br />Raj<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />We can’t do your work for you.[<img src=’/community/emoticons/emotion-5.gif’ alt=’‘ />]<br /><br />Take the suggestions and get to work with them – you will learn something, rather than just borrow a bit of knowledge.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
]]>