query tuning

I have a query which is taking 23 sec to run, if i create a temporary table
for subset of resultset of the query and rewrite it using the temporary
table , which is taking only 4 sec. I cant mention my real query, but i
outline it here.
Original Query outline:
select pacct from (select pacct, qacct from tableA
where pid = '123456' and date > @.date) a
where qacct in (select qacct from TableB where groupid = 'asdfa' )
Modified query outline:
select pacct, qacct into #tp from tableA
where pid = '123456' and date > @.date
select pacct from #tp
where qacct in (select qacct from TableB where groupid = 'asdfa' )
TableA has 12 million recs , Table B has half a million recs.
I used inner join too, there is no improvment. From this can anyone guess
what is wrong , with optimiser or query.
Thanks,
Subbu.
Try this instead:
select pacct from tableA
where pid = '123456'
and date > @.date
and exists
(select *
from TableB
where groupid = 'asdfa'
and TableB.qacct = TableA.qacct)
If that doesn't work, post DDL for your tables, including all constraints
and indexes.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have a query which is taking 23 sec to run, if i create a temporary
table
> for subset of resultset of the query and rewrite it using the temporary
> table , which is taking only 4 sec. I cant mention my real query, but i
> outline it here.
> Original Query outline:
> select pacct from (select pacct, qacct from tableA
> where pid = '123456' and date > @.date) a
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> Modified query outline:
> select pacct, qacct into #tp from tableA
> where pid = '123456' and date > @.date
> select pacct from #tp
> where qacct in (select qacct from TableB where groupid = 'asdfa' )
> TableA has 12 million recs , Table B has half a million recs.
> I used inner join too, there is no improvment. From this can anyone guess
> what is wrong , with optimiser or query.
> Thanks,
> Subbu.
>
>
>
|||It is taking more than 150 sec and still going i stopped it.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Try this instead:
>
> select pacct from tableA
> where pid = '123456'
> and date > @.date
> and exists
> (select *
> from TableB
> where groupid = 'asdfa'
> and TableB.qacct = TableA.qacct)
>
> If that doesn't work, post DDL for your tables, including all constraints
> and indexes.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Subbaiahd" <subbaiahd@.hotmail.com> wrote in message
> news:eFvWDbCzEHA.828@.TK2MSFTNGP10.phx.gbl...
> table
guess
>
|||What are your indexes on the two tables?
-Sue
On Wed, 17 Nov 2004 10:23:06 -0600, "Subbaiahd"
<subbaiahd@.hotmail.com> wrote:

>It is taking more than 150 sec and still going i stopped it.
>"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>news:edXuRLEzEHA.2656@.TK2MSFTNGP14.phx.gbl...
>guess
>