sql server programming: Query Performance -- sql server programming

Re: Query Performance

vanitha
10/17/2006 11:08:01 PM

yes, that takes only 2 minutes 15 secs

[quoted text, click to view]

Re: Query Performance

Uri Dimant
10/18/2006 12:00:00 AM

vanittha

It is really opened and question . Did you see an execution plan of the
query?

[quoted text, click to view]

--you have already set up SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare @Startdate datetime
set @startdate = '20060101'

select o_num
from orders
where o_type in (26,2,11) and o_open >= @startdate and o_open <
dateadd(d,1,@startdate )

--could be CI on o_open column

[quoted text, click to view]

delete @orderlist
from @orderlist a
left join track t on t.o_num = a.o_num and t.t_status like '%cancel%'
where t.t_status is not null

---Note , the optimizer will not be pobably available to use an index (
if you have on t_status) on t_status column

I'd tune each DELETE query separately to make sure that the optimizer is
available to use indexes
Also set SET STATISTICS IO and see how much does SQL Server perfom logical
reads/physical reads

[quoted text, click to view]

Re: Query Performance

Sylvain Lafontaine
10/18/2006 2:08:43 AM

Using the Read Uncommitted level or the NoLock hint will not really
accelerates things.

Setting o_num as the Primary Key Clustered on the table @orderlist might
help as well as making sure that you have all the proper indexes for the
table [track]. For example, do you have an index on t.t_status? Also,
where are you using string constants like 'fastescrow' instead of some
integer value?

I don't see why you are using LEFT JOIN instead of INNER JOIN.

Using '%cancel%' with the LIKE operator instead of 'cancel%' (with no % at
the beginning) is a real performance killer. If possible, you should add a
column where the presence of the word %cancel% would be flagged. Same thing
for '%already%'. You could also take the occasion to add in index on this
new column.

Finally, many of your delete statement are probably making a full table scan
on [track]. By combining all your delete statements in a single one with a
big WHERE statement, you will limit the query plan to a single full table
scan. (Of course, with the proper indexes, there is a possibility that the
full table scan will also be remplaced with something more efficient.)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.