July 27, 2006 at 2:13 pm
i have a sproc that is taking 5 hours+ to run. that was on 40000 rows. even on 25 rows it takes over an hour. the sproc calls a sproc or two with a cursor or two ( sorry for being so vague) . the sproc stil should not take an hour for 25 rows. i have checked what db objects are locked, and there are no other processes going on at the time, as i have killed all them off. would lack of space on a hard disk or something. any ideas of some strange things that could cause a sproc to run slowly?
July 27, 2006 at 2:22 pm
I think you are going to have to give us more information on this one. You've already referenced multiple SP's, cursors, and tables...
Can you make up dummy data? I imagine you have proprietary issues and hence your reticence.
I wasn't born stupid - I had to study.
July 27, 2006 at 2:23 pm
Could you post the source code?
July 27, 2006 at 2:24 pm
Are you setting variables inside of your sproc? I've seen issues where if you have a statement like
DECLARE @MyVar
SELECT *
FROM tblMytable
WHERE col1 = @Myvar
If the variable doesn't have a value, profiler doesn't know how to deal with it and you may see some really odd behavior. I beleive I remember hearing someone refer to it as 'parameter sniffing' search this site or google for the term and you should come across soemthing that will help.
July 27, 2006 at 7:29 pm
Look, also, for "triangular joins" especially in correlated sub-queries (which can be a RBAR load all by themselves). An example of a "triangualar join" might be something like the classic "running total" examples below...
SELECT t1.TransactionID,
t1.Amount,
(SELECT SUM(Amount)
FROM TestTable
WHERE TransactionID <= t1.TransactionID) AS RunTotal
INTO dbo.ResultTable
FROM TestTable t1
..or...
SELECT t1.TransactionID,
t1.Amount,
SUM(t2.Amount) AS RunTotal
INTO dbo.ResultTable
FROM TestTable t1,
TestTable t2
WHERE t2.TransactionID <= t1.TransactionID
GROUP BY t1.TransactionID,t1.Amount
Both sets of code appear to do a fine job at 1,000 records... then get a fair bit slower at 10,000 records... at 40,000 records, they're real pigs for time and resources. If "n" is the number of rows in the table, both set's of code must "touch" (n-1)2/2 records to get the job done. Not horribly bad for 1,000 rows but take a look at what happens at 40,000 rows...
Table Recs |
"Touched" Recs
1000
499,001
10,000
49,990,001
40,000
799,960,001
...and that's for EACH "triangular join"... if there's more than 1, oh well...
Anyway, I highlighted what makes the two pieces of code form the triangular joins... it's the inequality in the WHERE clauses of each.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply