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 Time Out

Hi,

I have a report with query timeout set to 1 sec (I want this for producing timeout exception). When I view it from Business Intelligence Dev. studio's preview tab, it gives me "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" exception.

This is fine..but when i deploy it on report server it doesn't give this exception and opens the report without giving any exception.

in DatabaseQueryTimeout in rsreportserver.config is set to 900sec.

Can someone help me out if I am doing anything wrong or missing something?

And I'will be thankfull to you in advance.

-Thanks

Sounds like more a reporting services issue. Try that group.

query takes 90-120 sec compile/parse time

Hi,
We have a long query that is taking about 180 sec total time to execute and
out of that 12 sec is spent on compile/parse time. I added some index hints
etc and now it is taking only 2 sec to execute but now takes about 90 sec to
compile so total time is 92 sec. What can be the reason for such high compile
time?.....
Then i also add the force order hint to make sure sql server is not taking
to much time to get the optimal execution plan and in this case it still
takes 20 sec to compile(very high) and now takes long time to execute about
35 sec instead of 2 sec...
I also tried using maxdop 1 but that increases the compile time to 120
sec....
Note:
We can’t change this query to stored procedure.
Memory is enough on the machine
Second time for same parameters this query takes 0 compile time but in our
case we need to tune this query for first run so recompilation tuning doesn't
help.
There is no blocking involved as all this is reproducible with single user
on the machine...
Thanks
--Harvinder
What service pack are you on? What kind of hardware? How's your storage
space look? tempdb? Is this the only query you're running on it? It's
hard to even narrow the problem down without specifics...
BTW, your second-time run is probably because the query plan and some of the
tables/results are cached. If you cleared out the cache you might find the
time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
compilation.
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> Hi,
> We have a long query that is taking about 180 sec total time to execute
> and
> out of that 12 sec is spent on compile/parse time. I added some index
> hints
> etc and now it is taking only 2 sec to execute but now takes about 90 sec
> to
> compile so total time is 92 sec. What can be the reason for such high
> compile
> time?.....
> Then i also add the force order hint to make sure sql server is not taking
> to much time to get the optimal execution plan and in this case it still
> takes 20 sec to compile(very high) and now takes long time to execute
> about
> 35 sec instead of 2 sec...
> I also tried using maxdop 1 but that increases the compile time to 120
> sec....
> Note:
> We can't change this query to stored procedure.
> Memory is enough on the machine
> Second time for same parameters this query takes 0 compile time but in our
> case we need to tune this query for first run so recompilation tuning
> doesn't
> help.
> There is no blocking involved as all this is reproducible with single user
> on the machine...
> Thanks
> --Harvinder
>
|||service pack is 3a
this problem is reproducible on different set of hardware i.e. tried on
production (raid 10), development(raid 5)...all the machine have all the
file properly configured...
tempdb is also configured with about 20GB and almost all free....
this probelm can be reproducible with only this query running on machine....
i have never seen any query taking 90-120 sec just for compile time...
"Michael C#" wrote:

> What service pack are you on? What kind of hardware? How's your storage
> space look? tempdb? Is this the only query you're running on it? It's
> hard to even narrow the problem down without specifics...
> BTW, your second-time run is probably because the query plan and some of the
> tables/results are cached. If you cleared out the cache you might find the
> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
> compilation.
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
>
>
|||Whoa... is it taking 12 seconds to compile or 120 seconds to compile? Of
course I'd consider either one unacceptable, but there's a ten-fold
difference there. Is this a particularly complex query with a lot of JOINs?
Perhaps breaking it down into smaller pieces; i.e., VIEWs, or re-writing
with sub-queries, would help you out here. Have you run Profiler to see if
there's a bottleneck in the system somewhere?
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:EBDDDD47-15FB-4067-8B4F-33A3850FF4E8@.microsoft.com...[vbcol=seagreen]
> service pack is 3a
> this problem is reproducible on different set of hardware i.e. tried on
> production (raid 10), development(raid 5)...all the machine have all the
> file properly configured...
> tempdb is also configured with about 20GB and almost all free....
> this probelm can be reproducible with only this query running on
> machine....
> i have never seen any query taking 90-120 sec just for compile time...
> "Michael C#" wrote:

