|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Any SQL experts?I've added a section of the SP below for reference. Here is an overview of my table structure (simplified). EMPLOYEE TABLE employeeid name IsActive EMPLOYEE HISTORY TABLE employeeid name IsActive FromDate -- this signifies the start date the current employee record info was valid. ToDate -- this signifies the end date the current employee record info was valid. PROCESS TABLE processid name startdate enddate Basically, this is what the SP is meant to do........ when the sp runs, it will only carry out processes where the current date is between startdate and enddate of the process record. And it should only process employees who were active between these dates. I can find out backdated info about an employee in the history table. However, if there have been no changes to an employee (ie: new joiner), then there will be no records in the history table so I have to read the current status from the master employee table. Hope all this makes sense. OLD CODE: -- this works fairly quickly, but is not correct as it's not looking at historic employee data (employees.IsActive = 1) NEW CODE: ( --if a history record exists for current employee, the read the info ( (select top 1 IsActive from employeehistory eh where eh.todate > process.enddate and employeeid = employee.employeeid order by eh.enddate) = 1 ) OR --if no history record exists for current employee, then read current employee --data in master employee table ( not exists (select * from employeehistory where employeeid = employee.employeeid ) AND (employees.IsActive = 1) ) ) The changed section has increased the process time massively. Furthermore, it is doing a lot of work on tempdb and hence using up all the harddisk space (and eventually fails). The SP is processing approx 500,000 records. Does anyone know why this would happen, and what I can do to improve the performace. What is it writing to tempdb? I've tried adding indexes to the date/employee columns in the history table, but it doesn't help. Well, iterating over 500000 records will take a long time. You don't
provide the code of your SP; so we don't know how the iteration is taking place and make it hard to give you any relevant help; however, using an UNION instead of a Cursor for example might be a solution in your case. Finally, I really don't understand why you have put an Order By in a subquery. Show quote "amy" <amy@nospam.com> wrote in message news:uyBhOeCUGHA.4452@TK2MSFTNGP12.phx.gbl... > Hi, I have an SP that was working fine (took about 10 mins to process). > > I've added a section of the SP below for reference. Here is an overview > of my table structure (simplified). > > EMPLOYEE TABLE > employeeid > name > IsActive > > EMPLOYEE HISTORY TABLE > employeeid > name > IsActive > FromDate -- this signifies the start date the current employee record info > was valid. > ToDate -- this signifies the end date the current employee record info was > valid. > > PROCESS TABLE > processid > name > startdate > enddate > > Basically, this is what the SP is meant to do........ when the sp runs, it > will only carry out processes where the current date is between startdate > and enddate of the process record. And it should only process employees > who were active between these dates. I can find out backdated info about > an employee in the history table. However, if there have been no changes > to an employee (ie: new joiner), then there will be no records in the > history table so I have to read the current status from the master > employee table. > > Hope all this makes sense. > > > OLD CODE: > -- this works fairly quickly, but is not correct as > it's not looking at historic employee data > (employees.IsActive = 1) > > > NEW CODE: > > ( > --if a history record exists for > current employee, the read the info > ( > (select top > 1 IsActive > from > employeehistory eh > where > eh.todate > process.enddate > and > employeeid = employee.employeeid > order by > eh.enddate) = 1 > ) > OR > > > --if no history record exists > for current employee, then read current employee > --data in master employee table > ( > not exists > (select * > from > employeehistory > where > employeeid = employee.employeeid > ) > > AND > > (employees.IsActive = 1) > ) > ) > > > > The changed section has increased the process time massively. > Furthermore, it is doing a lot of work on tempdb and hence using up all > the harddisk space (and eventually fails). The SP is processing approx > 500,000 records. Does anyone know why this would happen, and what I can do > to improve the performace. What is it writing to tempdb? I've tried > adding indexes to the date/employee columns in the history table, but it > doesn't help. > > > > Thanks for your response. There are no cursors being used, just straight
forward selects/joins. The full SP is very long, I have only included the part that has changed and is making the tempdb grow massively. The order by is required because I need to find the 1st instance of the employee history record after the process date. Eg: If the process date is 3rd feb and the employee history is: empid name IsActive fromdate todate 2 tom 0 20th feb 20th march 2 tom 1 1st feb 20th feb 2 tom 0 29th jan 1st feb 2 tom 1 1st jan 29th jan then i need to find the state of the employee as at 3rd feb. i do this by finding the first record where the enddate > 3rd feb. Show quote "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:OIqBtGEUGHA.5468@TK2MSFTNGP14.phx.gbl... > Well, iterating over 500000 records will take a long time. You don't > provide the code of your SP; so we don't know how the iteration is taking > place and make it hard to give you any relevant help; however, using an > UNION instead of a Cursor for example might be a solution in your case. > > Finally, I really don't understand why you have put an Order By in a > subquery. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF > > > "amy" <amy@nospam.com> wrote in message > news:uyBhOeCUGHA.4452@TK2MSFTNGP12.phx.gbl... >> Hi, I have an SP that was working fine (took about 10 mins to process). >> >> I've added a section of the SP below for reference. Here is an overview >> of my table structure (simplified). >> >> EMPLOYEE TABLE >> employeeid >> name >> IsActive >> >> EMPLOYEE HISTORY TABLE >> employeeid >> name >> IsActive >> FromDate -- this signifies the start date the current employee record >> info was valid. >> ToDate -- this signifies the end date the current employee record info >> was valid. >> >> PROCESS TABLE >> processid >> name >> startdate >> enddate >> >> Basically, this is what the SP is meant to do........ when the sp runs, >> it will only carry out processes where the current date is between >> startdate and enddate of the process record. And it should only process >> employees who were active between these dates. I can find out backdated >> info about an employee in the history table. However, if there have been >> no changes to an employee (ie: new joiner), then there will be no records >> in the history table so I have to read the current status from the master >> employee table. >> >> Hope all this makes sense. >> >> >> OLD CODE: >> -- this works fairly quickly, but is not correct as >> it's not looking at historic employee data >> (employees.IsActive = 1) >> >> >> NEW CODE: >> >> ( >> --if a history record exists for >> current employee, the read the info >> ( >> (select >> top 1 IsActive >> from >> employeehistory eh >> where >> eh.todate > process.enddate >> and >> employeeid = employee.employeeid >> order by >> eh.enddate) = 1 >> ) >> OR >> >> >> --if no history record exists >> for current employee, then read current employee >> --data in master employee >> table >> ( >> not exists >> (select * >> from >> employeehistory >> where >> employeeid = employee.employeeid >> ) >> >> AND >> >> (employees.IsActive = 1) >> ) >> ) >> >> >> >> The changed section has increased the process time massively. >> Furthermore, it is doing a lot of work on tempdb and hence using up all >> the harddisk space (and eventually fails). The SP is processing approx >> 500,000 records. Does anyone know why this would happen, and what I can >> do to improve the performace. What is it writing to tempdb? I've tried >> adding indexes to the date/employee columns in the history table, but it >> doesn't help. >> >> >> >> > > amy
It's hard to suggest without seeing the whole code and understand all business requirements I'd start looking at an execution plan , whether or not the optimizer ia available to use indexes Aaron has a great article at his web site http://www.aspfaq.com/show.asp?id=2446 Show quote "amy" <amy@nospam.com> wrote in message news:O46x64GUGHA.3192@TK2MSFTNGP09.phx.gbl... > Thanks for your response. There are no cursors being used, just straight > forward selects/joins. The full SP is very long, I have only included the > part that has changed and is making the tempdb grow massively. > > The order by is required because I need to find the 1st instance of the > employee history record after the process date. > > Eg: > > If the process date is 3rd feb and the employee history is: > > empid name IsActive fromdate todate > 2 tom 0 20th feb 20th march > 2 tom 1 1st feb 20th feb > 2 tom 0 29th jan 1st feb > 2 tom 1 1st jan 29th jan > > then i need to find the state of the employee as at 3rd feb. i do this by > finding the first record where the enddate > 3rd feb. > > > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> > wrote in message news:OIqBtGEUGHA.5468@TK2MSFTNGP14.phx.gbl... >> Well, iterating over 500000 records will take a long time. You don't >> provide the code of your SP; so we don't know how the iteration is taking >> place and make it hard to give you any relevant help; however, using an >> UNION instead of a Cursor for example might be a solution in your case. >> >> Finally, I really don't understand why you have put an Order By in a >> subquery. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF >> >> >> "amy" <amy@nospam.com> wrote in message >> news:uyBhOeCUGHA.4452@TK2MSFTNGP12.phx.gbl... >>> Hi, I have an SP that was working fine (took about 10 mins to process). >>> >>> I've added a section of the SP below for reference. Here is an overview >>> of my table structure (simplified). >>> >>> EMPLOYEE TABLE >>> employeeid >>> name >>> IsActive >>> >>> EMPLOYEE HISTORY TABLE >>> employeeid >>> name >>> IsActive >>> FromDate -- this signifies the start date the current employee record >>> info was valid. >>> ToDate -- this signifies the end date the current employee record info >>> was valid. >>> >>> PROCESS TABLE >>> processid >>> name >>> startdate >>> enddate >>> >>> Basically, this is what the SP is meant to do........ when the sp runs, >>> it will only carry out processes where the current date is between >>> startdate and enddate of the process record. And it should only process >>> employees who were active between these dates. I can find out backdated >>> info about an employee in the history table. However, if there have been >>> no changes to an employee (ie: new joiner), then there will be no >>> records in the history table so I have to read the current status from >>> the master employee table. >>> >>> Hope all this makes sense. >>> >>> >>> OLD CODE: >>> -- this works fairly quickly, but is not correct as >>> it's not looking at historic employee data >>> (employees.IsActive = 1) >>> >>> >>> NEW CODE: >>> >>> ( >>> --if a history record exists for >>> current employee, the read the info >>> ( >>> (select >>> top 1 IsActive >>> from >>> employeehistory eh >>> where >>> eh.todate > process.enddate >>> and >>> employeeid = employee.employeeid >>> order by >>> eh.enddate) = 1 >>> ) >>> OR >>> >>> >>> --if no history record exists >>> for current employee, then read current employee >>> --data in master employee >>> table >>> ( >>> not >>> exists (select * >>> from >>> employeehistory >>> where >>> employeeid = employee.employeeid >>> ) >>> >>> AND >>> >>> (employees.IsActive = 1) >>> ) >>> ) >>> >>> >>> >>> The changed section has increased the process time massively. >>> Furthermore, it is doing a lot of work on tempdb and hence using up all >>> the harddisk space (and eventually fails). The SP is processing approx >>> 500,000 records. Does anyone know why this would happen, and what I can >>> do to improve the performace. What is it writing to tempdb? I've tried >>> adding indexes to the date/employee columns in the history table, but it >>> doesn't help. >>> >>> >>> >>> >> >> > > [Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.] amy (amy@nospam.com) writes: Show quote > The order by is required because I need to find the 1st instance of the The standard idiom is something like:> employee history record after the process date. > > Eg: > > If the process date is 3rd feb and the employee history is: > > empid name IsActive fromdate todate > 2 tom 0 20th feb 20th march > 2 tom 1 1st feb 20th feb > 2 tom 0 29th jan 1st feb > 2 tom 1 1st jan 29th jan > > then i need to find the state of the employee as at 3rd feb. i do this by > finding the first record where the enddate > 3rd feb. SELECT eh.issactive FROM (SELECT * FROM process WHERE processid = @processid) CROSS JOIN (employees e JOIN employeehistory eh ON e.employessid = eh.empolyeeid AND e.startdate = (SELECT MAX(eh2.employeedate) FROM employeehistory eh2 WHERE eh2.empoloyeeid = eh.employessid AND e.employeedate <= p.processdate) Unforteunately, this is not going to perform very well. I guess it is not possible for you change the tables, but for this sort of operation, it can be far more effecient to have one row per employee and day, even if it takes up a lot more disk space. It helps if you include CREATE TABLE and CREATE INDEX statements for your tables. Also sample data as INSERT statements with sample data is good, as that helps to test the logic of a query. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seBooks Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Amy
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post useful DDL and example data. You don't say what should happen if the employee was only active for part of the period. This will say if the employee was active at the start of the period or currently active (but not necessarily an employee at the time of the period) which may be a starting place. SELECT e.employeeid e.name [EMPLOYEE TABLE] e JOIN [EMPLOYEE HISTORY TABLE] h ON h.employeeid = e.employeeid AND h.IsActive = 1 AND ((h.FromDate <= @FromDate AND h.ToDate >= @FromDate) UNION ALL SELECT e.employeeid e.name [EMPLOYEE TABLE] e WHERE NOT EXISTS ( SELECT * FROM [EMPLOYEE HISTORY TABLE] h WHERE h.employeeid = e.employeeid ) AND e.IsActive = 1 John Show quote "amy" wrote: > Hi, I have an SP that was working fine (took about 10 mins to process). > > I've added a section of the SP below for reference. Here is an overview of > my table structure (simplified). > > EMPLOYEE TABLE > employeeid > name > IsActive > > EMPLOYEE HISTORY TABLE > employeeid > name > IsActive > FromDate -- this signifies the start date the current employee record info > was valid. > ToDate -- this signifies the end date the current employee record info was > valid. > > PROCESS TABLE > processid > name > startdate > enddate > > Basically, this is what the SP is meant to do........ when the sp runs, it > will only carry out processes where the current date is between startdate > and enddate of the process record. And it should only process employees who > were active between these dates. I can find out backdated info about an > employee in the history table. However, if there have been no changes to an > employee (ie: new joiner), then there will be no records in the history > table so I have to read the current status from the master employee table. > > Hope all this makes sense. > > > OLD CODE: > -- this works fairly quickly, but is not correct as it's > not looking at historic employee data > (employees.IsActive = 1) > > > NEW CODE: > > ( > --if a history record exists for current > employee, the read the info > ( > (select top > 1 IsActive > from > employeehistory eh > where > eh.todate > process.enddate > and > employeeid = employee.employeeid > order by > eh.enddate) = 1 > ) > OR > > > --if no history record exists > for current employee, then read current employee > --data in master employee table > ( > not exists > (select * > from > employeehistory > where > employeeid = employee.employeeid > ) > > AND > (employees.IsActive > = 1) > ) > ) > > > > The changed section has increased the process time massively. Furthermore, > it is doing a lot of work on tempdb and hence using up all the harddisk > space (and eventually fails). The SP is processing approx 500,000 records. > Does anyone know why this would happen, and what I can do to improve the > performace. What is it writing to tempdb? I've tried adding indexes to the > date/employee columns in the history table, but it doesn't help. > > > > > Depending on the circumstances, joining on a sub-query can result in
performance issues. Instead of doing this: not exists (select * from employeehistory where employeeid = employee.employeeid) Consider doing this: left join employeehistory on employeehistory.employeeid = employee.employeeid Also consider inserting relevent transactions from employeehistory into a temporary tables and joining with that. You can use the Display Estimated Execution Plan feature of Query Analyzer to determine what lookups and indexes are used and compare different versions of a SQL statement: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp Show quote "amy" <amy@nospam.com> wrote in message news:uyBhOeCUGHA.4452@TK2MSFTNGP12.phx.gbl... > Hi, I have an SP that was working fine (took about 10 mins to process). > > I've added a section of the SP below for reference. Here is an overview > of my table structure (simplified). > > EMPLOYEE TABLE > employeeid > name > IsActive > > EMPLOYEE HISTORY TABLE > employeeid > name > IsActive > FromDate -- this signifies the start date the current employee record info > was valid. > ToDate -- this signifies the end date the current employee record info was > valid. > > PROCESS TABLE > processid > name > startdate > enddate > > Basically, this is what the SP is meant to do........ when the sp runs, it > will only carry out processes where the current date is between startdate > and enddate of the process record. And it should only process employees > who were active between these dates. I can find out backdated info about > an employee in the history table. However, if there have been no changes > to an employee (ie: new joiner), then there will be no records in the > history table so I have to read the current status from the master > employee table. > > Hope all this makes sense. > > > OLD CODE: > -- this works fairly quickly, but is not correct as > it's not looking at historic employee data > (employees.IsActive = 1) > > > NEW CODE: > > ( > --if a history record exists for > current employee, the read the info > ( > (select top > 1 IsActive > from > employeehistory eh > where > eh.todate > process.enddate > and > employeeid = employee.employeeid > order by > eh.enddate) = 1 > ) > OR > > > --if no history record exists > for current employee, then read current employee > --data in master employee table > ( > not exists > (select * > from > employeehistory > where > employeeid = employee.employeeid > ) > > AND > > (employees.IsActive = 1) > ) > ) > > > > The changed section has increased the process time massively. > Furthermore, it is doing a lot of work on tempdb and hence using up all > the harddisk space (and eventually fails). The SP is processing approx > 500,000 records. Does anyone know why this would happen, and what I can do > to improve the performace. What is it writing to tempdb? I've tried > adding indexes to the date/employee columns in the history table, but it > doesn't help. > > > >  
Other interesting topics
|
|||||||||||||||||||||||
