Timeout on SP on first try

[Scott Daveley]

I'm having a strange issue with a stored proc. I'm running it from an ASP
page, it's doing 8 relatively simple union select queries on a table with
~112,000 records. I set NOCOUNT on and call the proc with Set rs =
Connect.Execute("exec up_BasicStats etc...")

NOW - the first hit to the page, it usually runs for about 30 seconds, and
returns nothing. Refresh the page, and it fires right back in ~1.5 seconds.
An attempt 4 hours later produced the recordset in 26seconds, but again, the
refresh returned results in 1.2 seconds

Any ideas? I can provide more info if necessary.

TIA,
Scott

btw, I used to run the same 8 queries seperately using Connect.Execute and
no stored proc on that same page. I encountered had long execute times
(~20seconds) but no timeouts.

[Scott Daveley]

sorry, shoulda mentioned, IIS 5, SQL Server 7, seperate web and db servers,
each 1.5GHz cpu, 512mb physical ram, 1gb virtual memory

"Scott Daveley" <scottd@excelcomm.com> wrote in message
news:S_Spb.699$jW5.16309@twister.tampabay.rr.com.. .

> I'm having a strange issue with a stored proc. I'm running it from an ASP
> page, it's doing 8 relatively simple union select queries on a table with
> ~112,000 records. I set NOCOUNT on and call the proc with Set rs =
> Connect.Execute("exec up_BasicStats etc...")
>
> NOW - the first hit to the page, it usually runs for about 30 seconds, and
> returns nothing. Refresh the page, and it fires right back in ~1.5

seconds.

> An attempt 4 hours later produced the recordset in 26seconds, but again,

the

> refresh returned results in 1.2 seconds
>
> Any ideas? I can provide more info if necessary.
>
> TIA,
> Scott
>
> btw, I used to run the same 8 queries seperately using Connect.Execute and
> no stored proc on that same page. I encountered had long execute times
> (~20seconds) but no timeouts.
>
>
>

[Bob Barrows]

Scott Daveley wrote:

> I'm having a strange issue with a stored proc. I'm running it from
> an ASP page, it's doing 8 relatively simple union select queries on a
> table with ~112,000 records. I set NOCOUNT on and call the proc with
> Set rs = Connect.Execute("exec up_BasicStats etc...")
>
> NOW - the first hit to the page, it usually runs for about 30
> seconds, and returns nothing. Refresh the page, and it fires right
> back in ~1.5 seconds. An attempt 4 hours later produced the recordset
> in 26seconds, but again, the refresh returned results in 1.2 seconds
>
> Any ideas? I can provide more info if necessary.
>
> TIA,
> Scott
>
> btw, I used to run the same 8 queries seperately using
> Connect.Execute and no stored proc on that same page. I encountered
> had long execute times (~20seconds) but no timeouts.

Do you see the same behavior when running from Query Analyzer?

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[William Tasso]

Scott Daveley wrote:

> I'm having a strange issue with a stored proc. I'm running it from
> an ASP page, it's doing 8 relatively simple union select queries on a
> table with ~112,000 records. I set NOCOUNT on and call the proc with
> Set rs = Connect.Execute("exec up_BasicStats etc...")
>
> NOW - the first hit to the page, it usually runs for about 30
> seconds, and returns nothing. Refresh the page, and it fires right
> back in ~1.5 seconds. An attempt 4 hours later produced the recordset
> in 26seconds, but again, the refresh returned results in 1.2 seconds
>
> Any ideas? I can provide more info if necessary.
>

SQL Server is caching the query which is why the return is so good the
second time.

It's a guess, but it sounds like your database could use a little
optimisation. In the meantime you can increase the query timeout - sorry
can't remember where that is.

--
William Tasso - [url]http://WilliamTasso.com[/url]

[Scott Daveley]

Yes, Query Analyzer produces roughly the same result. Long execute the
first time around, very quick the second, and every successive try for the
next several hours. Someone else pointed out the database may need
optimising. Does this mean adding clustered and/or non-clustered indexes?
I've checked, and there's one clustered index on the table in question, but
each of the queries checks at least three different fields (WHERE...). One
an integer, one a string, and one a datetime. Might it be best to do a
non-clustered index on each? My understanding of indexes tho, is that the
more you add, the more it slows down INSERTS or UPDATES. This particular
table has new records added VERY frequently.

Thanks,
Scott



"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OdYWj5woDHA.2676@TK2MSFTNGP11.phx.gbl...

> Scott Daveley wrote:

> > I'm having a strange issue with a stored proc. I'm running it from
> > an ASP page, it's doing 8 relatively simple union select queries on a
> > table with ~112,000 records. I set NOCOUNT on and call the proc with
> > Set rs = Connect.Execute("exec up_BasicStats etc...")
> >
> > NOW - the first hit to the page, it usually runs for about 30
> > seconds, and returns nothing. Refresh the page, and it fires right
> > back in ~1.5 seconds. An attempt 4 hours later produced the recordset
> > in 26seconds, but again, the refresh returned results in 1.2 seconds
> >
> > Any ideas? I can provide more info if necessary.
> >
> > TIA,
> > Scott
> >
> > btw, I used to run the same 8 queries seperately using
> > Connect.Execute and no stored proc on that same page. I encountered
> > had long execute times (~20seconds) but no timeouts.