query takes 90-120 sec compile/parse time

Hi,
We have a long query that is taking about 180 sec total time to execute and
out of that 12 sec is spent on compile/parse time. I added some index hints
etc and now it is taking only 2 sec to execute but now takes about 90 sec to
compile so total time is 92 sec. What can be the reason for such high compile
time?.....
Then i also add the force order hint to make sure sql server is not taking
to much time to get the optimal execution plan and in this case it still
takes 20 sec to compile(very high) and now takes long time to execute about
35 sec instead of 2 sec...
I also tried using maxdop 1 but that increases the compile time to 120
sec....
Note:
We canâ't change this query to stored procedure.
Memory is enough on the machine
Second time for same parameters this query takes 0 compile time but in our
case we need to tune this query for first run so recompilation tuning doesn't
help.
There is no blocking involved as all this is reproducible with single user
on the machine...
Thanks
--HarvinderWhat service pack are you on? What kind of hardware? How's your storage
space look? tempdb? Is this the only query you're running on it? It's
hard to even narrow the problem down without specifics...
BTW, your second-time run is probably because the query plan and some of the
tables/results are cached. If you cleared out the cache you might find the
time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
compilation.
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> Hi,
> We have a long query that is taking about 180 sec total time to execute
> and
> out of that 12 sec is spent on compile/parse time. I added some index
> hints
> etc and now it is taking only 2 sec to execute but now takes about 90 sec
> to
> compile so total time is 92 sec. What can be the reason for such high
> compile
> time?.....
> Then i also add the force order hint to make sure sql server is not taking
> to much time to get the optimal execution plan and in this case it still
> takes 20 sec to compile(very high) and now takes long time to execute
> about
> 35 sec instead of 2 sec...
> I also tried using maxdop 1 but that increases the compile time to 120
> sec....
> Note:
> We can't change this query to stored procedure.
> Memory is enough on the machine
> Second time for same parameters this query takes 0 compile time but in our
> case we need to tune this query for first run so recompilation tuning
> doesn't
> help.
> There is no blocking involved as all this is reproducible with single user
> on the machine...
> Thanks
> --Harvinder
>|||service pack is 3a
this problem is reproducible on different set of hardware i.e. tried on
production (raid 10), development(raid 5)...all the machine have all the
file properly configured...
tempdb is also configured with about 20GB and almost all free....
this probelm can be reproducible with only this query running on machine....
i have never seen any query taking 90-120 sec just for compile time...
"Michael C#" wrote:
> What service pack are you on? What kind of hardware? How's your storage
> space look? tempdb? Is this the only query you're running on it? It's
> hard to even narrow the problem down without specifics...
> BTW, your second-time run is probably because the query plan and some of the
> tables/results are cached. If you cleared out the cache you might find the
> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
> compilation.
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> > Hi,
> >
> > We have a long query that is taking about 180 sec total time to execute
> > and
> > out of that 12 sec is spent on compile/parse time. I added some index
> > hints
> > etc and now it is taking only 2 sec to execute but now takes about 90 sec
> > to
> > compile so total time is 92 sec. What can be the reason for such high
> > compile
> > time?.....
> > Then i also add the force order hint to make sure sql server is not taking
> > to much time to get the optimal execution plan and in this case it still
> > takes 20 sec to compile(very high) and now takes long time to execute
> > about
> > 35 sec instead of 2 sec...
> > I also tried using maxdop 1 but that increases the compile time to 120
> > sec....
> >
> > Note:
> > We can't change this query to stored procedure.
> > Memory is enough on the machine
> > Second time for same parameters this query takes 0 compile time but in our
> > case we need to tune this query for first run so recompilation tuning
> > doesn't
> > help.
> > There is no blocking involved as all this is reproducible with single user
> > on the machine...
> >
> > Thanks
> > --Harvinder
> >
> >
>
>|||Whoa... is it taking 12 seconds to compile or 120 seconds to compile? Of
course I'd consider either one unacceptable, but there's a ten-fold
difference there. Is this a particularly complex query with a lot of JOINs?
Perhaps breaking it down into smaller pieces; i.e., VIEWs, or re-writing
with sub-queries, would help you out here. Have you run Profiler to see if
there's a bottleneck in the system somewhere?
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:EBDDDD47-15FB-4067-8B4F-33A3850FF4E8@.microsoft.com...
> service pack is 3a
> this problem is reproducible on different set of hardware i.e. tried on
> production (raid 10), development(raid 5)...all the machine have all the
> file properly configured...
> tempdb is also configured with about 20GB and almost all free....
> this probelm can be reproducible with only this query running on
> machine....
> i have never seen any query taking 90-120 sec just for compile time...
> "Michael C#" wrote:
>> What service pack are you on? What kind of hardware? How's your storage
>> space look? tempdb? Is this the only query you're running on it? It's
>> hard to even narrow the problem down without specifics...
>> BTW, your second-time run is probably because the query plan and some of
>> the
>> tables/results are cached. If you cleared out the cache you might find
>> the
>> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec
>> for
>> compilation.
>> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
>> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
>> > Hi,
>> >
>> > We have a long query that is taking about 180 sec total time to execute
>> > and
>> > out of that 12 sec is spent on compile/parse time. I added some index
>> > hints
>> > etc and now it is taking only 2 sec to execute but now takes about 90
>> > sec
>> > to
>> > compile so total time is 92 sec. What can be the reason for such high
>> > compile
>> > time?.....
>> > Then i also add the force order hint to make sure sql server is not
>> > taking
>> > to much time to get the optimal execution plan and in this case it
>> > still
>> > takes 20 sec to compile(very high) and now takes long time to execute
>> > about
>> > 35 sec instead of 2 sec...
>> > I also tried using maxdop 1 but that increases the compile time to 120
>> > sec....
>> >
>> > Note:
>> > We can't change this query to stored procedure.
>> > Memory is enough on the machine
>> > Second time for same parameters this query takes 0 compile time but in
>> > our
>> > case we need to tune this query for first run so recompilation tuning
>> > doesn't
>> > help.
>> > There is no blocking involved as all this is reproducible with single
>> > user
>> > on the machine...
>> >
>> > Thanks
>> > --Harvinder
>> >
>> >
>>

