Posted by dmarkle on 01/08/07 00:51

First off, I think I see a bug:

"(StartDate BETWEEN @FromDate-365 AND @ToDate-365)"

What happens on leap years?

It's impossible to tell what the performance of this query is going to
be like without knowing how all of the tables and views are structured.
I'd imagine that your biggest issue is going to be with the range of
dates that you're dealing with, but there's no way to know for sure
without knowing the table structure, view definitions, and statistical
distribution of data in the tables.

My advice: If it's not a proc that's being used a lot during the day,
try defining it with the RECOMPILE option (see docs) so that the
procedure gets re-optimized each time you run it.

-Dave





Erland Sommarskog wrote:
> kmounkhaty@yahoo.com (smounkhaty@bremer.com) writes:
> > When I ran my adhoc script below it generated only 45000 reads or 4
> > seconds but when I wrapped it into procedure it took about two minutes
> > or millions of reads. The parameters calling both adhoc and proc are
> > indeed the same. I'm pretty 99.9% sure that the proc does not recompile
> > because I don't mix up between DDL and DML, no temp tables or any thing
> > to cause proc to recompile. The big difference is adhoc used index scan
> > for 45% but proc used bookmark lookup for 75%. Why it's so difference
> > since they both returned the same results?
>
> Run this:
>
> select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
> objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')
>
> If any of these return 0, recreate the procedure and make sure that
> the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
> there is an indexed view or an index on a computed column. They can
> only be used if these two settings are active, and these two are saved
> with the stored procedure.
>
> It could also depend how you pass the parameters, as Hugo discussed,
> but we don't that. If the script is your actual ad-hoc script, it
> may be that it works better if you copy the parameters to local variables.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]