x

Improve Script Performance

I have a script that was created in a previous version of SQL and I'm trying to improve the performance but obviously get the same results. Can you please help me find the most efficient way of getting the results from the following scripts please:

 DECLARE @FDate as datetime
 set @FDate = (select max(dDate) from TableA where Field1 = 1)
 
 -- Update the version in all tables
 update X
 set [VERSION] =  @FDate
 FROM TableX AS X
 where not exists (select * from TableXOld WHERE Field2 = X.Field2)
 
 UPDATE y
 SET y.[VERSION] = @FDate
  from TableX AS y
 where exists (SELECT * from TableXOld x where x.Field2 = y.Field2 and x.Field3 <> y.Field3)

I think I should probably know this but would like some other opinions. I don't want to get to the situation where I spend ages finding a very small gain.

more ▼

asked Jun 14, 2011 at 01:00 AM in Default

avatar image

Mrs_Fatherjack
5.2k 66 69 77

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

As with most things it depends! You could try experimenting with joins ...

Gail Shaw posted some interesting pieces comparing EXISTS and JOINs and IN

more ▼

answered Jun 14, 2011 at 01:15 AM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

Many thanks, I've read all the articles and am amazed that the exists/Not Exists has better performance than joins.

Many thanks.

Jun 14, 2011 at 03:52 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left

I like Kev's references to Gail's blog posts. Excellent stuff.

The simple addition I would make is, check the execution plans of all three queries and be sure you're getting good index use (seek instead of a scan, no lookups) because otherwise, regardless of structure, you're likely to get poor performance.

more ▼

answered Jun 14, 2011 at 03:29 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1149
x310

asked: Jun 14, 2011 at 01:00 AM

Seen: 1200 times

Last Updated: Jun 14, 2011 at 01:00 AM

Copyright 2017 Redgate Software. Privacy Policy