Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
xrum
Yak Posting Veteran
87 Posts |
 Posted - 2012-01-05 : 14:36:11
|
| i have the following stored procedure:Begin INSERT dbo.Table1(ID, TIMESTAMP) SELECT table2.ID, NAT.TIMESTAMP FROM dbo.table2 WHERE table2.ID = @inidx AND table1.TIMESTAMP = @TheTS INSERT dbo.Table3(ID, TIMESTAMP) SELECT table4.ID, NAT.TIMESTAMP FROM dbo.table4 WHERE table4.ID = @inidx AND table4.TIMESTAMP = @TheTS IF @@TRANCOUNT > 0 COMMIT WORK END goand there's about 20 of these statements, right now the procedure takes over 5 minutes, which is waay too long, is there a way to make it faster? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-01-05 : 14:55:39
|
| Do you have indexes all your tables?If you don't have the passion to help people, you have no passion |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-05 : 15:01:55
|
Assuming that the elapsed time is not a result of blocked resources, I/O wait times etc., I would try to figure out how to speed it up in a series of steps.1: Is any one insert statement taking up the lion's share of the time? If so focus on that statement. If not, look at each insert. You can insert print statements with timestamp to see how much time is taken up by each insert statement.2: For one insert statement, run only the select portion of it. For exampleSELECT table2.ID, NAT.TIMESTAMPFROM dbo.table2WHERE table2.ID = @inidx AND table1.TIMESTAMP = @TheTS Is that very close to the time taken for the insert itself. If so, concentrate on the select statement part of it. If not, you will need to examine the insert part of it.3: Assuming it is the select, is it because there are a large number of rows returned? If so, there may not be much you can do about it. If it is only a few rows and still taking a long time, it could be that you need indexes on the ID column (and the TIMESTAMP column).4. If it turns out that it is the insert that is taking up the time (and not the select), are there a large number of indexes on the table that is causing the insert to be slow? |
|
|
|
xrum
Yak Posting Veteran
87 Posts |
Posted - 2012-01-05 : 15:03:04
|
| @yosiasz i have primary keys on all my tables...@sunitabeck how do i "print statements with timestamp to see how much time is taken up by each insert statement."? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-05 : 15:12:49
|
What I meant is saving the start time, and then printing the time difference, like this:DECLARE @startTime DATETIME;SET @startTime = GETDATE();-- First insert here.PRINT 'First insert took ' + CAST(DATEDIFF(ms,@startTime,GETDATE()) AS VARCHAR(10)) + ' milliseconds';SET @startTime = GETDATE();-- Second insert here.PRINT 'Second insert took ' + CAST(DATEDIFF(ms,@startTime,GETDATE()) AS VARCHAR(10)) + ' milliseconds';SET @startTime = GETDATE();-- and third insert here, and so on... |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-01-05 : 15:26:36
|
| what is the data type of column TIMESTAMP and what is the data type for @TheTS?If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
How to make a stored procedure run faster