SELECT query running very slow - it's crawling!

  • I have the following query

    SELECT distinct pt.ptno,t.suspense

    From lnksvr1.db1.dbo.Table1 pt Inner Join [271_Ineligible] t on pt.ptno = t.ptno

    Where t.uploaded=0 AND t.applied = '2' AND t.hcode in (350, 351 , 352)

    AND t.category in (1,2)

    AND pt.PTNO not in (SELECT ptno

    FROM lnksvr1.db1.dbo.Comments c

    WHERE c.ptno = t.ptno

    AND left(c.comment_code, 3) = '220'

    AND t.hcode in (350, 351 , 352) )

    **lnksvr1 is the linked server

    There are 1572367 records in the 271_Ineligible table and to return 403 rows it takes about 2 1/2 minutes. Is it too slow or pretty normal?

    I have checked the average fragmantation of indexes on these tables and all are below 30%

    Can you please help me to see how I can speed up the query? Any help would be appreciated.

  • Please post the actual execution plan.

    Also have you tried reversing the query to see if it runs faster from the remote server? It might be faster to get the results from there and push them to the 2nd server.

  • Not sure if I uploaded the image right...

  • Not a picture of the execution plan. The execution plan itself. See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry, will have to wait until tomorrow's process because the data got updated by another process, hence will not return the same result. I will post the execution plan tomorow morning as we will have another batch of data coming in that table tonight. Thank you for your assistance.

  • I don't know about your indexs, but applying a function to a column makes it so that predicate is not SARGable.

    You might try chaning:

    AND left(c.comment_code, 3) = '220'

    to something like:

    AND c.comment_code LIKE '220%'

  • I could have done hat but the records in the comments a table are as follows

    220- We receieved the data

    219 - Patient match found

    218 - This is only going to be hard 220 days left

    blah -blah..blah...blah....

    I need to select only those rows where it starts with 220. I do not want the 3rd row to be selected. The '%Like' would select the third row as well.

    Thank you for replying.

  • msharma-393915 (5/26/2011)


    I could have done hat but the records in the comments a table are as follows

    220- We receieved the data

    219 - Patient match found

    218 - This is only going to be hard 220 days left

    blah -blah..blah...blah....

    I need to select only those rows where it starts with 220. I do not want the 3rd row to be selected. The '%Like' would select the third row as well.

    Thank you for replying.

    He wrote LIKE '220%' how would 218 be returned by that filter??

  • Try Ninja's suggestion and run it against the linked server itself, and you can also try it using OPENQUERY

    For instance:

    SELECT

    DISTINCT pt.ptno,t.suspense

    FROM OPENQUERY(lnksvr1, '

    SELECT pt.ptno,t.suspense

    FROM

    db1.dbo.Table1 pt

    INNER JOIN db1.dbo.[271_Ineligible] t ON

    pt.ptno = t.ptno

    WHERE

    t.uploaded = 0

    AND t.applied = ''2''

    AND t.hcode IN (350, 351, 352)

    AND t.category IN (1,2)

    AND pt.PTNO NOT IN (

    SELECT ptno

    FROM db1.dbo.Comments c

    WHERE c.ptno = t.ptno

    AND left(c.comment_code, 3) = ''220''

    AND t.hcode IN (350, 351, 352)) ')

    If that doesn't save you much time you could try pulling two seperate queries using the OPENQUERY and inserting those result-sets into 2 seperate temporary tables, then SELECTing the DISTINCT values from them with a simple JOIN or something.

    Just throwing something out there

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • @ninja : My bad ..overlooked where the '%' was ..yes will give it a try. Thanks

    @mydoggiejessie : Definitely will give it a shot.

  • @ninja : My bad ..overlooked where the '%' was ..yes will give it a try. Thanks

    @mydoggiejessie : Definitely will give it a shot.

  • Can you please review my query ?

    This is what I am trying to do.

    I have a TABLE1 and TABLE3 on svr1 and TABLE2 on svr2. Now I am trying to do a update on suspense fiield of srvr1.TABLE1 with a join on srvr2.Table2 and ptno not in svr1.Table3 - I am trying to achieve this by the use of openquery as suggested above

    --Running this query on Srvr2

    Create #tmpInEligible1(ptno int)

    SELECT distinct ptno FROM

    OPENQUERY(lnksrv1,'SELECT t1.ptno FROM db1.dbo.TABLE1 t1

    INNER JOIN lnksrvr2.db2.dbo.TABLE2 t2

    on t1.ptno = t2.ptno

    WHERE t2.uploaded=0 AND t2.applied = 2 and t1.hosp in (1,2,3)

    AND t2.category in (1,2)' )

    CREATE #tmpInEligible2(ptno int)

    SELECT distinct ptno FROM

    OPENQUERY(lnksrv1,'SELECT c.ptno FROM db1.dbo.TABLE3 t3

    INNER JOIN lnksrvr2.db2.dbo.TABLE2 t2

    ON t3.ptno = t2.ptno

    WHERE t2.uploaded=0 AND t2.applied = 2 and t2.hosp in (1,2,3)

    AND t2.category in (1,2)

    AND t3.comment_code like ''220%'''

    )

    UPDATE p

    SET t1.suspense = tmp1.suspense

    FROM

    OPENQUERY(lnksrv1,'SELECT suspense FROM db1.dbo.Table1 ') t1

    INNER JOIN #tmpInEligible1 tmp1

    ON t1.ptno = tmp1.ptno

    WHERE not exists (SELECT ptno FROM #tmpInEligible2)

    DROP table #tmpInEligible1

    DROP table #tmpInEligible2

    Thanks a lot for the help!

Viewing 13 posts - 1 through 12 (of 12 total)

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