query takes 90-120 sec compile/parse time

Hi,
We have a long query that is taking about 180 sec total time to execute and
out of that 12 sec is spent on compile/parse time. I added some index hints
etc and now it is taking only 2 sec to execute but now takes about 90 sec to
compile so total time is 92 sec. What can be the reason for such high compil
e
time?.....
Then i also add the force order hint to make sure sql server is not taking
to much time to get the optimal execution plan and in this case it still
takes 20 sec to compile(very high) and now takes long time to execute about
35 sec instead of 2 sec...
I also tried using maxdop 1 but that increases the compile time to 120
sec....
Note:
We can’t change this query to stored procedure.
Memory is enough on the machine
Second time for same parameters this query takes 0 compile time but in our
case we need to tune this query for first run so recompilation tuning doesn'
t
help.
There is no blocking involved as all this is reproducible with single user
on the machine...
Thanks
--HarvinderWhat service pack are you on? What kind of hardware? How's your storage
space look? tempdb? Is this the only query you're running on it? It's
hard to even narrow the problem down without specifics...
BTW, your second-time run is probably because the query plan and some of the
tables/results are cached. If you cleared out the cache you might find the
time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
compilation.
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
> Hi,
> We have a long query that is taking about 180 sec total time to execute
> and
> out of that 12 sec is spent on compile/parse time. I added some index
> hints
> etc and now it is taking only 2 sec to execute but now takes about 90 sec
> to
> compile so total time is 92 sec. What can be the reason for such high
> compile
> time?.....
> Then i also add the force order hint to make sure sql server is not taking
> to much time to get the optimal execution plan and in this case it still
> takes 20 sec to compile(very high) and now takes long time to execute
> about
> 35 sec instead of 2 sec...
> I also tried using maxdop 1 but that increases the compile time to 120
> sec....
> Note:
> We can't change this query to stored procedure.
> Memory is enough on the machine
> Second time for same parameters this query takes 0 compile time but in our
> case we need to tune this query for first run so recompilation tuning
> doesn't
> help.
> There is no blocking involved as all this is reproducible with single user
> on the machine...
> Thanks
> --Harvinder
>|||service pack is 3a
this problem is reproducible on different set of hardware i.e. tried on
production (raid 10), development(raid 5)...all the machine have all the
file properly configured...
tempdb is also configured with about 20GB and almost all free....
this probelm can be reproducible with only this query running on machine...
.
i have never seen any query taking 90-120 sec just for compile time...
"Michael C#" wrote:

