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

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

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