Need Help Figuring Out SHOWPLAN_TEXT Help

Jul 23, 2005

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(WHERE:(If ([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]),
WHERE:(len(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(WHERE:(If ([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]),
WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',
isnull([g].[Email], ''), NULL)>1))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([r].[GuestId]),
WHERE:(PROBE([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.

View 1 Replies


ADVERTISEMENT

Figuring Out SPID Or A Running Job

Feb 1, 2002

I trying to find a way to find what the SPID of a given job that is running. I am trying to create a script that will give me the SPID the JOB_ID, and JOB_NAME. The problem comes in that if I use sysprocesses I have to pull the JOB_ID from program_name in sysprocesses and convert it into something then join it to sysjobs. Have not been sucessfull in that conversion. Any Ideas

thanks for any & all help

sean

View 1 Replies View Related

Need Help Figuring Out How Many Tables To Create

Apr 17, 2006

Hi, I have a users table in my SQL Server database. Now, I am looking to create a table (or multiple tables) to allow users to post their weekly events, meetings, activities, and accomplishments to the database. Each Monday morning, each user will enter their new schedule for that week and the previous week's entry will be archived in the database. My question is: what would make more sense? Should I create one big table that would have the following columns:

- Week Number (the current week number in the year)
- User ID
- Events
- Meetings
- Activities
- Accomplishments

And each user would have one row in the database per week.

OR should I create 4 separate tables named Events, Meetings, Activities, Accomplishments. Each of these tables would have the following columns: (for instance, the Events table would contain:)

- eventID (auto incremented number)
- userID
- weekNumber
- event_description

Each time a user adds a new event to their schedule, a new row in the Events table is created. Each time a user adds a new accomplishment, a new row in the Accomplishments table is created. etc., etc.

Which approach seems to make more sense and would be easier to maintain? Also, which one would conserve database space better and result in faster querying.

Thanks, Dave

View 11 Replies View Related

Figuring Out What A Backup File Contains

Nov 19, 2007

I have an old backup file that I am pretty sure contains a table that I am looking for. Is there a way to verify what exactly a backup file contains without restoring it to a test server?

View 4 Replies View Related

Need Help With Figuring Out A Particulart SELECT Statement

Oct 25, 2006

 I have 3 tables, Items, Brands and Categories. Brands and Categories consists of a primary key as well as the Brand/Category name. Items has a bunch of columns as well as two foreign key fields, one for Brands and the other for Categories.I want a SELECT statement that would select each Brand(id and name) as well as the associated categories for each brand (i want this info the menu control) if this was a one-to-many relation, i wouldn't have had any problem... but since this is basically a many-to-many relation, i just can't figure it out  

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved