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:
>