May 30, 2014 at 11:38 am
Hello and Happy Friday Everyone
I am working on a query that is in a sproc. Things execute really nice and quick, right up to an insert into a table. The table is truncated before every data load. There are no indexes on the table. I can select the data and it returns to the SSMS window in about one minute. When I use the same query and try to insert the results into a table from a CTE, it takes close to 30 minutes. I dropped and created the table again.
I know this is a little tough with not seeing anything. Just off top of your head, can you think of anything that I can check?
Thank you in advance
Andrew SQLDBA
May 30, 2014 at 11:52 am
AndrewSQLDBA (5/30/2014)
I know this is a little tough with not seeing anything.
Andrew...Happy Friday 🙂
any reason why you cant provide more details?
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 30, 2014 at 12:03 pm
The usual suspects are triggers, but you dropped and rebuilt the table. What is the wait_type in sys.dm_exec_requests for the session, when you are running the insert?
May 30, 2014 at 12:33 pm
Double check the indexes, be sure nothing's there.
You could look at DDL triggers, but I doubt they apply here.
Can you mock some up something, change a few fields/tablenames and show us what's happening?
May 30, 2014 at 12:46 pm
Thank You to each and every one of you guys for your suggestions. Sorry I was not able to give more details and some examples. I know that is tough on you. I greatly appreciate the suggestions.
I have been running some tests, and using copies of the original tables, etc... and think that I have found the issue. It is a join to another table, that is not in the same database. It is joining using all columns in the table. I have been able to cut some down. But I do need to check and verify if the row that I am inserting into the table, does not already exist in the destination table. I don't know really know of a great way to perform this other than a LEFT OUTER JOIN before the insert.
Any suggestions on this?
Thanks and I hope that you all have a great friday and a wonderful and relaxing weekend.
Andrew SQLDBA
May 30, 2014 at 12:50 pm
What about inserting all the rows into the ETL table that I have already verified, and then delete the rows from the ETL table that are already in the Destination table? Do you think that may be a little faster? Any reason why it would not be? I know there will be a table scan, but it may not be as bad as during the insert.
Never hurts to try.
Andrew SQLDBA
May 31, 2014 at 11:26 am
AndrewSQLDBA (5/30/2014)
What about inserting all the rows into the ETL table that I have already verified, and then delete the rows from the ETL table that are already in the Destination table? Do you think that may be a little faster? Any reason why it would not be? I know there will be a table scan, but it may not be as bad as during the insert.Never hurts to try.
Andrew SQLDBA
if you are performing an ETL process...(Source>Staging>Destination)...then you might well find it quicker.
it will of course depend on what you are doing...:-)
in principal....
source and destination tables have identical PKs
insert all columns from source into staging (based on your criteria for selection)
delete all from destination where matches staging (based on PK)
insert from staging into destination.
NOTE..this will not deal with deletes in the source table.
as an alternative....have you considered MERGE?
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply