SQL Server Performance

Joins Performance Problem

Discussion in 'General DBA Questions' started by alimmia, Jun 6, 2006.

  1. alimmia New Member

    Hello,

    It is taking too long to run the following query:

    Note: I have indexes on all of the columns in conditions. My temp db size is 18 GB. Pds_txn table size is 165 GB.

    The execution plan showing:

    Table pool/easer spool operation –

    Row Count: 9 M
    Disk i/o: 11k
    Row size: 1089
    Estimated cost: 11 k (57%)
    CPU cost: 3.3
    Sub tree cost: 20 K

    Any help/hint will be appreciated.

    Thanks,
    Alim
    -----------------------------------------------------------------------------------------------------------


    FROM
    dbo.pds_txn T1

    INNER JOIN
    dbo.GROUPS T2 ON
    T1.GROUP_ID = T2.ID_200
    INNER JOIN
    dbo.DIVISIONS T3 ON
    T1.DIVISION = T3.ID_102
    INNER JOIN
    dbo.BILLING_AREAS T4 ON
    T1.BILLING_AREA = T4.ID_202
    INNER JOIN
    dbo.PROVIDERS T6 ON
    T1.PROVIDER = T6.ID_3
    INNER JOIN
    dbo.LOCATIONS T7 ON
    T1.LOCATION = T7.ID_100
    INNER JOIN
    dbo.PROCEDURES T8 ON
    T1.[PROCEDURE] = T8.ID_1
    INNER JOIN
    dbo.FSC T9 ON
    T1.ORIG_FSC = T9.ID_19
    INNER JOIN
    dbo.DIAGNOSIS T10 ON
    T1.TXN_DX_1 = T10.ID_36
    INNER JOIN
    dbo.pds_invoice T11 ON
    T1.INVOICE_NUM = T11.INVOICE_NUM AND
    T1.GROUP_ID = T11.GROUP_ID
    LEFT OUTER JOIN
    dbo.PROVIDERS T6A ON
    T11.PERFORMING_PHYS = T6A.ID_3

    WHERE
    T1.POSTING_PD_DTE >= '05/01/2003' AND
    T1.PAY_CODE = 21 AND
    T2.EXCLUSION_FLAG = 0 AND
    T3.DIV_NUM <> '2901'




    Alim Mia
  2. joechang New Member

    this query is probably missing an index, resulting in the spool
    what table feeds into the spool
    and what indexes are on that table
  3. merrillaldrich New Member

    If I had to take a wild guess - maybe T1.PAY_CODE and T2.EXCLUSION_FLAG bear examining? Are they low-selectivity columns (with only a few different values)?

    Those might be causing table scans.
  4. alimmia New Member

    Thanks guys. You right, there was a missing index on the EXCLUSION_FLAG.


    Alim Mia
  5. merrillaldrich New Member

    Exclusion flag -- just adding an index might not help, since it seems like that would have very low selectivity. Did adding an index on that help?
  6. alimmia New Member

    No. The index did not help since i already have an index in another column that is in the join for the same table. My previous email was for giving you guys credits for your comments.
    We fixed the problem. It was not the index issue. There was a function that updating a column in the select statement that causing the problem. We removed the function from the select, then updated the column seperately after creating the table.

    Thanks,

    Alim Mia

Share This Page