I need to fine tune this query

Hi ALL,
This query is taking more than 5 minutes. I need to fine tune so that
it is completed in less than two minutes.
Can anyone help me with this ?
Create table #TempBatch(batchid int, transid int, amt money)
Insert into #TempBatch
SELECT b.batchid, t.transid, SUM(CASE e.DebitFlg
When 1 Then e.Amt
ELSE - e.Amt
END )
FROM BATCH b (NOLOCK) inner join
EAmount e (NOLOCK) on b.batchid = e.batchid
inner join #Transfer t (NOLOCK) on t.transid = e.transid
WHERE b.CheckDt > = '2001-01-01' and b.typecd = 'ABC'
and b.amountypecd = 'CD' and b.BatchCd = 'PD'
and e.TypeCd in ( 'X', 'E')
and e.Cd <> 'ENR'
AND NOT EXISTS ( SELECT Batchid,Transid
FROM CLIENT c (NOLOCK)
WHERE c.Batchid=e.batchid AND c.Transid=e.transid )
group by b.batchid, t.transidOn Thu, 19 Jul 2007 08:55:17 -0700, Aditya.shhh wrote:

> Hi ALL,
> This query is taking more than 5 minutes. I need to fine tune so that
> it is completed in less than two minutes.
> Can anyone help me with this ?
> Create table #TempBatch(batchid int, transid int, amt money)
> Insert into #TempBatch
> SELECT b.batchid, t.transid, SUM(CASE e.DebitFlg
> When 1 Then e.Amt
> ELSE - e.Amt
> END )
> FROM BATCH b (NOLOCK) inner join
> EAmount e (NOLOCK) on b.batchid = e.batchid
> inner join #Transfer t (NOLOCK) on t.transid = e.transid
> WHERE b.CheckDt > = '2001-01-01' and b.typecd = 'ABC'
> and b.amountypecd = 'CD' and b.BatchCd = 'PD'
> and e.TypeCd in ( 'X', 'E')
> and e.Cd <> 'ENR'
> AND NOT EXISTS ( SELECT Batchid,Transid
> FROM CLIENT c (NOLOCK)
> WHERE c.Batchid=e.batchid AND c.Transid=e.transid )
> group by b.batchid, t.transid
When you create your temp table, create a clustered index on BatchID or
TransID, which ever makes sense, then create a non-clustered index on the
other. Also may need to check if indexes are on columns used in where
clause. When you execute the proc or batch statement, turn on show
execution plan and look for Index Scan and Table Scan, if the tables are
large, you don't want to see these, add indexes to the columns.
HTH,
Chuck Lathrope
www.sqlwebpedia.com|||Thank you for properly stating the objective of question.
There is nothing wrong with your query. There is no obvious rewrite that
would speed it up.
The rest is completely up to the available indexes. Unfortunately, the
DDL is incomplete (no DDL for BATCH, EAmount, #Transfer and CLIENT).
There are a few strategies you can follow:
1. Try the index tuning wizard, or whatever it is called nowadays
2. Create a whole bunch of indexes, including compound indexes in all
possible combinations, run the query, and keep only the indexes that are
used.
3. Tune the query
If you want to tune the query, then a) you need to know which indexes
are there, and know the clustered index for each table (when
applicable), b) you need to know the selectivity and data distribution
of all the column values in the WHERE clause, c) you need to know or
guess if you can add the indexes you want without impacting
insert/update/delete performance too much.
If you want some tips, then please post the relevant DDL and the current
5-minute query plan (use SET SHOWPLAN_TEXT ON and run the query).
Gert-Jan
"Aditya.shhh" wrote:
> Hi ALL,
> This query is taking more than 5 minutes. I need to fine tune so that
> it is completed in less than two minutes.
> Can anyone help me with this ?
> Create table #TempBatch(batchid int, transid int, amt money)
> Insert into #TempBatch
> SELECT b.batchid, t.transid, SUM(CASE e.DebitFlg
> When 1 Then e.Amt
> ELSE - e.Amt
> END )
> FROM BATCH b (NOLOCK) inner join
> EAmount e (NOLOCK) on b.batchid = e.batchid
> inner join #Transfer t (NOLOCK) on t.transid = e.transid
> WHERE b.CheckDt > = '2001-01-01' and b.typecd = 'ABC'
> and b.amountypecd = 'CD' and b.BatchCd = 'PD'
> and e.TypeCd in ( 'X', 'E')
> and e.Cd <> 'ENR'
> AND NOT EXISTS ( SELECT Batchid,Transid
> FROM CLIENT c (NOLOCK)
> WHERE c.Batchid=e.batchid AND c.Transid=e.transid )
> group by b.batchid, t.transid|||On Jul 19, 9:55 pm, "Aditya.shhh" <Aditya.S...@.gmail.com> wrote:
> Hi ALL,
> This query is taking more than 5 minutes. I need to fine tune so that
> it is completed in less than two minutes.
> Can anyone help me with this ?
> Create table #TempBatch(batchid int, transid int, amt money)
> Insert into #TempBatch
> SELECT b.batchid, t.transid, SUM(CASE e.DebitFlg
> When 1 Then e.Amt
> ELSE - e.Amt
> END )
> FROM BATCH b (NOLOCK) inner join
> EAmount e (NOLOCK) on b.batchid = e.batchid
> inner join #Transfer t (NOLOCK) on t.transid = e.transid
> WHERE b.CheckDt > = '2001-01-01' and b.typecd = 'ABC'
> and b.amountypecd = 'CD' and b.BatchCd = 'PD'
> and e.TypeCd in ( 'X', 'E')
> and e.Cd <> 'ENR'
> AND NOT EXISTS ( SELECT Batchid,Transid
> FROM CLIENT c (NOLOCK)
> WHERE c.Batchid=e.batchid AND c.Transid=e.transid )
> group by b.batchid, t.transid
you can try something like following:
Create table #TempBatch(batchid int, transid int, amt money)
select b.batchid, t.transid, e.DebitFlg, e.Amt
into #all_records
FROM BATCH b (NOLOCK) inner join
EAmount e (NOLOCK) on b.batchid = e.batchid
inner join #Transfer t (NOLOCK) on t.transid = e.transid
WHERE b.CheckDt > = '2001-01-01' and b.typecd = 'ABC'
and b.amountypecd = 'CD' and b.BatchCd = 'PD'
and e.TypeCd in ( 'X', 'E')
and e.Cd <> 'ENR'
AND NOT EXISTS ( SELECT Batchid,Transid
FROM CLIENT c (NOLOCK)
WHERE c.Batchid=e.batchid AND c.Transid=e.transid
insert #TempBatch
select batchid, transid, SUM(CASE DebitFlg
When 1 Then Amt
ELSE - Amt
END)
from #all_rows
group by b.batchid, t.transid
HTH.|||Hi Aditya
Gert's comments are spot on - performance will be determined virtually
entirely by whether you have the right indexes in place, so it's crucial
that you check wheter the columns referenced in BATCH, CLIENT & also perhaps
#Transfer are indexed.
You didn't say whether you're using SQL 2000 or SQL 2005. If you're using
SQL 2005, the Database Tuning Advisor (DTA) might produce some good index
recommendations for you. SQL 2000's Index Tuning Advisor (ITA) probably
won't work though given that your query uses a temp #table (ITA doesn't work
properly with temp tables).
As far as re-writing the query's concerned there might be an option here to
move the NOT EXISTS into an OUTER JOIN, using a MERGE hint. This potentially
can be massively more efficient if it stops large scale NOT EXISTS checking
via NESTED LOOP join operations. However, depending on which columns are key
constraints in your schema, this re-written query could possibly return
different results & so requires testing (as does any re-written query).
Create table #TempBatch(batchid int, transid int, amt money)
Insert into #TempBatch
SELECT b.batchid,
t.transid,
SUM(CASE e.DebitFlg When 1 Then e.Amt ELSE - e.Amt END )
FROM BATCH b (NOLOCK)
join (select distinct e.batchid, e.transid, e.Amt, e.DebitFlg
from EAmount e (NOLOCK)
left merge join CLIENT c (NOLOCK)
on e.Batchid=c.batchid AND e.Transid=c.transid
where e.TypeCd in ( 'X', 'E')
and e.Cd <> 'ENR'
and c.batchid is null) e on b.batchid = e.batchid
join #Transfer t (NOLOCK) on t.transid = e.transid
WHERE b.CheckDt > = '2001-01-01'
and b.typecd = 'ABC'
and b.amountypecd = 'CD' and b.BatchCd = 'PD'
group by b.batchid, t.transid
Remember that it's crucial that you remember that there's no point
re-writing a query unless you have the correct indexes in place, and this
query probably needs indexes on:
CLIENT (batchid, transid)
EAmount (TypeCd, Cd, batchid, transid, DebitFlg, Amt)
BATCH (CheckDt, typecd, amountypecd, BatchCd, batchid)
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Aditya.shhh" <Aditya.Shhh@.gmail.com> wrote in message
news:1184860517.576683.295800@.x40g2000prg.googlegroups.com...
> Hi ALL,
> This query is taking more than 5 minutes. I need to fine tune so that
> it is completed in less than two minutes.
> Can anyone help me with this ?
> Create table #TempBatch(batchid int, transid int, amt money)
> Insert into #TempBatch
> SELECT b.batchid, t.transid, SUM(CASE e.DebitFlg
> When 1 Then e.Amt
> ELSE - e.Amt
> END )
> FROM BATCH b (NOLOCK) inner join
> EAmount e (NOLOCK) on b.batchid = e.batchid
> inner join #Transfer t (NOLOCK) on t.transid = e.transid
> WHERE b.CheckDt > = '2001-01-01' and b.typecd = 'ABC'
> and b.amountypecd = 'CD' and b.BatchCd = 'PD'
> and e.TypeCd in ( 'X', 'E')
> and e.Cd <> 'ENR'
> AND NOT EXISTS ( SELECT Batchid,Transid
> FROM CLIENT c (NOLOCK)
> WHERE c.Batchid=e.batchid AND c.Transid=e.transid )
> group by b.batchid, t.transid
>