A fix for slow running SP, but why?

OK, so we have these three SPs that are causing no problem.
We start developing version 2 of the database, and though we've made
minimal changes to these routines, changing parameter one from GUID to
Int, same as we've done to hundreds of routines, suddenly these
routines run 1000x more slowly (both reads and CPU) when called from
the app. The same commands taken from a trace log and run in QA run
at full speed. OK, this happens and is hard to track down, but here's
the kicker. Turns out parameter two was called @.foo_date but was
declared as varchar(32), the better to append a time of day to it when
passed in, I guess. Well, we redeclared it as the datetime it should
have been, and voila, speed is back to normal.
OK, I understand why this might fix things, but I do not understand
why it was not also broken in the V1 database, and not broken even in
V2 when called from QA.
Voodoo is a good explanation, but I'm open to others.
Could it be a change in the clients? Well, we actually invoked these
SPs from a couple of different clients, some were unchanged (well,
except for the new datatype of parameter one!). But that still
wouldn't really explain why it was still OK when called from QA.
Thanks!
JoshWhen you called it from the app you most likely have a parameter object that
tells it what the datatype is. In this case a varchar. When you run it
adhoc in QA it most likely did an implicit conversion to datetime as it
passed it in to the sp. You should always declare the parameters to be
exactly what they need to be to correspond to the columns you are matching
them to in the WHERE clause. Other wise you leave it up to chance as to
what you get.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:ecbvg1h8rrq1e8mc9rm42k98a40g064ac2@.
4ax.com...
> OK, so we have these three SPs that are causing no problem.
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
> Could it be a change in the clients? Well, we actually invoked these
> SPs from a couple of different clients, some were unchanged (well,
> except for the new datatype of parameter one!). But that still
> wouldn't really explain why it was still OK when called from QA.
> Thanks!
> Josh
>|||jxstern (jxstern@.nowhere.xyz) writes:
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
Voodoo is rarely an accurate explanation to these sort of problems. But
unfortunately, the way people present the problems, the amount of
information presented is so incomplete, that there is not much more to
suggest that just voodoo.
Andrew offered some speculations. I could offer a few more about clients
and Query Analyzer having different default settings for ARITHBORT ON.
But really, without the seeing the code, the tables and how it was called,
I really don't want to go into speculation.
But as a hint, performing problems in SQL Server are rarely resovled
by sticking pins into dolls of Bill Gates and Steve Ballmer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>When you called it from the app you most likely have a parameter object tha
t
>tells it what the datatype is. In this case a varchar. When you run it
>adhoc in QA it most likely did an implicit conversion to datetime as it
>passed it in to the sp. You should always declare the parameters to be
>exactly what they need to be to correspond to the columns you are matching
>them to in the WHERE clause. Other wise you leave it up to chance as to
>what you get.
The code in V2 is something like:
create procedure dbo.myfoov2(
myrowid int,
@.foo_date varchar(32)
)
as
begin
declare @.realdate datetime
set @.realdate = @.foo_date + ' 23.59.997'
...
end
The invocation is something like:
exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
The declaration and code are the same in the V1 and V2 databases. I
can't see how QA or ODBC or whatever would know to coerce the string
one way or another.
Sure, *something* must be different, one would suspect some more
direct use of @.foo_date down in the body of the code that, in one
case, the optimizer recognizes as deterministic and in the other case
it does not.
About the only change I know of is that in V1 we were using GUIDs:
create procedure dbo.myfoov1(
myrowid uniqueidentifier, <--
@.foo_date varchar(32)
)
...
And of course the invocation used GUIDs not ints.
But why that should have a side-effect on the coercion farther down of
@.foo_date, ... does that question trigger anything for anybody?
Unlikely, I know, but if it were obvious I wouldn't be asking.
Thanks.
J.|||Well what was the query plan difference between the slow and fast ones? I
have to assume the fast one was using an index and the slow one wasn't.
It's hard to believe the datetime (or varchar) played too much a part since
it isn't used in the WHERE clause. Or at least I assume it doesn't since
you didn't actually show that part<g>. Knowing what the difference in the
plans will mainly dictate where to look.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:8j34h1lj1u80l8jtngtlnfkh4h67oq9onp@.
4ax.com...
> On Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
> The code in V2 is something like:
> create procedure dbo.myfoov2(
> myrowid int,
> @.foo_date varchar(32)
> )
> as
> begin
> declare @.realdate datetime
> set @.realdate = @.foo_date + ' 23.59.997'
> ...
> end
> The invocation is something like:
> exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
> The declaration and code are the same in the V1 and V2 databases. I
> can't see how QA or ODBC or whatever would know to coerce the string
> one way or another.
> Sure, *something* must be different, one would suspect some more
> direct use of @.foo_date down in the body of the code that, in one
> case, the optimizer recognizes as deterministic and in the other case
> it does not.
> About the only change I know of is that in V1 we were using GUIDs:
> create procedure dbo.myfoov1(
> myrowid uniqueidentifier, <--
> @.foo_date varchar(32)
> )
> ...
> And of course the invocation used GUIDs not ints.
> But why that should have a side-effect on the coercion farther down of
> @.foo_date, ... does that question trigger anything for anybody?
> Unlikely, I know, but if it were obvious I wouldn't be asking.
> Thanks.
> J.
>|||On Sun, 28 Aug 2005 19:08:18 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Well what was the query plan difference between the slow and fast ones? I
>have to assume the fast one was using an index and the slow one wasn't.
>It's hard to believe the datetime (or varchar) played too much a part since
>it isn't used in the WHERE clause. Or at least I assume it doesn't since
>you didn't actually show that part<g>. Knowing what the difference in the
>plans will mainly dictate where to look.
The actual routines were long and complex, and I'll have to break this
again (my luck, it won't break!) to get a bad plan to compare.
The question will remain, however, why the plans differed, given the
setup.
J.