Sql subquery poor perf - Need to rewrite to optimise
-
I have a sql query as below which takes endless time to execute. Any help in rewriting this query to better way would be appreciated greatly.
Select PolNr
, (select value from tblPolSpec d
where m.PolNr=d.PolNr
and d. Category='Holder' ) as HolderName
, (select value from tblPolSpec d
where m.PolNr=d.PolNr
and d. Category='Status' ) as PolStatus
--there are around 20 such similar subqueries --for the rest of the select fields
From tblPolMaster m
Please help to rewrite this query in better way.
Thanks in advance.
-
Why are all the columns being selected via subqueries rather than joins?
-
Can you provide an actual execution plan?
How many rows are in tblPolMaster? tblPolSpec?
How many rows are returned by the query?
Is tblPolSpec a generic Entity-Attribute Value lookup table? Do you have indexes to support those joins?
Would there be other queries also executing that access either/both of those tables? What is the transaction isolation level?
-
The original code looks like a "poor man's CROSSTAB". Try the following true CROSSTAB instead.
SELECT PolNr = m.PolNr
,HolderName = MAX(IIF(d.Category = 'Holder',[value],''))
,PolStatus = MAX(IIF(d.Category = 'Status',[value],''))
... etc for the "20 such similar subqueries" ...
FROM dbo.tblPolMaster m
LEFT JOIN dbo.PolSpec s ON d.PolNr = m.PolNr
GROUP BY m.PolNr
ORDER BY PolNr --May need m.PolNr instead if you get an "abiguity" error.
;This one should make only 1 scan against the EAV table that you call "PolSpec" instead of what I think it may currently be doing (especially if indexes are missing). It really needs a redesign of the tblPolMaster table to follow the general advice of ""The key, the whole key, and nothing but the key, so help me Codd."
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Assuming that all of the other 20 subqueries are based on the same pattern in tblPolSpec, you will probably get better performance if you use a CROSS-TAB query like this
SELECT m.PolNr
, MAX( CASE WHEN d.Category = 'Holder' THEN d.value END ) AS HolderName
, MAX( CASE WHEN d.Category = 'Status' THEN d.value END ) AS PolStatus
-- Use this pattern to get the rest of the values
-- , MAX( CASE WHEN d.Category = 'xxx' THEN d.value END ) AS FieldX
-- , MAX( CASE WHEN d.Category = 'zzz' THEN d.value END ) AS FieldZ
FROM tblPolMaster AS m
INNER JOIN tblPolSpec AS d
ON m.PolNr = d.PolNr
WHERE d.Category IN ( 'Holder', 'Status', 'xxx', 'zzz' ) -- Add each Category as needed
GROUP BY m.PolNr -
Quick thought!
- DesNorton wrote:
Assuming that all of the other 20 subqueries are based on the same pattern in tblPolSpec, you will probably get better performance if you use a CROSS-TAB query like this
SELECT m.PolNr
, MAX( CASE WHEN d.Category = 'Holder' THEN d.value END ) AS HolderName
, MAX( CASE WHEN d.Category = 'Status' THEN d.value END ) AS PolStatus
-- Use this pattern to get the rest of the values
-- , MAX( CASE WHEN d.Category = 'xxx' THEN d.value END ) AS FieldX
-- , MAX( CASE WHEN d.Category = 'zzz' THEN d.value END ) AS FieldZ
FROM tblPolMaster AS m
INNER JOIN tblPolSpec AS d
ON m.PolNr = d.PolNr
WHERE d.Category IN ( 'Holder', 'Status', 'xxx', 'zzz' ) -- Add each Category as needed
GROUP BY m.PolNrShips passing in the night. Your WHERE clause is something that I forgot.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply