June 1, 2012 at 9:30 am
Hi,
I have the following SQL:
UPDATE TABLE1 t1
SET Processing_Status = 0
WHERE t1.ID NOT IN (SELECT ID FROM TABLE2);
circa 500k rows in Table2 and circa 250k rows in Table1
This statement is running a little slow, and I am hoping that someone can give some clues as to how to improve this.
I thought about using a MERGE (would it go quicker with simple update?) but in this case it's not quite right because I can not update on WHEN NOT MATCHED.
Kind regards,
B
June 1, 2012 at 9:35 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2012 at 9:45 am
Thanks for your reply Gail and yes, I really should provide that info ... but ** bows head, feels small and vulnerable ** I am using .... oracle ...
I was hoping that there would a (ANSI) SQL trick to use instead of the "WHERE t1.ID NOT IN (SELECT ID FROM TABLE2);" ... so more of a logic solution than pure code.
I guess I will probably have to go to a oracle forum and start talking about indexes, etc ... (and provide the info as per the guidelines you linked to).
June 1, 2012 at 9:48 am
Yes what will work as a solid performance enhancement for sql server may not be the same in Oracle. For example in Oracle it might be better to use a cursor and in t-sql that would probably be the worst thing you can do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2012 at 11:16 am
bleroy (6/1/2012)
but ** bows head, feels small and vulnerable ** I am using .... oracle ...
Then any optimisation recommendations that you would get that assumed you're using SQL 2008 (since this is in the SQL 2008 forum) would be useless or worse. The database platforms are different and the way you optimise them is different. Try asking in an Oracle forum.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply