February 19, 2009 at 4:40 am
Hi all,
I have a procedure (MS SQL Server 2000) which has to insert into a table in different database and then will delete those data from the related tables. There are more than 6 tables.. with primary key and foreign keys.. But I have one table which is giving the procedure a lot of problem.
I have done the execution plan it shows its scanning 33% of clustered index..
The delete from other table with more records than it takes less time but this take more than the others..
Any suggestions ..
Table Tx1 has total rows of 56848137
It has a joint primary key in 7 columns.
It has the clustered index.
I have another table Tx2 which has 56848332
it also has the same configuration.
When I delete the Tx2 table with the conditions deletes 6 rows in 2 secs.
but when I try to delete the Tx1 it takes nearly 10 mins..
I have done reindexing also .. but no changes..
Thanks,
Harsha
February 19, 2009 at 6:43 am
harsha.bhagat1 (2/19/2009)
Hi all,I have a procedure which has to insert into a table in different database and then will delete those data from the related tables. There are more than 6 tables.. with primary key and foreign keys.. But I have one table which is giving the procedure a lot of problem.
I have done the execution plan it shows its scanning 33% of clustered index..
The delete from other table with more records than it takes less time but this take more than the others..
Any suggestions ..
Table Tx1 has total rows of 56848137
It has a joint primary key in 7 columns.
It has the clustered index.
I have another table Tx2 which has 56848332
it also has the same configuration.
When I delete the Tx2 table with the conditions deletes 6 rows in 2 secs.
but when I try to delete the Tx1 it takes nearly 10 mins..
I have done reindexing also .. but no changes..
Thanks,
Harsha
Can you post the query and the execution plan?
From the sound of it, you either don't have an index that's useful to the delete statement (not selective enough, wrong rows, something) or you're just deleting so much data that it has no choice but to do a scan. Those are just guesses without more data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 7:44 am
I have the primary key.. in the tables and all the index i am using almost all the columns used in the tables.
delete TxBreak
FROM tblSys_Log_Data_TxBreak_1 TxBreak with (index(PK_tblSys_Log_Data_TxBreak_1))
WHERE NOT EXISTS (SELECT 1 FROM #Temp_tblSys_Log_Data_Spot
WHERE TxBreak.jet_batch_id = #Temp_tblSys_Log_Data_Spot.jet_batch_id
AND TxBreak.gp_sequence_number = #Temp_tblSys_Log_Data_Spot.gp_sequence_number
AND TxBreak.campaign_sequence_number = Temp_tblSys_Log_Data_Spot.campaign_sequence_number
AND TxBreak.station_sequence_number = #Temp_tblSys_Log_Data_Spot.station_sequence_number
AND TxBreak.break_sequence_number = #Temp_tblSys_Log_Data_Spot.break_sequence_number)
AND TxBreak.jet_batch_id = @jet_batch_id
primary keys on (jet_batch_id, campaign_sequence_number, gp_sequence_number, station_sequence_number, break_sequence_number)
February 19, 2009 at 7:56 am
That's kind of a wide PK, depending on the data types. Can you post the execution plan? Actual please, not estimated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 8:02 am
i am not able to paste the execution plan..
how will i do it.. please help..
February 19, 2009 at 8:17 am
harsha.bhagat1 (2/19/2009)
i am not able to paste the execution plan..how will i do it.. please help..
You generate an actual execution plan, graphical is fine, even preferred. Right click on the plan and select "Save Execution Plan As" to give it a file name. Zip it and attach it to a post here.
Here's a video if you need a little more help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 8:31 am
I was not able to see the video. I was not ale to save the execution plan also.
I have take the print screen of the whole script.
f anything else is needed then please do tell me
Thanks
Harsha
February 19, 2009 at 8:38 am
That looks like SQL SErver 2000. You're posting in the SQL SErver 2005 forum. There is a difference in functionality. That's why you can't save the plan.
Well, without the data inside the plan, it's hard to tell you specifically what's going wrong. But if you look at it, you'll see that you're getting table scans. That means that the indexes are not being used. You need to look at the criteria that the query is searching on in those table scans to see if you can create a useful index for it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 8:53 am
Sorry for reporting in the SQL server 2005 forum.. I was not knowing there was a different forum for 2000 and 2005.
Even I provide with the table hints it still takes a lot of time...
February 19, 2009 at 9:28 am
harsha.bhagat1 (2/19/2009)
Sorry for reporting in the SQL server 2005 forum.. I was not knowing there was a different forum for 2000 and 2005.Even I provide with the table hints it still takes a lot of time...
oh, I'd stay away from table hints.
Again, I can't get into details looking a picture of the execution plan, but whatever indexes you have, are not being used effectively by the query. So either the code isn't using the indexes well, or the indexes themselves are wrong. You should look at where it's doing a table scan in the execution plan, hover the mouse over the operation and see what table and columns it's trying to reference. That's a place where you might need an index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 9:33 am
Thanks..
🙂
February 19, 2009 at 9:53 am
Although posted in a SQL Server 2005 forum, I do have to give credit to the OP, he did state in his post he was using SQL Server 2000:
Hi all,
I have a procedure (MS SQL Server 2000) which has to insert into a table in different database and then will delete those data from the related tables. There are more than 6 tables.. with primary key and foreign keys.. But I have one table which is giving the procedure a lot of problem.
I have done the execution plan it shows its scanning 33% of clustered index..
Bolding is mine.
February 19, 2009 at 10:16 am
He was right. I had edited the Question as not to get any more confusion for the same..
Sorry once again
Harsha
February 20, 2009 at 9:48 am
Please post the textual version of the execution plan.
before you run your query, run this.
set statistics profile on
GO
you should see a textual plan at the bottom of the result set. It would be best if you attached a .txt file with the results.
Regards, Jim C
February 20, 2009 at 10:42 am
You might want to check to see if the foreign key defined in the child table actually has an index on it. It is a common misconception that creating a foreign key constraint on a child table automatically creates an index for it. It doesn't.
When deleting a row from the parent table, SQL Server has to check to see if a corresponding row in the child table to see if the delete can be allowed. If there is no index on the foreign key in the child table, then there is a lot more looking around to see if the delete operation is legal or not.
Todd Fifield
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply