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