SEO

Here's what's going on. I have a 2 computers (x & y) running SQL2000. I
backed up a copy a DB from x and restored it on y. I have a Stored proc
that runs in under 2 seconds on both x & y when running it through
Analyzer, but when I call this stored proc running it throuhg my C#
winforms app (running on computer z) it takes over 3 minutes on
computer x and under 10 seconds on y.

This stored proc does have a select clause as part of the where clause,
but again it works fine on y.

I've check the indexes and that looks good and I just did a restore of
the database so they should be identical. And I don't think it's a
performance issue because the rest of the app runs actaully a bit
faster on x.

The plans do have differences. Specifically with a mention of
Parallelism in the fast one.

Here are the plans:
X (slow):
|--Sort(DISTINCT ORDER BY[r].[GuestId] ASC, [g].[GuestNote] ASC,
[Expr1005] ASC, [g].[email] ASC, [g].[Phone1] ASC))
|--Compute Scalar(DEFINE[Expr1005]=[g].[LastName]+',
'+[g].[FirstName]))
|--Filter(WHEREIf ([Expr1003] IS NULL) then 0 else
[Expr1003]>=2))
|--Nested Loops(Left Outer Join, OUTER
REFERENCES[r].[GuestId]))
|--Hash Match(Inner Join,
HASH[g].[GuestId])=([r].[GuestId]),
RESIDUAL[r].[GuestId]=[g].[GuestId]))
| |--Clustered Index
Scan(OBJECT[Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),
WHERElen(isnull([g].[email], ''))>6 AND charindex('@',
isnull([g].[email], ''), NULL)>1))
| |--Clustered Index
Seek(OBJECT[Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r]), SEEK[r].[RestId]=1), WHERE[r].[Date]<='Jan 1 2005 12:00AM'
AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWARD)
|--Hash Match(Cache, HASH[r].[GuestId]),
RESIDUAL[r].[GuestId]=[r].[GuestId]))
|--Compute
Scalar(DEFINE[Expr1003]=Convert([Expr1011])))
|--Stream
Aggregate(DEFINE[Expr1011]=Count(*)))
|--Index
Spool(SEEK[r2].[GuestId]=[r].[GuestId]))
|--Clustered Index
Scan(OBJECT[Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r2]))


Y (Fast):
|--Parallelism(Gather Streams)
|--Sort(DISTINCT ORDER BY[r].[GuestId] ASC, [g].[GuestNote]
ASC, [Expr1005] ASC, [g].[email] ASC, [g].[Phone1] ASC))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS[r].[GuestId], [g].[GuestNote], [Expr1005], [g].[email],
[g].[Phone1]))
|--Compute Scalar(DEFINE[Expr1005]=[g].[LastName]+',
'+[g].[FirstName]))
|--Filter(WHEREIf ([Expr1003] IS NULL) then 0
else [Expr1003]>=2))
|--Compute
Scalar(DEFINE[Expr1003]=Convert([Expr1013])))
|--Hash Match Root(Right Outer Join,
HASH[r2].[GuestId])=([r].[GuestId]),
RESIDUAL[r2].[GuestId]=[r2].[GuestId]) AND
([r2].[GuestId]=[r].[GuestId]) DEFINE[Expr1013]=COUNT(*)))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS[r2].[GuestId]))
| |--Clustered Index
Scan(OBJECT[Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r2]))
|--Hash Match Team(Inner Join,
HASH[g].[GuestId])=([r].[GuestId]),
RESIDUAL[r].[GuestId]=[g].[GuestId]))

|--Bitmap(HASH[g].[GuestId]), DEFINE[Bitmap1014]))
|
|--Parallelism(Repartition Streams, PARTITION COLUMNS[g].[GuestId]))
| |--Clustered Index
Scan(OBJECT[Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),
WHERElen(isnull([g].[email], ''))>6 AND charindex('@',
isnull([g].[email], ''), NULL)>1))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS[r].[GuestId]),
WHEREPROBE([Bitmap1014])=TRUE))
|--Clustered Index
Seek(OBJECT[Restaurant].[dbo].[Reservations].[PK_Reservations] AS
[r]), SEEK[r].[RestId]=1), WHERE[r].[Date]<='Jan 1 2005 12:00AM'
AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORW
Any ideas of what I can check for?
Thanks for any help.

Voss (voss_grose@hotmail.com) writes:
> Here's what's going on. I have a 2 computers (x & y) running SQL2000. I
> backed up a copy a DB from x and restored it on y. I have a Stored proc
> that runs in under 2 seconds on both x & y when running it through
> Analyzer, but when I call this stored proc running it throuhg my C#
> winforms app (running on computer z) it takes over 3 minutes on
> computer x and under 10 seconds on y.

Could you post the @@version for both SQL Server boxes?

As I understand there are three plans here. One you get in QA for both
procedures, and then you get two different ones from your C# application?
Of these three, which two did you actually post?

What happens if you from QA first run the command SET ARITHABORT OFF
before you run the procedures?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp