May 19, 2009 at 5:21 am
Hi there,
i hope you can help me, because i dont know what else to do.
For archivieving i want to get the sets of a temporary table (tmptbl1) into the real archival table (tbl1).
IF NOT EXISTS (SELECT * FROM tmptbl1 INNER JOIN tbl1 ON tmptbl1.PK = tbl1.PK)
BEGIN
INSERT INTO tbl1 SELECT * FROM tmptbl1
END
ELSE
BEGIN
INSERT INTO tbl1 SELECT * FROM tbltbl1 LEFT JOIN tbl1 ON tmptbl1.PK = tbl1.PK WHERE tbl1.PK IS NULL
END
This Statement works fine, only at one Customer i had the following phenomenon:
The SELECT Statement: SELECT * FROM tmptbl1 INNER JOIN tbl1 ON tmptbl1.PK = tbl1.PK had for himself a responsetime of less then one second, but if i complete it by the IF EXISTS clause the Statement dont come to an end.
What did i done:
1. close all open Sessions and Processes on the DB
2. set the MAXDOP Option to 1
3. adjust the compatibility level from 80 to 90
4. run a DBCC CHECKDB without any Problems
5. disable AUTO CREATE and UPDATE Statistics
6. afterwards enable AUTO CREATE and UPDATE Statistics additionally the Option UPDATE STATISTICS Asynchronously
7. create and run a Maintenanceplan Optimization Job containing (check DB integrity, recreate indexes, update statistics)
without any improvement.
naturally i modulate the SQL Statement, so i tried to use
- a TOP 1 , that also use the high duration
- a count, that works fine
But i dont want to edit the statement because i had many sites to update this.
I dont know what the Problem ist, so please can somebody help me?
Cheerz
Michel
May 19, 2009 at 5:34 am
I don't see any issues with the statements, but I think you can try changing the NOT EXISTS clause to EXISTS clause and see if there's any performance improvement.
--Ramesh
May 19, 2009 at 5:41 am
thx for the answer
Theres no improvements if i change NOT EXISTS clause to EXISTS. For simplification i replaced the INSERT Statements with PRINT's and still get the high duration
May 19, 2009 at 5:55 am
Can you post the execution plans as .sqlplan file attachments for both the queries?
--Ramesh
May 19, 2009 at 7:19 am
also provide the amount of data in both the tables along with the available indexes.
May 19, 2009 at 7:20 am
I'm sorry, but *.sqlplan is a not permitted type, so I packed both Files in a *.zip File.
tblExportArchiv (841142 Sets) is the Archiv Table - tbl1
ATEMP_tblExportArchiv (217716 Sets) is the temporary Table - tmptbl1
EA_GUID is the PK
There are no Indexes, because this is only an Archieve Database, with no Useraccess
I Think its the right way to analyse the ExcecutionPlans, cause there are totally diffrent, but why it will be diffrent interpreted?
May 19, 2009 at 8:13 am
Why do the check at all. Your second insert statement does exactly what you need done, insert new rows. If there are no new rows you won't get any inserts. You could modify the second insert to use NOT EXISTS in the WHERE clause instead of the left join and may get a slight performance increase.
With your current solution you have to access tmptbl1 2 times and tbl1 once. By eliminating the IF EXISTS you would only access each table once.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2009 at 8:39 am
Hi Jack,
thx for the answer.
the reason why i used this structure is, that i think an INSERT without WHERE (1st Statement) clause is faster than an INSERT with an LEFT JOIN (2nd Statement), I hope i be right
in 90 percent of the cases the first Statement will be used only a few times the second statement will be used.
greets
Michel
May 19, 2009 at 8:48 am
But it isn't faster when your proc "hangs" at the IF EXISTS. Plus you are using more resources because you have to scan tmptbl1 twice no matter what your data is. I would dare to suggest that only scanning the table once will be faster.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2009 at 9:10 am
Hehe a little error in reasoning,
your right, thx for the broad hint
but thats not the real cause. I want to know why it doesnt work?
In the actual situation its not a great problem, because the Database has no user access. But what happen if the same Problem occur on the productive DB?
May 19, 2009 at 9:31 am
Check your statistics. Your IF EXISTS is showing an estimated # of rows for your "permanent" table as under 4 and the select as over 800K. Using the larger table as the outer table in a nested loops join won't be very effecient. You could try the FORCE ORDER hint and see if that speed it up.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2009 at 10:03 am
I cant set the OPTION(FORCE ORDER) in the IF EXISTS clause
In the SELECT only Statement ist works fine
SELECT * FROM dbo.ATEMP_tblExportArchiv ATEA INNER JOIN dbo.tblExportArchiv AEA ON ATEA.EA_GUID = AEA.EA_GUID OPTION(FORCE ORDER)
But
IF EXISTS (SELECT * FROM dbo.ATEMP_tblExportArchiv ATEA INNER JOIN dbo.tblExportArchiv AEA ON ATEA.EA_GUID = AEA.EA_GUID OPTION(FORCE ORDER))
PRINT 1
dont work
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply