Issue with a slow Query

  • Hi all,

    The attached script is taking some 20 mins to fetch some 1.5 million records. Our system times out in 11 mins, means this script will never succeed.

    I have tried a few tweaks like splitting OR, adding a few indexes but that is also not helping, and I'm not finding any way to rewrite the script.

    I have attached its execution plan also for your review. This script is used for insertion in some other table.

    Any sort of help will be highly appreciated.

    Regards.

  • 1) ORs are killers

    2) Just spooling 1.5M rows back to a client (especially if that client does row-by-row fetching) can alone be a cause of SIGNIFICANT slowness. Put the output columns into variables and run the SELECT to actually see how long SQL Server takes to process the data.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • In addition to the OR, we're doing NOT EQUALS. Between the two you're pretty much guaranteed scans on your indexes.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks for review.

    Yes you are right, these ORs are main issue. Earlier we had few more ORs, and by removing them I managed to get some 40-45% performance gain (I split the script and used UNION).

    Now I'm not finding any way to remove these remaining ORs or to rewrite the script. Thats why I have put up the issue here, and I'm sure you intelligent brains can definitely suggest something.

    Regards.

  • -- You could try breaking up the query. Here's one way, there are a few possibilities:

    SELECT

    [wck.wck_id] = wck.wck_id,

    [pd.mode_id] = pd.mode_id,

    [p.person_id] = p.person_id,

    [wck.worker_id] = wck.worker_id,

    [wck.worker_order_id] = wck.worker_order_id,

    [p.access_site] = p.access_site,

    [p.access_bu] = p.access_bu

    INTO #person_wck_item_dashboard

    FROM dbo.person p

    INNER JOIN dbo.wck_item wck

    ON wck.buyer_id = p.company_id

    AND wck.worker_id <> ''

    INNER JOIN offline_DB.dbo.dashboard pd

    ON pd.[object_id] = wck.wck_id

    AND pd.mode_id IN (870, 1350)

    AND pd.activity_id = 10179

    WHERE p.role_id <> '5'

    AND NOT (p.access_site = 'ALL' AND p.access_bu = 'ALL' AND p.access_cc = 'ALL')

    AND p.flag_access = 1

    -- You may need an index or two on the temp table #person_wck_item_dashboard

    -- try a clustered index on [p.person_id]

    SELECT

    [object_id] = [wck.wck_id],

    mode_id = [pd.mode_id],

    person_id = [p.person_id]

    FROM #person_wck_item_dashboard pwd

    LEFT JOIN dbo.worker w

    on w.worker_id = [wck.worker_id]

    LEFT JOIN dbo.worker_order wo

    on wo.worker_order_id = [wck.worker_order_id]

    LEFT JOIN dbo.job_enquiry jp

    on jp.job_enquiry_id = wo.job_enquiry_id

    WHERE

    NOT EXISTS (

    SELECT1

    FROMoffline_DB.dbo.dashboard_visible pdvs

    WHEREpdvs.object_id = [wck.wck_id] -- outer reference wck

    AND pdvs.mode_id = [pd.mode_id] -- outer reference pd

    AND pdvs.person_id = [p.person_id]) -- outer reference p

    --AND (1 = 1

    AND ((

    ([p.access_site] = 'ALL' OR EXISTS (

    SELECT 'x'

    FROM dbo.person_site ps

    WHERE ps.site_id = wo.site_id -- outer reference wo

    AND ps.person_id = [p.person_id])) -- outer reference p

    AND ([p.access_bu] = 'ALL' OR EXISTS (

    SELECT 'x'

    FROM dbo.person_bu pb

    WHERE pb.bu_id = wo.bu_id -- outer reference wo

    AND pb.person_id = [p.person_id])) -- outer reference p

    AND EXISTS (

    SELECT 'x'

    FROM dbo.worker_cc wcc

    INNER JOIN dbo.person_cc pcc

    ON wcc.cost_center_id = pcc.cost_center_id

    WHERE wcc.worker_id = w.worker_id -- outer reference w

    AND pcc.person_id = [p.person_id]) -- outer reference p

    ))

    --)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 5 posts - 1 through 5 (of 5 total)

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