> What service pack are you on? What kind of hardware? How's your storage
> space look? tempdb? Is this the only query you're running on it? It's
> hard to even narrow the problem down without specifics...
> BTW, your second-time run is probably because the query plan and some of t
he
> tables/results are cached. If you cleared out the cache you might find th
e
> time goes back (DBCC FREEPROCCACE and DBCC DROPCLEANBUFFERS) to 12 sec for
> compilation.
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:2F167CEA-D078-4054-9BBA-86B73FB236C6@.microsoft.com...
>
>|||Whoa... is it taking 12 seconds to compile or 120 seconds to compile? Of
course I'd consider either one unacceptable, but there's a ten-fold
difference there. Is this a particularly complex query with a lot of JOINs?
Perhaps breaking it down into smaller pieces; i.e., VIEWs, or re-writing
with sub-queries, would help you out here. Have you run Profiler to see if
there's a bottleneck in the system somewhere?
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:EBDDDD47-15FB-4067-8B4F-33A3850FF4E8@.microsoft.com...[vbcol=seagreen]
> service pack is 3a
> this problem is reproducible on different set of hardware i.e. tried on
> production (raid 10), development(raid 5)...all the machine have all the
> file properly configured...
> tempdb is also configured with about 20GB and almost all free....
> this probelm can be reproducible with only this query running on
> machine....
> i have never seen any query taking 90-120 sec just for compile time...
> "Michael C#" wrote:
>

query syntax help

Folks,
Hi, I have the following query:
SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
[BytesSentPerSec] FROM tblHostCapacity001
It returns the following syntax error:
Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
'CardType'.
I understand the reasons for the error - i.e. I need to escape the square
brackets around the [R] in the fieldname...
BUT how do I accomplish this escaping of the [ and ] - normally these chars
delimit a space-contained fieldname. Haven't been able to find any solutions
on the web via google.
Appreciate any advice.
Cheers,
Neil Evans-Mudie
-. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
... -...- -- ..- -.. .. .
e: My@.myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=kYou should really consider renaming the column to something sensible
following the rules of indentifiers. As for a short term workaround, use
double quotes (") instead of square brackets.
The actual alternative to escaping square brackets is to add another closing
square bracket after the existing one like:
CREATE TABLE tbl ( [\\host\NetItfc(Intel [R]] CardType\Bytes Sent/sec] INT )
Note the addition of ']' after [R] but not before it.
Anith|||See if this helps:
-- cast([\\host\NetItfc(Intel[R]] CardType\Bytes Sent/sec] AS float)
create table t1 (
[a[b]]c] int
)
go
select
cast([a[b]]c] as varchar) as c1
from
t1
go
drop table t1
go
AMB
"Neil Evans-Mudie" wrote:

> Folks,
> Hi, I have the following query:
> SELECT cast([\\host\NetItfc(Intel[R] CardType\Bytes Sent/sec] AS float) AS
> [BytesSentPerSec] FROM tblHostCapacity001
> It returns the following syntax error:
> Server: Msg 170, Level 15, State 1, Line 1. Line 1: Incorrect syntax near
> 'CardType'.
> I understand the reasons for the error - i.e. I need to escape the square
> brackets around the [R] in the fieldname...
> BUT how do I accomplish this escaping of the [ and ] - normally these char
s
> delimit a space-contained fieldname. Haven't been able to find any solutio
ns
> on the web via google.
> Appreciate any advice.
> Cheers,
> Neil Evans-Mudie
> -. . .. .-.. / .--. ... -- -. .. -.-. .--. / . ...- .- -.
> ... -...- -- ..- -.. .. .
> e: My@.myorg.com address is a spam sink
> If you wish to email me, try neilevans underscore mudie at hotmail dot com
> w: http://groups.msn.com/TheEvansMudie...new.msnw?&pps=k
>
>