February 18, 2008 at 2:20 am
Dear All,
I have a SELECT statement which runs less than 1 sec.
When I try to INSERT this select into real table, nor @table, nor #table it takes between 1:10 to 1:20 minutes.
What shall I check? Where am I wrong?
Thanks in advance
February 18, 2008 at 2:40 am
Has comparing the query execution plans for the two statements given you any clues?
Best wishes,
Phil Factor
February 18, 2008 at 4:08 am
Zeev Kazhdan (2/18/2008)
Dear All,I have a SELECT statement which runs less than 1 sec.
When I try to INSERT this select into real table, nor @table, nor #table it takes between 1:10 to 1:20 minutes.
What shall I check? Where am I wrong?
Thanks in advance
What kind of indexes do you have on the table you are inserting into? Are there any triggers or check constraints? Is anyone else using this table at the same time?
Regards,
Andras
February 18, 2008 at 4:46 am
Thank you for the reply.
I have a primary key on both tables and I have tried to play with WITH(INDEX()), but nothing changed.
There is no trigger nor constraint on both tables and I work in my TEST environment, so nobody uses these tables, except me.
Kind Regards
Zeev
February 18, 2008 at 4:57 am
Zeev Kazhdan (2/18/2008)
Thank you for the reply.I have a primary key on both tables and I have tried to play with WITH(INDEX()), but nothing changed.
There is no trigger nor constraint on both tables and I work in my TEST environment, so nobody uses these tables, except me.
Kind Regards
Zeev
Could you measure the time it takes to insert the same data into a temptable that has the same index structure. Also, similarly to Phil's suggestion, check how many database pages are affected ("SET STATISTICS IO ON"). Also, what is the "health" of your clustered index on this table (it may be rather fragmented)
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(N'mydb'), OBJECT_ID(N'mytable'), 1,
NULL, NULL)
and IO could be a bottleneck.
Regards,
Andras
February 18, 2008 at 5:05 am
Andras,
Thank you for the kind reply - I will check this points.
BTW, I've bought the Query Prompt of Red Gate - nice product indead.
Zeev Kazhdan
February 18, 2008 at 10:38 pm
OK,
Here it goes; it didn't let me to run the statement with 1,NULL,NULL as you suggested,
but NULL,NULL,'DETAILED' - hope it will be ok. I"ve loaded the result file here
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'shidurit_test'),OBJECT_ID(N'I_SCHEDULE_MAIN'),NULL, NULL , 'DETAILED')
WHERE object_id = 237295955
I thought as well, that if I will recreate the PK from the scratch it will improve the performance, but no.
The show-plan didn't give me too much as well; it shows that SELECT takes 87% percent while using CLUSTERED INDEX SEEK while INSERT INTO of this select takes 86% only, same with a nested join loop: Select takes 9% and INSERT - 8%.
MANY THANKS!
February 19, 2008 at 3:06 am
Zeev Kazhdan (2/18/2008)
OK,Here it goes; it didn't let me to run the statement with 1,NULL,NULL as you suggested,
but NULL,NULL,'DETAILED' - hope it will be ok. I"ve loaded the result file here
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'shidurit_test'),OBJECT_ID(N'I_SCHEDULE_MAIN'),NULL, NULL , 'DETAILED')
WHERE object_id = 237295955
I thought as well, that if I will recreate the PK from the scratch it will improve the performance, but no.
The show-plan didn't give me too much as well; it shows that SELECT takes 87% percent while using CLUSTERED INDEX SEEK while INSERT INTO of this select takes 86% only, same with a nested join loop: Select takes 9% and INSERT - 8%.
MANY THANKS!
Hi Zeev,
the output of the sys.dm_db_index_physical_stats does suggest quite a lot. First, your table you are inserting into has, in addition to the clustered index, seven nonclusterd indexes. While all of them are small and reference fixed length columns, every row insert will need to update these indexes. When you were inserting data into an empty temporary table, you did not have this overhead of updateing all the idexes.
You could try to do several things to speed your insert up.
If you are allowed, change to bulk recovery mode before the insert, and then after the inserts, back to full recovery mode. (or, if it is a toy database/this happens in a window of enough full backups, ... maybe take a full backup, switch to simple, do the inserts, and take a full backup again)
Disabling the nonclustered indexes and enabling them after the inserts should also help (but do have a look at how much time this will save).
Regards,
Andras
February 19, 2008 at 3:21 am
Andras,
Thank you for the kind reply, but I think there is some misunderstanding: "First, your table you are inserting into has, in addition to the clustered index, seven nonclusterd indexes." May be I miss you, but the source table of mine has clustered plus 7 nonclustered. The target table doesn't. I even tried to insert the SELECT of mine into @table or #table with the same "success".
Many thanks,
Zeev
February 19, 2008 at 3:41 am
What is the size of your database before and after you run the SELECT...INSERT. If either the database or the tran log grows during this process, you will have to wait while SQL sorts out the file growth.
Hopefully you have autoshrink and autoclose turned OFF.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 19, 2008 at 4:04 am
Thank you for the kind reply: the server has a scheduled transaction log maintenance set to each hour during the working hours, so I never saw it going over 50MB, even when I run my INSERT COMMAND.
THX,
Zeev
February 19, 2008 at 9:56 pm
Here are she showplans.
The client a has a transaction log maintenance scheduled for each hour during the working times, so the log is very small - never goes over 50MB.
Regards,
Zeev
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply