cache of stored procedure

Dear sir,
On one of my page, it often reach a connection time out for the first hit.
After the first hit, that page will be much faster for the second visitor.
However after a while, that page back to original situation, waiting for the
first hit to make it faster.
I think this is because SQL server cache the execution plan after first hit.
Is it possible to make SQL server always cache this execution plan, so that
this page will always be that faster.
Thanks in advanced for your help!!!
regards,
Guoqi Zheng
http://www.ureader.comSQL Server will reuse cached plans whenever it can but sometimes it
needs to recalculate a given plan. It's a fairly complicated area with
many possible scenarios. If you want to know more about query plan
caching and reuse read this whitepaper (it's much better than any
summary I could give):
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server
2005 <http://www.microsoft.com/technet/pr...005/recomp.mspx>
It deals specifically with SQL 2005 but most of it (the theory at least)
applies to SQL 2000 as well.
*mike hodgson*
http://sqlnerd.blogspot.com
guoqi zheng wrote:

>Dear sir,
>On one of my page, it often reach a connection time out for the first hit.
>After the first hit, that page will be much faster for the second visitor.
>However after a while, that page back to original situation, waiting for th
e
>first hit to make it faster.
>I think this is because SQL server cache the execution plan after first hit
.
>Is it possible to make SQL server always cache this execution plan, so that
>this page will always be that faster.
>Thanks in advanced for your help!!!
>regards,
>Guoqi Zheng
>http://www.ureader.com
>|||thanks, I will read it!
regards,
Guoqi Zheng
http://www.ureader.com

cache of stored procedure

Dear sir,
On one of my page, it often reach a connection time out for the first hit.
After the first hit, that page will be much faster for the second visitor.
However after a while, that page back to original situation, waiting for the
first hit to make it faster.
I think this is because SQL server cache the execution plan after first hit.
Is it possible to make SQL server always cache this execution plan, so that
this page will always be that faster.
Thanks in advanced for your help!!!
regards,
Guoqi Zheng
http://www.ureader.com
SQL Server will reuse cached plans whenever it can but sometimes it
needs to recalculate a given plan. It's a fairly complicated area with
many possible scenarios. If you want to know more about query plan
caching and reuse read this whitepaper (it's much better than any
summary I could give):
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server
2005 <http://www.microsoft.com/technet/pro...05/recomp.mspx>
It deals specifically with SQL 2005 but most of it (the theory at least)
applies to SQL 2000 as well.
*mike hodgson*
http://sqlnerd.blogspot.com
guoqi zheng wrote:

>Dear sir,
>On one of my page, it often reach a connection time out for the first hit.
>After the first hit, that page will be much faster for the second visitor.
>However after a while, that page back to original situation, waiting for the
>first hit to make it faster.
>I think this is because SQL server cache the execution plan after first hit.
>Is it possible to make SQL server always cache this execution plan, so that
>this page will always be that faster.
>Thanks in advanced for your help!!!
>regards,
>Guoqi Zheng
>http://www.ureader.com
>
|||thanks, I will read it!
regards,
Guoqi Zheng
http://www.ureader.com

Business Day Calendar table get x days in the past

I have a table of dates and a second column for bit. I need to know the
date of X business days ago
declare @.dateIn datetime, @.days int
set @.datein = '5-17-2006'
set @.days=5
select A.bdate
From BusinessCALendar as A
Inner Join BusinessCALendar as B
on A.bdate <= B.bdate
and A.btype=B.btype
Where A.btype= 1 -- identify a work day as 1
and A.bdate < @.datein
Group by A.bdate
Having count(*) = @.days
My having line voids any return of data. I think this is close, but how do
you walk backwards in rows?
TIAhttp://www.aspfaq.com/2519
"_Stephen" <srussell@.electracash.com> wrote in message
news:OBe6%23oceGHA.4532@.TK2MSFTNGP02.phx.gbl...
>I have a table of dates and a second column for bit. I need to know the
>date of X business days ago
> declare @.dateIn datetime, @.days int
> set @.datein = '5-17-2006'
> set @.days=5
> select A.bdate
> From BusinessCALendar as A
> Inner Join BusinessCALendar as B
> on A.bdate <= B.bdate
> and A.btype=B.btype
> Where A.btype= 1 -- identify a work day as 1
> and A.bdate < @.datein
> Group by A.bdate
> Having count(*) = @.days
>
> My having line voids any return of data. I think this is close, but how
> do you walk backwards in rows?
>
> TIA
>|||http://www.aspfaq.com/2519
"_Stephen" <srussell@.electracash.com> wrote in message
news:OBe6%23oceGHA.4532@.TK2MSFTNGP02.phx.gbl...
>I have a table of dates and a second column for bit. I need to know the
>date of X business days ago
> declare @.dateIn datetime, @.days int
> set @.datein = '5-17-2006'
> set @.days=5
> select A.bdate
> From BusinessCALendar as A
> Inner Join BusinessCALendar as B
> on A.bdate <= B.bdate
> and A.btype=B.btype
> Where A.btype= 1 -- identify a work day as 1
> and A.bdate < @.datein
> Group by A.bdate
> Having count(*) = @.days
>
> My having line voids any return of data. I think this is close, but how
> do you walk backwards in rows?
>
> TIA
>|||> http://www.aspfaq.com/2519
Thanks. I got what was needed there.