problem queryplan xml template query

Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert Jan
I don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>
|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way[vbcol=seagreen]
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
for[vbcol=seagreen]
that
>
>
sql