SLOW Update
-
By reviewing the SQL alone, can anyone see a deficiency? It takes forever to run. I always stop it at one minute.
UPDATE #report
SET Closed = (
SELECT COUNT(*)
FROM TableA ws WITH (NOLOCK)
JOIN TableB wo WITH (NOLOCK)
ON wo.ID = ws.WorkOrders_ID
WHERE ws.ServiceEvents_Code = 'CLOS'
AND ws.CreateDateTime >= @start
AND ws.CreateDateTime < @Last
AND ws.CreateUserName = #report.Disp
AND wo.ReturnDate IS NOT NULL
AND wo.Voided = 0
AND wo.WorkOrderTypes_Code IN ('PF','FF') )
-
How many recrods are in #report when you run this update? Also, how many records in TableA and TableB? How are TableA and TableB indexed?
-
#Report has 145 records
TableA has:
- 10,254,648 records
- a clustered index on WorkOrders_ID
- a nonclustered index on ServiceEvents_Code
TableB has:
- 2,099,655 records
- a clustered index on ID
- a nonclustered index on ReturnDate
I also tried a variation of the query by first getting the MIN and MAX ID columns and then looking for records between that set as opposed to between a set filtered by CreateDateTime. The IDENTITY column by the way is on TableA.ID, not TableA.WorkOrders_ID. I can't get a result set from this since I stop the query after one minute. Why would this part take sooo long?
SELECT MIN(ID)
FROM TableA
WHERE CreateDateTime >= '09-01-2007'
-
AND ws.CreateDateTime >= @start
AND ws.CreateDateTime < @Last
causes table scan.
FOr this query you should have clustered index on ( WorkOrders_ID, ServiceEvents_Code, CreateDateTime ).
But if you perform a lot of queries like this:
SELECT MIN(ID)
FROM TableA
WHERE CreateDateTime >= '09-01-2007'
you need clustered index on CreateDateTime and non-clustered index on (WorkOrders_ID, ServiceEvents_Code, CreateDateTime).
If you do it only occasionally then you need just non-clustered index on CreateDateTime.
See for yourself which query is more important for you.
_____________
Code for TallyGenerator -
Heh... damned straight it's slow... you've written it as you would in Oracle... you've used a "Correlated Subquery" in the SET clause and that means that the query must be re-executed in full for every row that appears in the #Report table.
I'll be back in a minute with the corrected query... in the mean time, lookup UPDATE in Books Online and see how it differs in SQL Server... it is NOT the same as it is in other databases.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's the corrected query... since I don't have the tables available that you do, I'm unable to test it, but it should be close. Combine that with the suggestions others have given for the indexes required, and the code should fly...
UPDATE #Report
SET Closed = d.TheCount
FROM #Report r
JOIN (--==== Derived table gets the user name and count
SELECT ws.CreateUserName, COUNT(*) AS TheCount
FROM TableA ws WITH (NOLOCK)
JOIN TableB wo WITH (NOLOCK)
ON wo.ID = ws.WorkOrders_ID
WHERE ws.ServiceEvents_Code = 'CLOS'
AND ws.CreateDateTime >= @start
AND ws.CreateDateTime < @Last
AND wo.ReturnDate IS NOT NULL
AND wo.Voided = 0
AND wo.WorkOrderTypes_Code IN ('PF','FF')
GROUP BY ws.CreateUserName
) d
ON d.CreateUserName = r.Disp
Derived tables are different from correlated sub-queries in that the criteria of a derived table makes no reference outside itself and it produces results that can be used as if a table...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Dude (Jeff), you are so smart!!
-
David (9/24/2007)
Dude (Jeff), you are so smart!!Not really - still has to work. :laugh:
_____________
Code for TallyGenerator -
David (9/24/2007)
Dude (Jeff), you are so smart!!Heh... thanks for the compliment, David... but Serqiy is correct... did it work and did it speed things up for you?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry you cannot send this private message.
• The recipient has already exceeded their permitted number of private messages.
Jeff, it's about you. :blink:
_____________
Code for TallyGenerator -
I'm working on fixing that little problem... I had 84 PM's... when they shifted to the new system, the limit became 50. I'm working out how to download them (I want to keep them in an archive) and then erase them from my inbox...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply