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
>

Query Subset of Data

I am fairly new to this - so please forgive me. I have a set of data that I
collected with one query but in the same stored procedure I would like to
turn around and use this data in another query to get additional info about
the agent. Please see below for example:
select paymmain.agentno,
sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor = 'Refund' or PaidFor
= 'Credit' or PaidFor = 'Reversed') then paidamount * -1
else paidamount end) as paidamount,
sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
Rev' or PaidFor = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
paidamount * -1 else paidamount end) else (case when
(PaidFor = 'NSF Rev' or PaidFor
= 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
(paidamount * -1) - servicefee else paidamount - servicefee
end) end) as sumamount,
sum(servicefee) as servicefeesum
from paymmain
join agendefa on paymmain.agentno = agendefa.agentno
where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
paymmain.agentno >= 0000
and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
group by paymmain.agentno
order by paymmain.agentno
This will return one record per agent. I have additional information that I
need about the agent - like bank info and status. So I would like to take
the above query and use it in another query to get the additional info
before returning from the stored procedure.
Also, I tired adding the info to this query but the status is a bit which it
gives an error that a bit can not be included ...
Any help would be appreciate - even if it is a keyword to do a search on.You can put this query into a view and then run another select query
that join the agent table to this view.
Another way is to put this query as a inner query in the FROM clause.
SQL Server support the following syntax.
Select * from Agent, (another select statement) as SomeTempTableName
where Agent.JoinField=SomeTempTableName.JoinField.
HHickey wrote:
> I am fairly new to this - so please forgive me. I have a set of data that I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor => 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
> Rev' or PaidFor => 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount - servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>|||You could SELECT this into a temporary table level variable, or a temp table
of some type then use it in another procedure or within the same procedure.
Rick Sawtell
MCT, MCSD, MCDBA
"HHickey" <hhickey@.pcgroup.holdings.com> wrote in message
news:er1v73%23qEHA.3172@.TK2MSFTNGP10.phx.gbl...
> I am fairly new to this - so please forgive me. I have a set of data that
I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info
about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor
=> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor ='NSF
> Rev' or PaidFor => 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount -
servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that
I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which
it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>

Query Subset of Data

I am fairly new to this - so please forgive me. I have a set of data that I
collected with one query but in the same stored procedure I would like to
turn around and use this data in another query to get additional info about
the agent. Please see below for example:
select paymmain.agentno,
sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor =
'Refund' or PaidFor
= 'Credit' or PaidFor = 'Reversed') then paidamount * -1
else paidamount end) as paidamount,
sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
Rev' or PaidFor =
'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
paidamount * -1 else paidamount end) else (case when
(PaidFor = 'NSF Rev' or PaidFor
= 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
PaidFor = 'Reversed') then
(paidamount * -1) - servicefee else paidamount - servicefee
end) end) as sumamount,
sum(servicefee) as servicefeesum
from paymmain
join agendefa on paymmain.agentno = agendefa.agentno
where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
paymmain.agentno >= 0000
and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
group by paymmain.agentno
order by paymmain.agentno
This will return one record per agent. I have additional information that I
need about the agent - like bank info and status. So I would like to take
the above query and use it in another query to get the additional info
before returning from the stored procedure.
Also, I tired adding the info to this query but the status is a bit which it
gives an error that a bit can not be included ...
Any help would be appreciate - even if it is a keyword to do a search on.
You can put this query into a view and then run another select query
that join the agent table to this view.
Another way is to put this query as a inner query in the FROM clause.
SQL Server support the following syntax.
Select * from Agent, (another select statement) as SomeTempTableName
where Agent.JoinField=SomeTempTableName.JoinField.
HHickey wrote:
> I am fairly new to this - so please forgive me. I have a set of data that I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor =
> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor = 'NSF
> Rev' or PaidFor =
> 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount - servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>
|||You could SELECT this into a temporary table level variable, or a temp table
of some type then use it in another procedure or within the same procedure.
Rick Sawtell
MCT, MCSD, MCDBA
"HHickey" <hhickey@.pcgroup.holdings.com> wrote in message
news:er1v73%23qEHA.3172@.TK2MSFTNGP10.phx.gbl...
> I am fairly new to this - so please forgive me. I have a set of data that
I
> collected with one query but in the same stored procedure I would like to
> turn around and use this data in another query to get additional info
about
> the agent. Please see below for example:
> select paymmain.agentno,
> sum(case when (PaidFor = 'NSF Rev' or PaidFor = 'Void' or PaidFor
=
> 'Refund' or PaidFor
> = 'Credit' or PaidFor = 'Reversed') then paidamount * -1
> else paidamount end) as paidamount,
> sum(case when (agendefa.captive = 1) then (case when (PaidFor =
'NSF
> Rev' or PaidFor =
> 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> paidamount * -1 else paidamount end) else (case when
> (PaidFor = 'NSF Rev' or PaidFor
> = 'Void' or PaidFor = 'Refund' or PaidFor = 'Credit' or
> PaidFor = 'Reversed') then
> (paidamount * -1) - servicefee else paidamount -
servicefee
> end) end) as sumamount,
> sum(servicefee) as servicefeesum
> from paymmain
> join agendefa on paymmain.agentno = agendefa.agentno
> where paiddate >= '10/05/2004' and paiddate <= '10/05/2004' and
> paymmain.agentno >= 0000
> and paymmain.agentno <= 7999 and paymmain.paidFor <> 'Credit'
> group by paymmain.agentno
> order by paymmain.agentno
> This will return one record per agent. I have additional information that
I
> need about the agent - like bank info and status. So I would like to take
> the above query and use it in another query to get the additional info
> before returning from the stored procedure.
> Also, I tired adding the info to this query but the status is a bit which
it
> gives an error that a bit can not be included ...
> Any help would be appreciate - even if it is a keyword to do a search on.
>
>