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.transid
On 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.