>
> Do you see the same behavior when running from Query Analyzer?
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

[Bob Barrows]

This is very strange. Are you doing dynamic sql in your procedure? I was
going to suggest using the Index Anaylzer in QA to see if it recommended any
new indexes for you, but this does not seem to be the right situation for
this approach. It sounds as if your procedure is getting a new execution
plan put into cache every few hours.


I suggest posting the text of your procedure over at
microsoft.public.sqlserver.programming and let them take a whack at
analyzing it. You will get quicker help if you include your table creation
scripts (DDL) along with some insert statements to provide sample data.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Scott Daveley wrote:

> Yes, Query Analyzer produces roughly the same result. Long execute
> the first time around, very quick the second, and every successive
> try for the next several hours. Someone else pointed out the
> database may need optimising. Does this mean adding clustered and/or
> non-clustered indexes? I've checked, and there's one clustered index
> on the table in question, but each of the queries checks at least
> three different fields (WHERE...). One an integer, one a string, and
> one a datetime. Might it be best to do a non-clustered index on
> each? My understanding of indexes tho, is that the more you add, the
> more it slows down INSERTS or UPDATES. This particular table has new
> records added VERY frequently.
>
> Thanks,
> Scott
>
>
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OdYWj5woDHA.2676@TK2MSFTNGP11.phx.gbl...

>> Scott Daveley wrote:

>>> I'm having a strange issue with a stored proc. I'm running it from
>>> an ASP page, it's doing 8 relatively simple union select queries on
>>> a table with ~112,000 records. I set NOCOUNT on and call the proc
>>> with Set rs = Connect.Execute("exec up_BasicStats etc...")
>>>
>>> NOW - the first hit to the page, it usually runs for about 30
>>> seconds, and returns nothing. Refresh the page, and it fires right
>>> back in ~1.5 seconds. An attempt 4 hours later produced the
>>> recordset in 26seconds, but again, the refresh returned results in
>>> 1.2 seconds
>>>
>>> Any ideas? I can provide more info if necessary.
>>>
>>> TIA,
>>> Scott
>>>
>>> btw, I used to run the same 8 queries seperately using
>>> Connect.Execute and no stored proc on that same page. I encountered
>>> had long execute times (~20seconds) but no timeouts.

>>
>> Do you see the same behavior when running from Query Analyzer?
>>

[Scott Daveley]

Bob,
I'm passing 3 parameters to the proc. No dynamic stuff, though(also, no
LIKE, NOT, or other bad things). I tried the Index Analyzer, and it
suggested non-clustered indexes on the 3 fields I'm checking. My only issue
with that is the possibility of slowing down the frequent INSERTs that I do
on that table.

I'll try the programming group too. Thanks

Scott

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:#lQ7O$yoDHA.2772@TK2MSFTNGP12.phx.gbl...

> This is very strange. Are you doing dynamic sql in your procedure? I was
> going to suggest using the Index Anaylzer in QA to see if it recommended

any

> new indexes for you, but this does not seem to be the right situation for
> this approach. It sounds as if your procedure is getting a new execution
> plan put into cache every few hours.
>
>
> I suggest posting the text of your procedure over at
> microsoft.public.sqlserver.programming and let them take a whack at
> analyzing it. You will get quicker help if you include your table creation
> scripts (DDL) along with some insert statements to provide sample data.
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
> Scott Daveley wrote:

> > Yes, Query Analyzer produces roughly the same result. Long execute
> > the first time around, very quick the second, and every successive
> > try for the next several hours. Someone else pointed out the
> > database may need optimising. Does this mean adding clustered and/or
> > non-clustered indexes? I've checked, and there's one clustered index
> > on the table in question, but each of the queries checks at least
> > three different fields (WHERE...). One an integer, one a string, and
> > one a datetime. Might it be best to do a non-clustered index on
> > each? My understanding of indexes tho, is that the more you add, the
> > more it slows down INSERTS or UPDATES. This particular table has new
> > records added VERY frequently.
> >
> > Thanks,
> > Scott
> >
> >
> >
> > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:OdYWj5woDHA.2676@TK2MSFTNGP11.phx.gbl...

> >> Scott Daveley wrote:
> >>> I'm having a strange issue with a stored proc. I'm running it from
> >>> an ASP page, it's doing 8 relatively simple union select queries on
> >>> a table with ~112,000 records. I set NOCOUNT on and call the proc
> >>> with Set rs = Connect.Execute("exec up_BasicStats etc...")
> >>>
> >>> NOW - the first hit to the page, it usually runs for about 30
> >>> seconds, and returns nothing. Refresh the page, and it fires right
> >>> back in ~1.5 seconds. An attempt 4 hours later produced the
> >>> recordset in 26seconds, but again, the refresh returned results in
> >>> 1.2 seconds
> >>>
> >>> Any ideas? I can provide more info if necessary.
> >>>
> >>> TIA,
> >>> Scott
> >>>
> >>> btw, I used to run the same 8 queries seperately using
> >>> Connect.Execute and no stored proc on that same page. I encountered
> >>> had long execute times (~20seconds) but no timeouts.
> >>
> >> Do you see the same behavior when running from Query Analyzer?
> >>

>
>
>
>