Slow query help needed!!!!

  • I need help to optimise this query; as currently it takes a lot of time to process.

    /*This collects all employees that do not have an active copy (as of today) of themselves and that fullfil the criteria of being manager */

    SELECT e.SDS_ID as code,

    FORENAME + ' ' + SURNAME as [description]

    FROM EMPLOYEE e

    WHERE MANAGER = 1

    AND CONTRACT = 1

    AND (DATE_LASTDAY is null OR DATE_LASTDAY >= getdate())

    AND (e.LEAVER <> 1)

    AND (e.CONTR_END >=getDate() or e.CONTR_END is null)

    AND getdate() >= e.startdate

    AND not sds_id in (select oldsds_id from employee where oldsds_id is not null and date_left>=getdate())

    UNION

    /*This collects all employees that are managers and even though they have old_sdsids, they are valid as of today*/

    SELECT e.OLDSDS_ID as code,

    FORENAME + ' ' + SURNAME as [description]

    FROM EMPLOYEE e

    WHERE (MANAGER = 1 AND CONTRACT = 1 )

    AND getdate() >= e.startdate

    AND employ_ref in (select employ_ref from employee where oldsds_id is not null and date_left>=getdate())

    ORDER BY description

    Please advice

    TIA

  • The union you are doing is effectively doing a group by, getting rid of dupes. If there are not any dupes try and use union all.

  • It would greatly help to have some additional information:

    Query execution plans for the listed SQL

    DDL for the tables involved and their indexes

    The number of rows in each table

     

    When you say "a long time", how long is that, exactly. 


    And then again, I might be wrong ...
    David Webb

  • What happens if you change the NOT IN to a LEFT OUTER JOIN in the first query and the IN to a INNER JOIN in the second query?  Hopefully my syntax is correct. 

    SELECT e.SDS_ID as code,

    FORENAME + ' ' + SURNAME as [description]

    FROM EMPLOYEE e LEFT OUTER JOIN (select oldsds_id from employee where oldsds_id is not null and date_left>=getdate()) a

    ON e.sds_id=a.oldsds_id

    WHERE MANAGER = 1

    AND CONTRACT = 1

    AND (DATE_LASTDAY is null OR DATE_LASTDAY >= getdate())

    AND (e.LEAVER <> 1)

    AND (e.CONTR_END >=getDate() or e.CONTR_END is null)

    AND getdate() >= e.startdate

    AND a.oldsds_id IS NULL

    UNION

    /*This collects all employees that are managers and even though they have old_sdsids, they are valid as of today*/

    SELECT e.OLDSDS_ID as code,

    FORENAME + ' ' + SURNAME as [description]

    FROM EMPLOYEE e INNER JOIN (select employ_ref from employee where oldsds_id is not null and date_left>=getdate()) a

    ON e.employ_ref=a.employ_ref

    WHERE (MANAGER = 1 AND CONTRACT = 1 )

    AND getdate() >= e.startdate

    AND a.employ_ref IS NOT NULL

    ORDER BY description

    You may also see if using ISNULL(DATE_LASTDAY,'1/1/4000')>GETDATE() helps.

    Agree with David, the DDL of the tables would be helpful.

    Brian

  • Changing the NOT IN to NOT Exists should help.

    ...AND not sds_id in (select oldsds_id from employee where oldsds_id is not null and date_left>=getdate())

    Should be:

    ...And Not Exists (select oldsds_id from employee where oldsds_id = e.oldsds And oldsds_id is not null and date_left>=getdate())

    When using Not Exists the sub select stops processing when the first matching record is found.  Using Not In the sub select may return 1,000 rows and then the IN comparison is processed.

    You will also gain by changing this:

    ...AND employ_ref in (select employ_ref from employee where oldsds_id is not null and date_left>=getdate())

    to an ...And Exist (Select....)

    With all due credit to Remi who showed me the error of my ways

  • If you go with my suggestion above,

    ...And Not Exists (select oldsds_id from employee where oldsds_id = e.oldsds And oldsds_id is not null and date_left>=getdate())

    It looks like you can get rid of the And oldsds_id is not null since the oldssds_id = e.oldsds should be false if either or both is null.

     

  • Your code uses the getdate() funciton 6 times. You could DECLARE a token for the current date and time at the start of the function and then replace the 6 calls to getdate() in your code with the token. Unless you need absolute accuracy or as close as you can get. BOL states that T-SQL date and time functions have an accuracy of one three-hundredth of a second (about 3.33 milliseconds). Eliminating 5 calls to getdate() should give you a boost.

    You could also gain some speed from rearranging the order of your OR logic. SQL reads logical AND/OR statements from left to right and terminates the read as soon as it encounters a statement that meets the requirements. The value that you expect the most often should always be the first statement. You could select a random sample of your data to determine the relative frequency of each expected value ie. has data or is null 

    HTH Mike

  • When you say "a long time", how long is that, exactly

    Dave I have been asking myself that question since Windows 3.x when MS started using the Message "IF your system stops responding for a long time restart your computer"

    Einstein’s thoughts on the relativity of time: If you hold your hand on a hot stove for a second that is a long time. If you spend the night with a beautiful woman that is a very short time.

    Mike

  • EMPLOY_REFvarchar

    SURNAMEvarchar

    FORENAMEvarchar

    ADDRESS1varchar

    ADDRESS2varchar

    APPOINTEDdatetime

    A_NUMsmallint

    A_LEVELSvarchar

    AID_REVIEWdatetime

    BIRTHDATEdatetime

    BANK_ACCvarchar

    BANK_NAMEvarchar

    BS_ROLL_NOvarchar

    CONT_SERVdatetime

    COUNTYvarchar

    COSTCENTREvarchar

    CONTRACTbit

    CONTR_ENDdatetime

    DATE_LEFTdatetime

    DATE_LASTDAYdatetime

    DEPARTMENTvarchar

    DEG_CLASSvarchar

    DEG_SUBJvarchar

    DEG_CLASS2varchar

    DEG_SUBJ2varchar

    DRIVEvarchar

    DIVISIONvarchar

    ENTRY_BYvarchar

    ETHNIC_ORGvarchar

    EYE_TESTdatetime

    EMP_TYPEvarchar

    EMP_AGREED_DATEdatetime

    EMPLER_NTCEdecimal

    EMPLE_NOT_MNbit

    EMPLR_NOT_MNbit

    EXIT_IVbit

    E_MAIL_IDvarchar

    EXT_EMAILvarchar

    FAXvarchar

    FIRSTAIDERbit

    F_EDUCATvarchar

    GENDERvarchar

    GRADEvarchar

    GRADE_DATEdatetime

    HEALTH_CHECKdatetime

    INITIALSvarchar

    JOB_REFvarchar

    KNOWN_ASvarchar

    LEAVERvarchar

    LOCATIONvarchar

    MAIDENNAMEvarchar

    MGR_REFvarchar

    MANAGERvarchar

    MOBILEvarchar

    NATIONALITYvarchar

    NOTICEvarchar

    NOTESvarchar

    NI_NUMBERvarchar

    NIGHT_WORKERbit

    NOISYENVbit

    O_NUMsmallint

    O_LEVELSvarchar

    OTHERNAMESvarchar

    PAYROLL_NOvarchar

    POSTCODEvarchar

    PHONEvarchar

    PROF_QUALStext

    PROBATIONsmallint

    PROB_ENDdatetime

    REASONLEFTvarchar

    RETIREDATEdatetime

    RW_PAYNUMvarchar

    SAFETY_REVIEWdatetime

    SAFETY_OFFbit

    SORT_CODEvarchar

    STARTDATEdatetime

    STATUSvarchar

    TEL_EXTvarchar

    TITLEvarchar

    TOWNvarchar

    TOTAL_HOLSdecimal

    UPDATED_ONdatetime

    VDU_USERbit

    WK_EXPDATEdatetime

    WK_PERMITbit

    WTD_OUTbit

    PWORDvarchar

    RE_EMPLOYbit

    CONTR_TYPEvarchar

    DISABLEDbit

    DISABLED_TYPEvarchar

    DISABLED_NOTEStext

    COMPANY_REFvarchar

    COMPANY_NAMEvarchar

    COUNTRYvarchar

    EXTERNAL_DELEGATEbit

    PHONE2varchar

    FAX2varchar

    HOLIDAY_TYPEvarchar

    FRAMEWORK_REFvarchar

    SYS_CREATED_BYvarchar

    SYS_CREATED_ONdatetime

    SYS_EDITED_BYvarchar

    SYS_EDITED_ONdatetime

    DEDUCTIONmoney

    DEDUCTION_BASEdecimal

    EX_RATEdecimal

    CUR_LOCALvarchar

    CUR_BASEvarchar

    LEAVER_NOTEStext

    POSTAL_CORRESPONDENCEvarchar

    POSTAL_ADDRESS1varchar

    POSTAL_ADDRESS2varchar

    POSTAL_TOWNvarchar

    POSTAL_COUNTYvarchar

    POSTAL_COUNTRYvarchar

    POSTAL_POSTCODEvarchar

    POSTAL_TELNOvarchar

    POSTAL_FAXvarchar

    POSTAL_MOBILEvarchar

    POSTAL_EMAILvarchar

    HOME_WORKERbit

    CRB_CHECKdatetime

    CRB_CHECK_TYPEvarchar

    CRB_CHECK_RESULTvarchar

    MARITAL_STATUS_IDint

    DATE_DECEASEDdatetime

    EMP_AGREED_DATAbit

    SIGN_ONvarchar

    MANAGER_NAMEvarchar

    DEPT_DESCvarchar

    INSITE_CODEvarchar

    IA_SALARYmoney

    FIRE_MARSHALLbit

    FIRE_MARSHALL_RETRAINdatetime

    TEAMvarchar

    WORK_FLOORvarchar

    SWITCHBOARDvarchar

    EMP_WRITTEN_ACCP_DATEdatetime

    HOLIDAYS_LEFTdecimal

    DAYS_DEDUCTdecimal

    COMPANY_LOANSmoney

    PAY_LIEU_NOTICEmoney

    PAY_LIEU_NOTICE_TAXmoney

    PENSION_DEDUCTmoney

    LEAVE_SICKNESSbit

    DEDUCTION_DESCvarchar

    LEAVE_EMAILbit

    EMP_STATEvarchar

    CON_SUPPLIERvarchar

    CON_PROJECTvarchar

    CON_PROJECTIDvarchar

    CON_PONUMBERvarchar

    CON_DAILYRATEvarchar

    uphotovarchar

    photoimage

    USR_NTLoginvarchar

    PAYROLLvarchar

    PAYMETHODvarchar

    BANKACC_DATEdatetime

    TO_SOURCE_STARTDATEdatetime

    SDS_IDint

    OLDSDS_IDint

    SAL_MGR_REFvarchar

    SALARY_MANAGERbit

    Index info:

    FORENAMEnonclustered located on PRIMARYFORENAME

    IDX_FULLNAMEnonclustered located on PRIMARYEMPLOY_REF, SURNAME, FORENAME

    IDX_JOBREFnonclustered located on PRIMARYJOB_REF

    PK_EMPLOYEEclustered, unique, primary key located on PRIMARYEMPLOY_REF

    SURNAMEnonclustered located on PRIMARYSURNAME

  • Let me make sure I understand ...

    You want to execute an autojunction query on a 165 column table ... I'am not sure this is the best way for high performance code.

    May be the best way is to split your table.

     

  • The number of columns will have no effect on searching data in a set solution as SQL uses pointers to indexed columns to search the data. These pointers point to  the memory location containing the data of the indexed column and ignore the data in the other columns.

    T-SQL does limit the number of columns to 1024 for a base table. Now that would be a big table.  

    Have you considered making indexing the following columns as noclustered indexes. SDS_ID, Date_LastDay,Contr_End,StartDate, oldSDS_ID and Date_Left along with the indexes you have already created. Adding nonclustered indexes of the columns being searched should speed up your search. As would following Remi's suggestions.  I think (I am still checking on this) that T-sql creates an array of pointers to all indexed columns. If this is the case then adding nonclustered indexes of the columns being searched should speed up your query. As would following Remi's suggestions.

    Mike

    Increasing my store of knowledge increases my awareness of how much I do not know.

  • Did I post a solution of this thread???

    I don't remember posting one... maybe you've read too many of my post lately Michael .

  • No. But I did and gave you credit

     

  • k, that makes more sens .

  • Ron, Remi I try to give credit when credit is due it keeps you guys posting great answers. Ron thanks for suggesting a great soultion that you gleaned from Remi's post.  And yes Remi I have read a lot of your post, if fact that is one of the criteria I use when scanning post your answers along with those of Frank, Ron and a few others always seem to be on the mark.

    [Edited to change the spelling of gleamed to gleaned. Remi's post are shinning objects (they gleam) but Ron gleaned them (picked) sorry guys spelling is not a strong point  .]

    Mike 

Viewing 15 posts - 1 through 15 (of 25 total)

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