Wierd query performance behaviour. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Wierd query performance behaviour.

I have a query I am trying to optimise and I’m having a bit of trouble trying to figure out some wierd behaviour. The query was originally generated by a dynamic query generator I have built which works quite well for ad hoc searches through our data, so well in fact that the other develoers are using it instead of rolling their own queries. Not a problem in and of itself, though they are just relying on it being fast and accurate – which it generally is, though the performance can vary depending on the make-up of the data they are looking at. In an effort to optimise one of the reports we have (which is executed every 10 minutes) I am taking the dynamically generated query and tweaking it to produce a custom query for this report only. I have got the time taken down from > 10 seconds to about 3, yet when I apply one further "obvious" optimisation, it goes bad. Real bad. The (mostly) optimised query looks like this: SELECT DISTINCT R.ResourceID AS EntityID, R.Description,
MAX(CASE RC.CharacteristicID WHEN 1 THEN RC.Value END) AS ‘AVIATION FUEL TYPE’,
MAX(CASE RC.CharacteristicID WHEN 2 THEN RC.Value END) AS ‘FIXED WING MAKE’,
MAX(CASE RC.CharacteristicID WHEN 4 THEN RC.Value END) AS ‘FIXED WING MODEL’,
MAX(CASE RC.CharacteristicID WHEN 590 THEN RC.Value END) AS ‘RATS ID’,
MAX(CASE RC.CharacteristicID WHEN 9 THEN RC.Value END) AS ‘ENDURANCE’,
MAX(CASE RC.CharacteristicID WHEN 10 THEN RC.Value END) AS ‘PASSENGER’,
MAX(CASE RC.CharacteristicID WHEN 14 THEN RC.Value END) AS ‘DEPT AERIAL’,
MAX(CASE RC.CharacteristicID WHEN 11 THEN RC.Value END) AS ‘FUEL CONSUMPTION’,
MAX(CASE RC.CharacteristicID WHEN 15 THEN RC.Value END) AS ‘DEPT RADIO’,
MAX(CASE RC.CharacteristicID WHEN 16 THEN RC.Value END) AS ‘CALL SIGN’,
MAX(CASE RC.CharacteristicID WHEN 18 THEN RC.Value END) AS ‘ENGINE’,
MAX(CASE RC.CharacteristicID WHEN 19 THEN RC.Value END) AS ‘WING’,
MAX(CASE RC.CharacteristicID WHEN 20 THEN RC.Value END) AS ‘AIRCRAFT HIRE’,
MAX(CASE RC.CharacteristicID WHEN 26 THEN RC.Value END) AS ‘FIXED WING CLASS’,
MAX(CASE RC.CharacteristicID WHEN 27 THEN RC.Value END) AS ‘AERONAUTICAL RADIO’,
MAX(CASE RC.CharacteristicID WHEN 639 THEN RC.Value END) AS ‘RATS DISPLAY’,
MAX(CASE RC.CharacteristicID WHEN 365 THEN RC.Value END) AS ‘STANDBY’,
MAX(CASE RC.CharacteristicID WHEN 366 THEN RC.Value END) AS ‘HIRE WET’,
MAX(CASE RC.CharacteristicID WHEN 21 THEN RC.Value END) AS ‘HOPPER CAPACITY’,
MAX(CASE RC.CharacteristicID WHEN 367 THEN RC.Value END) AS ‘HIRE DRY’,
MAX(CASE RC.CharacteristicID WHEN 7 THEN RC.Value END) AS ‘REGISTRATION’,
MAX(CASE RC.CharacteristicID WHEN 1070 THEN RC.Value END) AS ‘AIRCRAFT CONTRACT TYPE’,
MAX(CASE RC.CharacteristicID WHEN 1072 THEN RC.Value END) AS ‘AIRCRAFT STANDBY TIME’,
MAX(CASE RC.CharacteristicID WHEN 1071 THEN RC.Value END) AS ‘AIRCRAFT AVAILABILITY’,
MAX(CASE RC.CharacteristicID WHEN 88 THEN RC.Value END) AS ‘COMMENT’,
MAX(CASE R2.ResRelTypeID WHEN 33 THEN R2.Description END) AS ‘HAS AS BASE LOCATION’,
MAX(CASE R2.ResRelTypeID WHEN 5 THEN R2.Description END) AS ‘IS OWNED BY’,
MAX(CASE R2.ResRelTypeID WHEN 42 THEN R2.Description END) AS ‘HAS ALLOCATION OF’,
‘FIXED WING AIRCRAFT’ AS ResourceTypeName, 3 AS ProgCode
FROM vwResource R
INNER JOIN vwResourceCharacteristic RC ON RC.ResourceID = R.ResourceID AND R.ResourceTypeID = 3
AND RC.CharacteristicID IN (SELECT CharacteristicID
FROM vwCharacteristic
WHERE Name IN ( ‘AVIATION FUEL TYPE’, …
‘AIRCRAFT AVAILABILITY’, ‘COMMENT’)) LEFT JOIN (SELECT R.Description, RR.ResourceID1, RR.ResRelTypeID
FROM vwResourceRelationship RR
INNER JOIN vwResource R ON RR.ResourceID2 = R.ResourceID
AND RR.ResRelTypeID IN (42, 33, 5)
AND RR.ResourceTypeID1 = 3
AND RR.ResourceTypeID2 IN (44, 65, 220)
AND R.ResourceTypeID IN (44, 65, 220)
) R2 ON R2.ResourceID1 = R.ResourceID GROUP BY R.ResourceID, R.Description ORDER BY R.Description
If I remove the "RC.CharacteristicID IN (SELECT…" completely (the data is guaranteed to only have those characteristics) the query takes > 1 minute to execute. And when I replace the vwResourceCharacteristic join with the following the performance is just as bad as removing the bit above. The list of numbers is the list if CharacteristicIDs returned by the sub-query. INNER JOIN vwResourceCharacteristic RC ON RC.ResourceID = R.ResourceID AND R.ResourceTypeID = 3
AND RC.CharacteristicID IN (27, 1071, … 590, 7, 365, 19)
I would have thought that replacing a sub-query with a hard-coded list of known numbers would be faster, but obviously not. I also would have thought that removing unneccessary tables from a query would help, but my knowledge of SQLs query optimiser is unfortunately very slim at this stage. Using [bold]SET STATISTICS IO ON[/bold] with the original query I get: Table ‘tblResource’. Scan count 511, logical reads 1927, physical reads 0, read-ahead reads 0.
Table ‘tblResourceRelationship’. Scan count 3, logical reads 11, physical reads 0, read-ahead reads 0.
Table ‘tblCharacteristic’. Scan count 24279, logical reads 50154, physical reads 0, read-ahead reads 0.
Table ‘tblResourceCharacteristic’. Scan count 130, logical reads 500, physical reads 0, read-ahead reads 0. And for the version WITHOUT the AND RC.CharacteristicID IN … I get: Table ‘tblResource’. Scan count 980731, logical reads 2950275, physical reads 0, read-ahead reads 0.
Table ‘tblResourceRelationship’. Scan count 5769, logical reads 21153, physical reads 0, read-ahead reads 0.
Table ‘tblResourceCharacteristic’. Scan count 130, logical reads 500, physical reads 0, read-ahead reads 0. I can see where the performance goes (too many IOs on tblResource) but not why or how to fix it. Any ideas?
Generally speaking, using the IN clause should be avoided, and it is one of the less efficient ways to query data. But to really understand what is going on with your query, and to pinpoint the problem, what you need to do is to take a close look at the excution plan of the queries involved. Often, you can very quickly determine that an index is missing, or if a slow type of join is being used, etc. While the statistics I/O is a good start to compare two queries, often you need the additional information available from an execution plan to actually pinpoint the problem. For more information on the use of the IN clause, see this URL:http://www.sql-server-performance.com/transact_sql.asp For more information on understanding execution plans, see this URL:http://www.sql-server-performance.com/query_execution_plan_analysis.asp If you like, post the text query plans for both of these queries, and perhaps I, or someone else, will be able to help you pinpoint the specific differences between them.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>