November 4, 2022 at 5:50 pm
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.
November 4, 2022 at 6:02 pm
Why are all the columns being selected via subqueries rather than joins?
November 4, 2022 at 6:05 pm
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?
November 4, 2022 at 6:14 pm
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
Change is inevitable... Change for the better is not.
November 4, 2022 at 6:16 pm
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
November 5, 2022 at 7:44 pm
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
Ships passing in the night. Your WHERE clause is something that I forgot.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply