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_REF varchar
SURNAME varchar
FORENAME varchar
ADDRESS1 varchar
ADDRESS2 varchar
APPOINTED datetime
A_NUM smallint
A_LEVELS varchar
AID_REVIEW datetime
BIRTHDATE datetime
BANK_ACC varchar
BANK_NAME varchar
BS_ROLL_NO varchar
CONT_SERV datetime
COUNTY varchar
COSTCENTRE varchar
CONTRACT bit
CONTR_END datetime
DATE_LEFT datetime
DATE_LASTDAY datetime
DEPARTMENT varchar
DEG_CLASS varchar
DEG_SUBJ varchar
DEG_CLASS2 varchar
DEG_SUBJ2 varchar
DRIVE varchar
DIVISION varchar
ENTRY_BY varchar
ETHNIC_ORG varchar
EYE_TEST datetime
EMP_TYPE varchar
EMP_AGREED_DATE datetime
EMPLER_NTCE decimal
EMPLE_NOT_MN bit
EMPLR_NOT_MN bit
EXIT_IV bit
E_MAIL_ID varchar
EXT_EMAIL varchar
FAX varchar
FIRSTAIDER bit
F_EDUCAT varchar
GENDER varchar
GRADE varchar
GRADE_DATE datetime
HEALTH_CHECK datetime
INITIALS varchar
JOB_REF varchar
KNOWN_AS varchar
LEAVER varchar
LOCATION varchar
MAIDENNAME varchar
MGR_REF varchar
MANAGER varchar
MOBILE varchar
NATIONALITY varchar
NOTICE varchar
NOTES varchar
NI_NUMBER varchar
NIGHT_WORKER bit
NOISYENV bit
O_NUM smallint
O_LEVELS varchar
OTHERNAMES varchar
PAYROLL_NO varchar
POSTCODE varchar
PHONE varchar
PROF_QUALS text
PROBATION smallint
PROB_END datetime
REASONLEFT varchar
RETIREDATE datetime
RW_PAYNUM varchar
SAFETY_REVIEW datetime
SAFETY_OFF bit
SORT_CODE varchar
STARTDATE datetime
STATUS varchar
TEL_EXT varchar
TITLE varchar
TOWN varchar
TOTAL_HOLS decimal
UPDATED_ON datetime
VDU_USER bit
WK_EXPDATE datetime
WK_PERMIT bit
WTD_OUT bit
PWORD varchar
RE_EMPLOY bit
CONTR_TYPE varchar
DISABLED bit
DISABLED_TYPE varchar
DISABLED_NOTES text
COMPANY_REF varchar
COMPANY_NAME varchar
COUNTRY varchar
EXTERNAL_DELEGATE bit
PHONE2 varchar
FAX2 varchar
HOLIDAY_TYPE varchar
FRAMEWORK_REF varchar
SYS_CREATED_BY varchar
SYS_CREATED_ON datetime
SYS_EDITED_BY varchar
SYS_EDITED_ON datetime
DEDUCTION money
DEDUCTION_BASE decimal
EX_RATE decimal
CUR_LOCAL varchar
CUR_BASE varchar
LEAVER_NOTES text
POSTAL_CORRESPONDENCE varchar
POSTAL_ADDRESS1 varchar
POSTAL_ADDRESS2 varchar
POSTAL_TOWN varchar
POSTAL_COUNTY varchar
POSTAL_COUNTRY varchar
POSTAL_POSTCODE varchar
POSTAL_TELNO varchar
POSTAL_FAX varchar
POSTAL_MOBILE varchar
POSTAL_EMAIL varchar
HOME_WORKER bit
CRB_CHECK datetime
CRB_CHECK_TYPE varchar
CRB_CHECK_RESULT varchar
MARITAL_STATUS_ID int
DATE_DECEASED datetime
EMP_AGREED_DATA bit
SIGN_ON varchar
MANAGER_NAME varchar
DEPT_DESC varchar
INSITE_CODE varchar
IA_SALARY money
FIRE_MARSHALL bit
FIRE_MARSHALL_RETRAIN datetime
TEAM varchar
WORK_FLOOR varchar
SWITCHBOARD varchar
EMP_WRITTEN_ACCP_DATE datetime
HOLIDAYS_LEFT decimal
DAYS_DEDUCT decimal
COMPANY_LOANS money
PAY_LIEU_NOTICE money
PAY_LIEU_NOTICE_TAX money
PENSION_DEDUCT money
LEAVE_SICKNESS bit
DEDUCTION_DESC varchar
LEAVE_EMAIL bit
EMP_STATE varchar
CON_SUPPLIER varchar
CON_PROJECT varchar
CON_PROJECTID varchar
CON_PONUMBER varchar
CON_DAILYRATE varchar
uphoto varchar
photo image
USR_NTLogin varchar
PAYROLL varchar
PAYMETHOD varchar
BANKACC_DATE datetime
TO_SOURCE_STARTDATE datetime
SDS_ID int
OLDSDS_ID int
SAL_MGR_REF varchar
SALARY_MANAGER bit
Index info:
FORENAME nonclustered located on PRIMARY FORENAME
IDX_FULLNAME nonclustered located on PRIMARY EMPLOY_REF, SURNAME, FORENAME
IDX_JOBREF nonclustered located on PRIMARY JOB_REF
PK_EMPLOYEE clustered, unique, primary key located on PRIMARY EMPLOY_REF
SURNAME nonclustered located on PRIMARY SURNAME
-
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