"FOR XML Explicit" why poor performance, and any "quick wins"

Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:

> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, yo
u
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhe
ad
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in messa
ge
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event becau
se
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that the
se
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables
in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:
>