Sql subquery poor perf - Need to rewrite to optimise

Question

Post reply

Sql subquery poor perf - Need to rewrite to optimise

SQL Bee

Old Hand

Points: 367
More actions
November 4, 2022 at 5:50 pm

#4108788

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Why are all the columns being selected via subqueries rather than joins?

ratbak Ten Centuries Points: 1358 More actions · Answer 2

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?

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

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)

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

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

How to post data/code on a forum to get the best help.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

Eirikur Eiriksson

SSC Guru

Points: 182911

November 5, 2022 at 1:24 pm

#4109038

Quick thought!

😎

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

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.PolNr

Ships 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)