Query Plans & Statistics

Gurus,

I'm trying to get an application finished that works like Query Analizer in
terms of returning query plans and statistics.

Problem the co-author is having:

>In using ADO to connect to SQL Server, I'm trying to retrieve multiple
>datasets AND statistics that are usually returned via the OnInfoMessage
>event. For those that are familiar with SQL Server, I need the results
>returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options.
>Anyone had any luck doing this before?

Can anyone shed any light on this please?

Thanks.

BTW if anyone wants to take a look at the tool so far - to see what I'm
delving into:
http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1

Much Appreciated!!How about Object Browser pane? This one comes handy very often, you know?|||I'm not sure what you mean by Object Browser Pane - unless you mean something in Microsoft Query analizer...??

The problem I have is that this is a new tool that doesn't have any panes..just pains...arghhh.

Thanks for the help though!|||Here's some more info from the original post

http://81.130.213.94/myforum/forum_posts.asp?TID=26&PN=1

SET STATISTICS IO ON
SELECT 1 FROM sometable

IO statistics are returned as messages in the 2nd (blank) recordset. Good.

SET STATISTICS PROFILE ON
SELECT 1 FROM sometable

Execution plan is returned in the 2nd recordset. Good.

SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable

2 recordsets are returned, the 2nd containing the execution plan. But the
IO statistics are nowhere to be found. Help!

SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
PRINT 1

3 recordsets are returned. The 2nd one contains the execution plan, the
third is blank but contains '1' as a info message. The IO statistics are
nowhere to be found. Help!sql

Query optimization help, please.

Below are two versions of a SQL query. The first is the original query, which was not properly returning all of the required records. I modified this query to be what you see in the second SQL statement below. The problem I'm having now is the extremely long query time for this to execute (it keeps timing out or will take at least 20-30 seconds) from what originally took only a second.

The problem is obviously in where I added the "AND CONTENTID IN (SELECT DISTINCT ContentID from Contents_view WHERE ApplicationID = @.ApplicationID)" to the first portion of the WHERE statements (UserID = @.UserID); as it now has to search the associated view twice for each element instead of once.

Thanks in advance for any advice you can provide.

Original Query (did not pull back the proper results, was missing the user specific records):

CREATE PROCEDURE GetContents
(
@.UserID int,
@.Perms nvarchar(1000) = null,
@.UserGroup nvarchar(100) = null,
@.ApplicationID int
)
AS
SELECT DISTINCT * FROM Contents_view
WHERE (UserID = @.UserID OR (CHARINDEX('''' + PermissionName + '''',@.Perms)>0 ))
AND ContentID IN (SELECT DISTINCT ContentID from Contents_view
 WHERE PermissionName = @.UserGroup
 AND ApplicationID = @.ApplicationID)
ORDER BY Rank ASC

Modified Query (properly pulls back all results and user specific records):

CREATE PROCEDURE GetContents
(
@.UserID int,
@.Perms nvarchar(1000) = null,
@.UserGroup nvarchar(100) = null,
@.ApplicationID int
)
AS
SELECT DISTINCT * FROM Contents_view
WHERE ((UserID = @.UserID AND CONTENTID IN (SELECT DISTINCT ContentID from Contents_view
WHERE ApplicationID = @.ApplicationID))
OR ((CHARINDEX('''' + PermissionName + '''',@.Brands)>0 ))
AND ContentID IN (SELECT DISTINCT ContentID from Contents_view
 WHERE PermissionName = @.UserGroup
 AND ApplicationID = @.ApplicationID)
 ORDER BY Rank ASC

Not really having any idea exactly what you are doing, a guess is that the use of CHARINDEX() in your WHERE clause of your subquery is what is slowing you down. THis will force a table scan, since no index will help. Where is @.Brands coming from?

You should reqork the database if possible so that there is a direct way to determine what you are trying to determine. I presume you have a field with permission names comma delimited or similar?|||The @.Brands should actually be @.Perms, like in the first query.

What is happening, is there is a view that combines the related data of two seperate tables into one Contents_view that contains each content record in the database with all of its pertinent information.

The query itself is pulling back the content for a particilar page based on two things.
1) If the supplied userID and applicationID are present in a single record
2) If the usergroup and associated permissions are present in a single record

CHARINDEX() is searching a comma delimited permissions string that is passed to the SPROC.|||ok. Split up the string passed in, create either a temp table (SQL Server 7) or a user defined function that returns a table object (SQL 2000). Then do a join with the temp table or table object. Then you will not need CHARINDEX() which is being evaluated on each and every record in the table.|||Well ... How many rows are returned would have been a very good indicator ...

1. Since you are using an IN clause you DONOT have to use the DISTINCT in the inner co-related sub query ... These are not required.

Change the above query to:

SELECT DISTINCT * FROM Contents_view
WHERE ((UserID = @.UserID AND CONTENTID IN (SELECT ContentID from Contents_view
WHERE (ApplicationID = @.ApplicationID) or (PermissionName = @.UserGroup and ApplicationID = @.ApplicationID))
OR ((CHARINDEX('''' + PermissionName + '''',@.Brands)>0 ))
ORDER BY Rank ASC

PS: Not tested. See if this gives you the desired results and performance ?

Note that use of string operations also consume valuable CPU cycles and hence hamper performance ...

Query Not Returning Values and process is waiting on CXPACKET

Good Morning All,
I have a query from a user that I have been asked to take a look at. I
have narrowed down the issue to one of the parameters in the query.
When the query covers 2002 thru 2003 one execution plan is issued when
the query covers only a portion of 2003 a second execution plan is
issued. The issue is that the 2002 thru 2003 executes fine, the query
that executes a portion over a portion of 2003 data does not. If I
check the process it has an item that has a wait type of CXPACKET and it
just hangs never returning. I was wondering if anyone has seen this
kind of behavior before. The two different plans where created by only
changing the data range.
An advise would help.
Thanks
Show Plan text (Non Working)
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[vendor_ident]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[opco_ident]))
| |
|--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
[fact_sales]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
| | | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]), ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
[fact_sales]),
SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
[fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
[fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
|
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like([Dim_Vendor].[opco_vendor_name],
'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
[Dim_Vendor].[opco_vendor_num]<>3136) AND
[Dim_Vendor].[opco_vendor_num]<>3571) AND
[Dim_Vendor].[opco_vendor_num]<>3572) AND
[Dim_Vendor].[opco_vendor_num]<>3573) AND
[Dim_Vendor].[opco_vendor_num]<>3673) AND
[Dim_Vendor].[opco_vendor_num]<>3860) AND
[Dim_Vendor].[opco_vendor_num]<>3861) AND
[Dim_Vendor].[opco_vendor_num]<>4290) AND
[Dim_Vendor].[opco_vendor_num]<>4530) AND
[Dim_Vendor].[opco_vendor_num]<>5373) AND
[Dim_Vendor].[opco_vendor_num]<>5760) AND
[Dim_Vendor].[opco_vendor_num]<>5797) AND
[Dim_Vendor].[opco_vendor_num]<>6064) AND
[Dim_Vendor].[opco_vendor_num]<>6065) AND
[Dim_Vendor].[opco_vendor_num]<>6066) AND
[Dim_Vendor].[opco_vendor_num]<>6073) AND
[Dim_Vendor].[opco_vendor_num]<>6329) AND
[Dim_Vendor].[opco_vendor_num]<>6525) AND
[Dim_Vendor].[opco_vendor_num]<>6607) AND
[Dim_Vendor].[opco_vendor_num]<>6684) AND
[Dim_Vendor].[opco_vendor_num]<>7240) AND
[Dim_Vendor].[opco_vendor_num]<>7241) AND
[Dim_Vendor].[opco_vendor_num]<>8325) AND
[Dim_Vendor].[opco_vendor_num]<>8326) AND
[Dim_Vendor].[opco_vendor_num]<>16229) AND
[Dim_Vendor].[opco_vendor_num]<>16230) AND
[Dim_Vendor].[opco_vendor_num]<>17756) AND
[Dim_Vendor].[opco_vendor_num]<>18121) AND
[Dim_Vendor].[opco_vendor_num]<>19552) AND
[Dim_Vendor].[opco_vendor_num]<>23852) AND
[Dim_Vendor].[opco_vendor_num]<>23853) AND
[Dim_Vendor].[opco_vendor_num]<>25307) AND
[Dim_Vendor].[opco_vendor_num]<>25558) AND
[Dim_Vendor].[opco_vendor_num]<>30003) AND
[Dim_Vendor].[opco_vendor_num]<>30004) AND
[Dim_Vendor].[opco_vendor_num]<>33068) AND
[Dim_Vendor].[opco_vendor_num]<>36230) AND
[Dim_Vendor].[opco_vendor_num]<>36236) AND
[Dim_Vendor].[opco_vendor_num]<>37756) AND
[Dim_Vendor].[opco_vendor_num]<>49365) AND
[Dim_Vendor].[opco_vendor_num]<>62380) AND
[Dim_Vendor].[opco_vendor_num]<>69485))
| |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]),
SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
[Dim_Vendor].[opco_vendor_num]<>372) AND
[Dim_Vendor].[opco_vendor_num]<>429) AND
[Dim_Vendor].[opco_vendor_num]<>448) AND
[Dim_Vendor].[opco_vendor_num]<>600) AND
[Dim_Vendor].[opco_vendor_num]<>617) AND
[Dim_Vendor].[opco_vendor_num]<>641) AND
[Dim_Vendor].[opco_vendor_num]<>713) AND
[Dim_Vendor].[opco_vendor_num]<>1325) AND
[Dim_Vendor].[opco_vendor_num]<>1672) AND
[Dim_Vendor].[opco_vendor_num]<>1850) AND
[Dim_Vendor].[opco_vendor_num]<>1940) AND
[Dim_Vendor].[opco_vendor_num]<>2210) AND
[Dim_Vendor].[opco_vendor_num]<>2215) AND
[Dim_Vendor].[opco_vendor_num]<>3130) AND
[Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED FORWARD)
Show plan text (working)
|--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Hash Match(Inner Join,
HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
|--Parallelism(Broadcast)
| |--Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
|--Hash Match(Inner Join,
HASH:([fact_sales].[opco_num],
[fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
[dim_inv_info_tbl].[item_num]),
RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
[fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
| |--Hash Match(Inner Join,
HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident])=([fact_sales].[opco_num],
[fact_sales].[item_ident]),
RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
[Dim_item].[item_ident]=[fact_sales].[item_ident]))
| |--Bitmap(HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
| | |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident]))
| | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
WHERE:(PROBE([Bitmap1006])=TRUE))
| |--Hash Match(Inner Join,
HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
| |--Parallelism(Broadcast)
| | |--Nested Loops(Left
Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
| |
|--Parallelism(Gather Streams)
| | |
|--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
| | |
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
| | |--Constant Scan
| |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
[fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]))This is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B993.52F36600
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Sounds like parallelism is the problem. Try adding the following to the end
of your query:
OPTION (MAXDOP 1)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"sfibich" <sfibich@.pfgc.com> wrote in message
news:#ISwrybuDHA.2180@.TK2MSFTNGP09.phx.gbl...
Good Morning All,
I have a query from a user that I have been asked to take a look at. I
have narrowed down the issue to one of the parameters in the query.
When the query covers 2002 thru 2003 one execution plan is issued when
the query covers only a portion of 2003 a second execution plan is
issued. The issue is that the 2002 thru 2003 executes fine, the query
that executes a portion over a portion of 2003 data does not. If I
check the process it has an item that has a wait type of CXPACKET and it
just hangs never returning. I was wondering if anyone has seen this
kind of behavior before. The two different plans where created by only
changing the data range.
An advise would help.
Thanks
Show Plan text (Non Working)
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[vendor_ident]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([fact_sales].[opco_ident]))
| |
|--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
| | | |--Bookmark
Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
[fact_sales]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
| | | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]), ORDERED FORWARD)
| | | |--Index
Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
[fact_sales]),
SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
[fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
[fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
| | |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
|
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like
([Dim_Vendor].[opco_vendor_name],
'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
[Dim_Vendor].[opco_vendor_num]<>3136) AND
[Dim_Vendor].[opco_vendor_num]<>3571) AND
[Dim_Vendor].[opco_vendor_num]<>3572) AND
[Dim_Vendor].[opco_vendor_num]<>3573) AND
[Dim_Vendor].[opco_vendor_num]<>3673) AND
[Dim_Vendor].[opco_vendor_num]<>3860) AND
[Dim_Vendor].[opco_vendor_num]<>3861) AND
[Dim_Vendor].[opco_vendor_num]<>4290) AND
[Dim_Vendor].[opco_vendor_num]<>4530) AND
[Dim_Vendor].[opco_vendor_num]<>5373) AND
[Dim_Vendor].[opco_vendor_num]<>5760) AND
[Dim_Vendor].[opco_vendor_num]<>5797) AND
[Dim_Vendor].[opco_vendor_num]<>6064) AND
[Dim_Vendor].[opco_vendor_num]<>6065) AND
[Dim_Vendor].[opco_vendor_num]<>6066) AND
[Dim_Vendor].[opco_vendor_num]<>6073) AND
[Dim_Vendor].[opco_vendor_num]<>6329) AND
[Dim_Vendor].[opco_vendor_num]<>6525) AND
[Dim_Vendor].[opco_vendor_num]<>6607) AND
[Dim_Vendor].[opco_vendor_num]<>6684) AND
[Dim_Vendor].[opco_vendor_num]<>7240) AND
[Dim_Vendor].[opco_vendor_num]<>7241) AND
[Dim_Vendor].[opco_vendor_num]<>8325) AND
[Dim_Vendor].[opco_vendor_num]<>8326) AND
[Dim_Vendor].[opco_vendor_num]<>16229) AND
[Dim_Vendor].[opco_vendor_num]<>16230) AND
[Dim_Vendor].[opco_vendor_num]<>17756) AND
[Dim_Vendor].[opco_vendor_num]<>18121) AND
[Dim_Vendor].[opco_vendor_num]<>19552) AND
[Dim_Vendor].[opco_vendor_num]<>23852) AND
[Dim_Vendor].[opco_vendor_num]<>23853) AND
[Dim_Vendor].[opco_vendor_num]<>25307) AND
[Dim_Vendor].[opco_vendor_num]<>25558) AND
[Dim_Vendor].[opco_vendor_num]<>30003) AND
[Dim_Vendor].[opco_vendor_num]<>30004) AND
[Dim_Vendor].[opco_vendor_num]<>33068) AND
[Dim_Vendor].[opco_vendor_num]<>36230) AND
[Dim_Vendor].[opco_vendor_num]<>36236) AND
[Dim_Vendor].[opco_vendor_num]<>37756) AND
[Dim_Vendor].[opco_vendor_num]<>49365) AND
[Dim_Vendor].[opco_vendor_num]<>62380) AND
[Dim_Vendor].[opco_vendor_num]<>69485))
| |--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]),
SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
[Dim_Vendor].[opco_vendor_num]<>372) AND
[Dim_Vendor].[opco_vendor_num]<>429) AND
[Dim_Vendor].[opco_vendor_num]<>448) AND
[Dim_Vendor].[opco_vendor_num]<>600) AND
[Dim_Vendor].[opco_vendor_num]<>617) AND
[Dim_Vendor].[opco_vendor_num]<>641) AND
[Dim_Vendor].[opco_vendor_num]<>713) AND
[Dim_Vendor].[opco_vendor_num]<>1325) AND
[Dim_Vendor].[opco_vendor_num]<>1672) AND
[Dim_Vendor].[opco_vendor_num]<>1850) AND
[Dim_Vendor].[opco_vendor_num]<>1940) AND
[Dim_Vendor].[opco_vendor_num]<>2210) AND
[Dim_Vendor].[opco_vendor_num]<>2215) AND
[Dim_Vendor].[opco_vendor_num]<>3130) AND
[Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED FORWARD)
Show plan text (working)
|--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
[dim_inv_info_tbl].[case_qty_on_hand] ASC))
|--Hash Match(Inner Join,
HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
|--Parallelism(Broadcast)
| |--Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
|--Hash Match(Inner Join,
HASH:([fact_sales].[opco_num],
[fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
[dim_inv_info_tbl].[item_num]),
RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
[fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
| |--Hash Match(Inner Join,
HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident])=([fact_sales].[opco_num],
[fact_sales].[item_ident]),
RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
[Dim_item].[item_ident]=[fact_sales].[item_ident]))
| |--Bitmap(HASH:([Dim_item].[opco_num],
[Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
| | |--Parallelism(Repartition
Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident]))
| | |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
| |--Parallelism(Repartition Streams,
PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
WHERE:(PROBE([Bitmap1006])=TRUE))
| |--Hash Match(Inner Join,
HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
| |--Parallelism(Broadcast)
| | |--Nested Loops(Left
Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
| |
|--Parallelism(Gather Streams)
| | |
|--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
| | |
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
[Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
| | |--Constant Scan
| |--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
[fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
|--Clustered Index
Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
AS [dim_inv_info_tbl]))
--=_NextPart_000_02C4_01C3B993.52F36600
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Sounds like parallelism is the =problem. Try adding the following to the end of your query:
OPTION (MAXDOP 1)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"sfibich" wrote in message news:#ISwrybuDHA.2180=@.TK2MSFTNGP09.phx.gbl...Good Morning All,I have a query from a user that I have been asked to =take a look at. I have narrowed down the issue to one of the parameters in =the query. When the query covers 2002 thru 2003 one execution plan is =issued when the query covers only a portion of 2003 a second execution plan =is issued. The issue is that the 2002 thru 2003 executes fine, =the query that executes a portion over a portion of 2003 data does not. =If I check the process it has an item that has a wait type of CXPACKET =and it just hangs never returning. I was wondering if anyone has seen =this kind of behavior before. The two different plans where created =by only changing the data range.An advise would help.ThanksShow Plan text (Non =Working) |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC, =[Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) =|--Parallelism(Gather Streams) &=nbsp; |--Nested Loops(Inner Join, OUTER =REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num])) &n=bsp; |--Nested Loops(Inner Join, OUTER REFERENCES:([fact_sales].[vendor_ident])) = &=nbsp; | |--Nested Loops(Inner Join, OUTER REFERENCES:([fact_sales].[opco_ident])) &n=bsp; &nb=sp; | | |--Filter(WHERE:([fact_sales].[item_num]=3D[dim_inv_info_tbl].[item_n=um])) &nbs=p; | | | |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS [fact_sales])) &nb=sp; | |
20
| |--Nested Loops(Inner =Join, OUTER REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH) = | | | = |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_=01] AS [dim_inv_info_tbl]), ORDERED FORWARD) &=nbsp; | | | = |--Index =Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS [fact_sales]), SEEK:([fact_sales].[opco_num]=3D[dim_inv_info_tbl].[opco_num] AND [fact_sales].[calendar_date] >=3D 'Nov 5 2003 12:00AM' AND [fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD) &=nbsp; | | |--Clustered Index Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]), =SEEK:([Dim_OPCO].[opco_ident]=3D[fact_sales].[opco_ident]) ORDERED FORWARD) &=nbsp; | |--Filter(WHERE:(((((((((((((((((((((((((((((((((((((((((((((((((((((=(((like([Dim_Vendor].[opco_vendor_name], 'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], ='Awrey%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND [Dim_Vendor].[opco_vendor_num]3136) AND [Dim_Vendor].[opco_vendor_num]3571) AND [Dim_Vendor].[opco_vendor_num]3572) AND [Dim_Vendor].[opco_vendor_num]3573) AND [Dim_Vendor].[opco_vendor_num]3673) AND [Dim_Vendor].[opco_vendor_num]3860) AND [Dim_Vendor].[opco_vendor_num]3861) AND [Dim_Vendor].[opco_vendor_num]4290) AND [Dim_Vendor].[opco_vendor_num]4530) AND [Dim_Vendor].[opco_vendor_num]5373) AND [Dim_Vendor].[opco_vendor_num]5760) AND [Dim_Vendor].[opco_vendor_num]5797) AND [Dim_Vendor].[opco_vendor_num]6064) AND [Dim_Vendor].[opco_vendor_num]6065) AND [Dim_Vendor].[opco_vendor_num]6066) AND [Dim_Vendor].[opco_vendor_num]6073) AND [Dim_Vendor].[opco_vendor_num]6329) AND [Dim_Vendor].[opco_vendor_num]6525) AND [Dim_Vendor].[opco_vendor_num]6607) AND [Dim_Vendor].[opco_vendor_num]6684) AND [Dim_Vendor].[opco_vendor_num]7240) AND [Dim_Vendor].[opco_vendor_num]7241) AND [Dim_Vendor].[opco_vendor_num]8325) AND [Dim_Vendor].[opco_vendor_num]8326) AND [Dim_Vendor].[opco_vendor_num]16229) AND [Dim_Vendor].[opco_vendor_num]16230) AND [Dim_Vendor].[opco_vendor_num]17756) AND [Dim_Vendor].[opco_vendor_num]18121) AND [Dim_Vendor].[opco_vendor_num]19552) AND [Dim_Vendor].[opco_vendor_num]23852) AND [Dim_Vendor].[opco_vendor_num]23853) AND [Dim_Vendor].[opco_vendor_num]25307) AND [Dim_Vendor].[opco_vendor_num]25558) AND [Dim_Vendor].[opco_vendor_num]30003) AND [Dim_Vendor].[opco_vendor_num]30004) AND [Dim_Vendor].[opco_vendor_num]33068) AND [Dim_Vendor].[opco_vendor_num]36230) AND [Dim_Vendor].[opco_vendor_num]36236) AND [Dim_Vendor].[opco_vendor_num]37756) AND [Dim_Vendor].[opco_vendor_num]49365) AND [Dim_Vendor].[opco_vendor_num]62380) AND [Dim_Vendor].[opco_vendor_num]69485)) &n=bsp; &nb=sp; | |--Clustered Index Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS [Dim_Vendor]), SEEK:([Dim_Vendor].[vendor_ident]=3D[fact_sales].[vendor_ident]), WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]173 AND [Dim_Vendor].[opco_vendor_num]372) AND [Dim_Vendor].[opco_vendor_num]429) AND [Dim_Vendor].[opco_vendor_num]448) AND [Dim_Vendor].[opco_vendor_num]600) AND [Dim_Vendor].[opco_vendor_num]617) AND [Dim_Vendor].[opco_vendor_num]641) AND [Dim_Vendor].[opco_vendor_num]713) AND [Dim_Vendor].[opco_vendor_num]1325) AND [Dim_Vendor].[opco_vendor_num]1672) AND [Dim_Vendor].[opco_vendor_num]1850) AND [Dim_Vendor].[opco_vendor_num]1940) AND [Dim_Vendor].[opco_vendor_num]2210) AND [Dim_Vendor].[opco_vendor_num]2215) AND [Dim_Vendor].[opco_vendor_num]3130) AND [Dim_Vendor].[opco_vendor_num]3131) ORDERED FORWARD) &=nbsp; |--Clustered Index =Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]), =SEEK:([Dim_item].[item_ident]=3D[fact_sales].[item_ident]), WHERE:([Dim_item].[opco_num]=3D[dim_inv_info_tbl].[opco_num]) =ORDERED FORWARD)Show plan text (working) |--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC, [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC, [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC, =[dim_inv_info_tbl].[case_qty_on_hand] ASC)) &nbs=p; |--Hash Match(Inner Join, HASH:([Dim_OPCO].[opco_ident])=3D([fact_sales].[opco_ident]), RESIDUAL:([fact_sales].[opco_ident]=3D[Dim_OPCO].[opco_ident]))&n=bsp; &nb=sp; |--Parallelism(Broadcast) &n=bsp; | |--Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO])) &nb=sp; |--Hash Match(Inner Join, HASH:([fact_sales].[opco_num], [fact_sales].[item_num])=3D([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]), RESIDUAL:([dim_inv_info_tbl].[opco_num]=3D[fact_sales].[opco_num] =AND [fact_sales].[item_num]=3D[dim_inv_info_tbl].[item_num])) &n=bsp; &nb=sp; |--Parallelism(Repartition Streams, PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num])) &n=bsp; &nb=sp; | |--Hash Match(Inner Join, =HASH:([Dim_item].[opco_num], [Dim_item].[item_ident])=3D([fact_sales].[opco_num], [fact_sales].[item_ident]), RESIDUAL:([Dim_item].[opco_num]=3D[fact_sales].[opco_num] AND [Dim_item].[item_ident]=3D[fact_sales].[item_ident])) = &=nbsp; | |--Bitmap(HASH:([Dim_item].[opco_num], [Dim_item].[item_ident]), DEFINE:([Bitmap1006]))  =; = | | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Dim_item].[opco_num], [Dim_item].[item_ident])) &n=bsp; &nb=sp; | | |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item])) &nb=sp; &nbs=p; | =|--Parallelism(Repartition Streams, PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]), WHERE:(PROBE([Bitmap1006])=3DTRUE)) = &=nbsp; | = |--Hash Match(Inner Join, HASH:([Dim_Vendor].[vendor_ident])=3D([fact_sales].[vendor_ident]), RESIDUAL:([fact_sales].[vendor_ident]=3D[Dim_Vendor].[vendor_ident]))= &nb=sp; | = |--Parallelism(Broadcast) &n=bsp; &nb=sp; | = | |--Nested Loops(Left Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=3D[Expr1005]))  =; = | = | |--Parallelism(Gather Streams) &=nbsp; | = | | |--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], ='All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', =NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL))) &n=bsp; | = | | |--Clustered =Index Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS [Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]173)) &=nbsp; &n=bsp; | = | |--Constant Scan  =; | = |--Clustered Index =Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS [fact_sales]), =WHERE:([fact_sales].[calendar_date]>=3D'Nov 5 2002 12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))  =; = |--Parallelism(Repartition Streams, PARTITION COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num])) &n=bsp; &nb=sp; |--Clustered Index Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_=01] AS [dim_inv_info_tbl]))

---

--=_NextPart_000_02C4_01C3B993.52F36600--|||1. I would update stats to make sure that's not an issue...
2. CXPacket indicates a waittime assoicated with parrallel query plans. Try
setting the MAXDOP to 1 as a test. The slow plan is probably parallel and
the fast plan may be serial.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"sfibich" <sfibich@.pfgc.com> wrote in message
news:%23ISwrybuDHA.2180@.TK2MSFTNGP09.phx.gbl...
> Good Morning All,
> I have a query from a user that I have been asked to take a look at. I
> have narrowed down the issue to one of the parameters in the query.
> When the query covers 2002 thru 2003 one execution plan is issued when
> the query covers only a portion of 2003 a second execution plan is
> issued. The issue is that the 2002 thru 2003 executes fine, the query
> that executes a portion over a portion of 2003 data does not. If I
> check the process it has an item that has a wait type of CXPACKET and it
> just hangs never returning. I was wondering if anyone has seen this
> kind of behavior before. The two different plans where created by only
> changing the data range.
> An advise would help.
> Thanks
>
> Show Plan text (Non Working)
> |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Parallelism(Gather Streams)
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[item_ident], [dim_inv_info_tbl].[opco_num]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[vendor_ident]))
> | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([fact_sales].[opco_ident]))
> | |
> |--Filter(WHERE:([fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
> | | | |--Bookmark
> Lookup(BOOKMARK:([Bmk1001]), OBJECT:([pfg_dm].[dbo].[fact_sales] AS
> [fact_sales]))
> | | | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([dim_inv_info_tbl].[opco_num]) WITH PREFETCH)
> | | | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
> AS [dim_inv_info_tbl]), ORDERED FORWARD)
> | | | |--Index
> Seek(OBJECT:([pfg_dm].[dbo].[fact_sales].[idx_fact_sales_06] AS
> [fact_sales]),
> SEEK:([fact_sales].[opco_num]=[dim_inv_info_tbl].[opco_num] AND
> [fact_sales].[calendar_date] >= 'Nov 5 2003 12:00AM' AND
> [fact_sales].[calendar_date] < 'Nov 19 2003 12:00AM') ORDERED FORWARD)
> | | |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[PK_Dim_OPCO] AS [Dim_OPCO]),
> SEEK:([Dim_OPCO].[opco_ident]=[fact_sales].[opco_ident]) ORDERED FORWARD)
> |
>
|--Filter(WHERE:((((((((((((((((((((((((((((((((((((((((((((((((((((((((like
([Dim_Vendor].[opco_vendor_name],
> 'All Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%',
> NULL)) OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)) AND
> [Dim_Vendor].[opco_vendor_num]<>3136) AND
> [Dim_Vendor].[opco_vendor_num]<>3571) AND
> [Dim_Vendor].[opco_vendor_num]<>3572) AND
> [Dim_Vendor].[opco_vendor_num]<>3573) AND
> [Dim_Vendor].[opco_vendor_num]<>3673) AND
> [Dim_Vendor].[opco_vendor_num]<>3860) AND
> [Dim_Vendor].[opco_vendor_num]<>3861) AND
> [Dim_Vendor].[opco_vendor_num]<>4290) AND
> [Dim_Vendor].[opco_vendor_num]<>4530) AND
> [Dim_Vendor].[opco_vendor_num]<>5373) AND
> [Dim_Vendor].[opco_vendor_num]<>5760) AND
> [Dim_Vendor].[opco_vendor_num]<>5797) AND
> [Dim_Vendor].[opco_vendor_num]<>6064) AND
> [Dim_Vendor].[opco_vendor_num]<>6065) AND
> [Dim_Vendor].[opco_vendor_num]<>6066) AND
> [Dim_Vendor].[opco_vendor_num]<>6073) AND
> [Dim_Vendor].[opco_vendor_num]<>6329) AND
> [Dim_Vendor].[opco_vendor_num]<>6525) AND
> [Dim_Vendor].[opco_vendor_num]<>6607) AND
> [Dim_Vendor].[opco_vendor_num]<>6684) AND
> [Dim_Vendor].[opco_vendor_num]<>7240) AND
> [Dim_Vendor].[opco_vendor_num]<>7241) AND
> [Dim_Vendor].[opco_vendor_num]<>8325) AND
> [Dim_Vendor].[opco_vendor_num]<>8326) AND
> [Dim_Vendor].[opco_vendor_num]<>16229) AND
> [Dim_Vendor].[opco_vendor_num]<>16230) AND
> [Dim_Vendor].[opco_vendor_num]<>17756) AND
> [Dim_Vendor].[opco_vendor_num]<>18121) AND
> [Dim_Vendor].[opco_vendor_num]<>19552) AND
> [Dim_Vendor].[opco_vendor_num]<>23852) AND
> [Dim_Vendor].[opco_vendor_num]<>23853) AND
> [Dim_Vendor].[opco_vendor_num]<>25307) AND
> [Dim_Vendor].[opco_vendor_num]<>25558) AND
> [Dim_Vendor].[opco_vendor_num]<>30003) AND
> [Dim_Vendor].[opco_vendor_num]<>30004) AND
> [Dim_Vendor].[opco_vendor_num]<>33068) AND
> [Dim_Vendor].[opco_vendor_num]<>36230) AND
> [Dim_Vendor].[opco_vendor_num]<>36236) AND
> [Dim_Vendor].[opco_vendor_num]<>37756) AND
> [Dim_Vendor].[opco_vendor_num]<>49365) AND
> [Dim_Vendor].[opco_vendor_num]<>62380) AND
> [Dim_Vendor].[opco_vendor_num]<>69485))
> | |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
> [Dim_Vendor]),
> SEEK:([Dim_Vendor].[vendor_ident]=[fact_sales].[vendor_ident]),
> WHERE:((((((((((((((([Dim_Vendor].[opco_vendor_num]<>173 AND
> [Dim_Vendor].[opco_vendor_num]<>372) AND
> [Dim_Vendor].[opco_vendor_num]<>429) AND
> [Dim_Vendor].[opco_vendor_num]<>448) AND
> [Dim_Vendor].[opco_vendor_num]<>600) AND
> [Dim_Vendor].[opco_vendor_num]<>617) AND
> [Dim_Vendor].[opco_vendor_num]<>641) AND
> [Dim_Vendor].[opco_vendor_num]<>713) AND
> [Dim_Vendor].[opco_vendor_num]<>1325) AND
> [Dim_Vendor].[opco_vendor_num]<>1672) AND
> [Dim_Vendor].[opco_vendor_num]<>1850) AND
> [Dim_Vendor].[opco_vendor_num]<>1940) AND
> [Dim_Vendor].[opco_vendor_num]<>2210) AND
> [Dim_Vendor].[opco_vendor_num]<>2215) AND
> [Dim_Vendor].[opco_vendor_num]<>3130) AND
> [Dim_Vendor].[opco_vendor_num]<>3131) ORDERED FORWARD)
> |--Clustered Index
> Seek(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]),
> SEEK:([Dim_item].[item_ident]=[fact_sales].[item_ident]),
> WHERE:([Dim_item].[opco_num]=[dim_inv_info_tbl].[opco_num]) ORDERED
FORWARD)
>
> Show plan text (working)
> |--Parallelism(Gather Streams, ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Sort(ORDER BY:([Dim_OPCO].[opco_num] ASC,
> [Dim_Vendor].[opco_vendor_name] ASC, [Dim_item].[opco_item_num] ASC,
> [dim_inv_info_tbl].[case_qty_on_hand] ASC))
> |--Hash Match(Inner Join,
> HASH:([Dim_OPCO].[opco_ident])=([fact_sales].[opco_ident]),
> RESIDUAL:([fact_sales].[opco_ident]=[Dim_OPCO].[opco_ident]))
> |--Parallelism(Broadcast)
> | |--Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_OPCO].[idx_dim_opco_01] AS [Dim_OPCO]))
> |--Hash Match(Inner Join,
> HASH:([fact_sales].[opco_num],
> [fact_sales].[item_num])=([dim_inv_info_tbl].[opco_num],
> [dim_inv_info_tbl].[item_num]),
> RESIDUAL:([dim_inv_info_tbl].[opco_num]=[fact_sales].[opco_num] AND
> [fact_sales].[item_num]=[dim_inv_info_tbl].[item_num]))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_num]))
> | |--Hash Match(Inner Join,
> HASH:([Dim_item].[opco_num],
> [Dim_item].[item_ident])=([fact_sales].[opco_num],
> [fact_sales].[item_ident]),
> RESIDUAL:([Dim_item].[opco_num]=[fact_sales].[opco_num] AND
> [Dim_item].[item_ident]=[fact_sales].[item_ident]))
> | |--Bitmap(HASH:([Dim_item].[opco_num],
> [Dim_item].[item_ident]), DEFINE:([Bitmap1006]))
> | | |--Parallelism(Repartition
> Streams, PARTITION COLUMNS:([Dim_item].[opco_num],
[Dim_item].[item_ident]))
> | | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_item].[PK_Dim_item] AS [Dim_item]))
> | |--Parallelism(Repartition Streams,
> PARTITION COLUMNS:([fact_sales].[opco_num], [fact_sales].[item_ident]),
> WHERE:(PROBE([Bitmap1006])=TRUE))
> | |--Hash Match(Inner Join,
> HASH:([Dim_Vendor].[vendor_ident])=([fact_sales].[vendor_ident]),
> RESIDUAL:([fact_sales].[vendor_ident]=[Dim_Vendor].[vendor_ident]))
> | |--Parallelism(Broadcast)
> | | |--Nested Loops(Left
> Anti Semi Join, WHERE:([Dim_Vendor].[opco_vendor_num]=[Expr1005]))
> | |
> |--Parallelism(Gather Streams)
> | | |
> |--Filter(WHERE:((((((((((((((like([Dim_Vendor].[opco_vendor_name], 'All
> Round%', NULL) OR like([Dim_Vendor].[opco_vendor_name], 'Awrey%', NULL))
> OR like([Dim_Vendor].[opco_vendor_name], 'Copes%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Diversifood%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'General Mill%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Lonestar%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'McCain%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Norpac%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Otis%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Pillsbury%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Rochester%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Sara Lee%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Seabrook%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Simplot%', NULL)) OR
> like([Dim_Vendor].[opco_vendor_name], 'Trappe%', NULL)))
> | | |
> |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[Dim_Vendor].[PK_Dim_Vendor] AS
> [Dim_Vendor]), WHERE:([Dim_Vendor].[opco_vendor_num]<>173))
> | | |--Constant Scan
> | |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[fact_sales].[PK_fact_sales] AS
> [fact_sales]), WHERE:([fact_sales].[calendar_date]>='Nov 5 2002
> 12:00AM' AND [fact_sales].[calendar_date]<'Nov 19 2003 12:00AM'))
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS:([dim_inv_info_tbl].[opco_num], [dim_inv_info_tbl].[item_num]))
> |--Clustered Index
> Scan(OBJECT:([pfg_dm].[dbo].[dim_inv_info_tbl].[idx_dim_inv_info_tbl_01]
> AS [dim_inv_info_tbl]))
>|||Brian Moran wrote:
> 1. I would update stats to make sure that's not an issue...
> 2. CXPacket indicates a waittime assoicated with parrallel query plans. Try
> setting the MAXDOP to 1 as a test. The slow plan is probably parallel and
> the fast plan may be serial.
>
Thanks,
I guess I should have specified that MAXDOP is not an option, this
query is being run through a tool that does not allow direct
modification of the query. I will check the stats, thanks

Query not returning proper data (date related) in 2005 after upgrade from 2000...

I am sending out an SOS.

Here is the situation:

We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can’t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn’t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea’s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.

Thank you.

ScottIm very curious about this situation. Can you post the query that you are running?|||

It is very long but here it is...

select data.shipment,
po_line,
data.style,
p.prodcat1 as omnia_sbu,
Left(p.prodcat1, 3) as sbu_mstr,
isnull(CONVERT(CHAR(10), OrigCRD, 101),'') OrigCRD,
isnull(CONVERT(CHAR(10), CRD, 101),'') CRD,
isnull(OrigNDC, '') OrigNDC,
isnull(CONVERT(CHAR(10), NDC, 101),'') NDC,
isnull(OrigLogP, '') OrigLogP,
isnull(LogP,'') LogP,
isnull(po_mode, '') po_mode,
isnull(data.agent, '') agent,
isnull(rtrim(agent),'') + isnull(ship_mode,'') agent_key,
isnull(left(agent, 2),'') agent_mstr,
isnull(left(agent, 2),'') + isnull(ship_mode,'') agent_mstr_key,
isnull(data.factory_id, '') factory_id,
isnull(data.factory, '') factory,
isnull(rtrim(data.factory), '') + isnull(ship_mode,'') as fact_key,
isnull(data.broker, '') broker,
isnull(left(broker, 3), '') broker_mstr,
isnull(template, '') ship_cal,
isnull( ship_mode, '') ship_mode,
isnull(CONVERT(CHAR(10), brd_actual_date, 101),'') brd_actual_date,
isnull(CONVERT(CHAR(10), crd_actual_date, 101),'') crd_actual_date,
isnull(CONVERT(CHAR(10), cls_actual_date, 101),'') cls_actual_date,
isnull(CONVERT(CHAR(10), dep_actual_date, 101),'') dep_actual_date,
isnull(CONVERT(CHAR(10), arv_actual_date, 101),'') arv_actual_date,
isnull(CONVERT(CHAR(10), dlv_actual_date, 101),'') dlv_actual_date,
isnull(CONVERT(CHAR(10), rcv_actual_date, 101),'') rcv_actual_date,
isnull(CONVERT(CHAR(10), idc_actual_date, 101),'') idc_actual_date,
isnull(CONVERT(CHAR(10), bkd_actual_date, 101),'') bkd_actual_date,
isnull(CONVERT(CHAR(10), aci_actual_date, 101),'') aci_actual_date,
isnull(CONVERT(CHAR(10), dlt_actual_date, 101),'') dlt_actual_date,
isnull(CONVERT(CHAR(10), lot_actual_date, 101),'') lot_actual_date,
isnull(CONVERT(CHAR(10), dcr_actual_date, 101),'') dcr_actual_date,
isnull(CONVERT(CHAR(10), ccd_actual_date, 101),'') ccd_actual_date,
units ship_units
from
(select shipment.shipment, lot po_line, prbunhea.style, CASE ltrim(prbunhea.misc12)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc12) END OrigCRD,
CASE ltrim(prbunhea.misc4)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc4) END CRD,
ltrim(prbunhea.misc13) OrigNDC,
CASE ltrim(revdd)
WHEN '' THEN ''
ELSE revdd END NDC,
CASE ltrim(orig_shipcal)
WHEN '' THEN ''
ELSE ltrim(orig_shipcal) END OrigLogP,
CASE ltrim(prbunhea.defshipcal)
WHEN '' THEN ''
ELSE ltrim(prbunhea.defshipcal) END LogP,
CASE ltrim(prbunhea.misc14)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc14) END po_mode,
CASE ltrim(shipment.misc5)
WHEN '' THEN ''
ELSE ltrim(shipment.misc5) END ship_mode,
prbunhea.rdacode agent,
shipment.broker,
prbunhea.rfactory factory_id,
ship_to_1 factory,
sum(shipped.unitship) units
from prbunhea, shipped, shipment, shshipto where
prbunhea.lot = shipped.cut
and shipment.shipment = shipped.shipment
and shshipto.factory = prbunhea.rfactory
group by shipment.shipment, lot, prbunhea.style,prbunhea.misc12,
prbunhea.misc4, prbunhea.misc13, shipment.misc5, revdd,
orig_shipcal, prbunhea.DefShipCal, prbunhea.misc14,
prbunhea.rdacode, shipment.broker, prbunhea.rfactory, ship_to_1
) data,
(select CAST(tam.name AS CHAR(20)) AS name,
tam.id_ta,
tam.template,
brd_actual_date,
crd_actual_date,
cls_actual_date,
dep_actual_date,
arv_actual_date,
dlv_actual_date,
rcv_actual_date,
idc_actual_date,
bkd_actual_date,
aci_actual_date,
dlt_actual_date,
lot_actual_date,
dcr_actual_date,
ccd_actual_date
from
(select * from tamaster where calendarmodule = 'Shipment') as tam left outer join
(select id_ta,
max(brd_actual_date) as brd_actual_date,
max(crd_actual_date) as crd_actual_date,
max(cls_actual_date) as cls_actual_date,
max(dep_actual_date) as dep_actual_date,
max(arv_actual_date) as arv_actual_date,
max(dlv_actual_date) as dlv_actual_date,
max(rcv_actual_date) as rcv_actual_date,
max(idc_actual_date) as idc_actual_date,
max(bkd_actual_date) as bkd_actual_date,
max(aci_actual_date) as aci_actual_date,
max(dlt_actual_date) as dlt_actual_date,
max(lot_actual_date) as lot_actual_date,
max(dcr_actual_date) as dcr_actual_date,
max(ccd_actual_date) as ccd_actual_date
from
(
-- CRD start
select id_ta,
--name,
crd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as brd_actual_date ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CRD - CARGO READY DATE'
-- CRD end
union all
-- BRD start
select id_ta,
--name,
null as crd_actual_date,
brd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BRD - BOOKING REQUEST DATE'
-- BRD end
union all
-- CLS start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
cls_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CLS - SHIP CLOSE'
-- CLS end
union all
-- DEP start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
dep_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DEP - CONFIRMED ON BOARD'
-- DEP end
union all
-- ARV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
arv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ARV - ACTUAL DATE OF ARRIVAL'
-- ARV end
union all
-- DLV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
dlv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLV - CARGO RECEIVED AT DC (POD)'
-- DLV end
union all
-- RCV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
rcv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'RCV - RECEIVE DC'
-- RCV end
union all
-- IDC start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
idc_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'IDC - IN DISTRIBUTION CENTER'
-- IDC end
union all
-- BKD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
bkd_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BKD - BOOKING APPROVED'
-- BKD end
union all
-- ACI start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
aci_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ACI - DATE ENTRY IS FILED WITH CUSTOMS'
-- ACI end
union all
-- DLT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
dlt_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLT - ARRIVAL AT ATL RAIL / CY'
-- DLT end
union all
-- LOT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
lot_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'LOT - LOADED ON TRUCK OR TRAIN'
-- LOT end
union all
-- DCR start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end
union all
-- CCD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
ccd_actual_date =
CASE year(act_finish)
WHEN 1900 THEN null
WHEN 1899 THEN null
ELSE act_finish
END
from tacalendar where name = 'CCD - CUSTOMS CLEARED'
-- CCD end
) as cal group by id_ta ) as cal_temp
on tam.id_ta = cal_temp.id_ta) as cal, tmp_wcc_out_products p
where cast(data.shipment as char(20)) = cast(cal.name as char(20))
and data.style = p.prodcode
order by data.shipment, data.po_line

|||

We solved the issue by making the following change. I am wondering if SS 2005 handles null values differently.

Before:

union all
-- DCR start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end

After:

union all
-- DCR start
select id_ta,
--name,
convert(datetime,'12/30/1899',101) crd_actual_date,
convert(datetime,'12/30/1899',101) brd_actual_date,
convert(datetime,'12/30/1899',101) cls_actual_date,
convert(datetime,'12/30/1899',101) dep_actual_date,
convert(datetime,'12/30/1899',101) arv_actual_date,
convert(datetime,'12/30/1899',101) dlv_actual_date,
convert(datetime,'12/30/1899',101) rcv_actual_date,
convert(datetime,'12/30/1899',101) idc_actual_date,
convert(datetime,'12/30/1899',101) bkd_actual_date,
convert(datetime,'12/30/1899',101) aci_actual_date,
convert(datetime,'12/30/1899',101) dlt_actual_date,
convert(datetime,'12/30/1899',101) lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN convert(datetime,'12/30/1899',101) WHEN 1900 THEN convert(datetime,'12/30/1899',101)
ELSE act_finish
END,
convert(datetime,'12/30/1899',101) ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end|||

I can't see where exactly the problem is by looking at the work workaround that you implemented (at least with a first glance). Is it possible to reduce the query to a small statement that shows that NULLs are treated differently between the two versions of SQL Server ? That will help us investigate the problem.

Thanks a lot,

Leo

query not returning data!

Hi! I have a sql query in stored procedure:
SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname

Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.

You can us ISNULL or COALESCE to take care the NULL value in your Salutation column before you combine it with firstname and lastname. For example:

SELECT ISNULL(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

Or

SELECT COALESCE(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

|||

To be precise, the query is returning a value. The value is NULL, which in SQL terms is unknown.

So, when you add (concatenate) a known value to an unknown one, what should the result be?

Unknown!

It's analogous to saying, I'm going to add $5 to the money in my pocket. How much money is in my pocket?

You don't know, because you don't know how much money is already in my pocket.

(You don't even know if there is $5 in my pocket - there might be a hole in it. )

You have to be very careful to account for NULL values in the database.

Nothing is less than, greater than, or equal to NULL. Even NULL!

|||

Thank you! It works great now. What does COALESCE mean?

Cheers!

|||

Thank you for the explanation! Make sense.

Query not returning all results

OK, interesting problem I haven't encountered before.
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo = '123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
--
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBANever mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!

Query not returning all results

OK, interesting problem I haven't encountered before.
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo =
'123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct
.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBANever mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas
"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!

Query not returning all results

OK, interesting problem I haven't encountered before.
If I do the following query:
Select * from TableA where SequenceNo = 555 and Column = 6 and ClientNo =
'123456'
I get 1 row (which is correct).
If I run the same query without the ClientNo specification, I get 4 results,
but Clientno 123456 isn't in the results.
If I run the same query, without the ClientNo specification, without the *
(i.e. manually listing each column), then I get 20 results, which is correct.
Why is this happening? Anyone know how to resolve this?
Manually listing each column is so not an option. Why isn't "Select * from
TableA where SequenceNo = 555 and Column = 6" to returning all 20 valid
results?
FYI: SequenceNo and Column are datatypes int, Clientno is varchar.
I've tried with and without single quotes, and also adding in "where
sequenceno is not null" kind of logic, too.
Argh!!!!!
Financial Systems Analyst
CCNA, MCSE, MCSA, MCDBA
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!
|||Sounds like you've got data corruption or, at least, integrity issues.
Probably because of the use of SELECT * and such.
If you are explicit about everything you do, then there will never be a
question concerning what it is you want.
Leave everything to defaults and you will get ambiguity, which is that start
of corruption.
Sorry to disappoint you, but you need to fix your data or start being more
explicit.
You could try a TOP clause to find the row that is causing you grief and,
then, manually fix the data.
Sincerely,
Anthony Thomas

"Ysandre" <Ysandre@.discussions.microsoft.com> wrote in message
news:773DC2E5-81C5-4AEA-A563-020065BE8943@.microsoft.com...
Never mind!
forgot to check to Message tab (doh!), there was a Numeric Value Out Of
Range error.
So, anyone know how to resolve THAT little issue?!?
Thanks!

Query no returning desired resluts, HELP

I have 3tables/views I need to pull into one query for a results set for a Gridview table.

2 views, 1 table.

1st view, vw_ORG has fields, ORGID, ORGNAME

2nd view, vw_Staff, has fields, ORGID, STAFFID, FIRSTNAME, LASTNAME, MIDDLENAME, PREFIX, DEGREE

1st table : tbl_Data, has fields, ORGID, LASTMODIFIEDBY (which is a fk, pk in vw_Staff), LASTMODIFIED (date time ).

Basically my results need to have the ORGID, ORGNAME, LASTMODIFIED DATE and LASTMODIFIEDBY.

Would look like this...

ORGID ORGNAME LASTMODIFIED DATE LASTMODIFIEDBY
1 Science 10/10/2006 9:42 a.m. John P. Smith
22 Mathmatic 10/01/2006 9:15 p.m. Leslie Stahl
95 Football 5/01/2006 12:15 a.m. Terrell Owens

I have gotten results that displays the lastmodified date use MAX but can't figure out how to pull in the names from vw_Staff

Here is that part if you want to use as a starting base.

Select DISTINCT b.orgid, b.OrgName,
(SELECT Max(d.lastmodified)FROM tbl_Data d WHERE d.orgid = b.orgid) AS modifiedDate
From vw_ORGS b
ORDER BY ORGID

I've been banging my head against a wall for 2 days now and am desperatly needing some resolution before the voices in my head start telling me to harm myself j/k.No one can help with this... ?|||select o.ORGID
, o.ORGNAME
, d.LASTMODIFIED
, s.FIRSTNAME
from vw_ORG as o
inner
join tbl_Data as d
on d.ORGID = o.ORGID
inner
join vw_Staff as s
on s.STAFFID = d.LASTMODIFIEDBY|||To many results from your code. but the attmept is most appreciated.

Basically it has to be a MAX date from when the ORG was last modified. Only one row of data per ORG.|||so the tbl_Data table has multiple "last modified" rows per ORGID?select o.ORGID
, o.ORGNAME
, d.LASTMODIFIED
, s.FIRSTNAME
from vw_ORG as o
inner
join tbl_Data as d
on d.ORGID = o.ORGID
and d.LASTMODIFIED =
( select max(LASTMODIFIED)
from tbl_Data
where ORGID = o.ORGID )
inner
join vw_Staff as s
on s.STAFFID = d.LASTMODIFIEDBY|||Yes, that is corrrect.. I was able to get the quasi results needed by using SELECT DISTINCT?

But another issue is that I need to retireve all ORGS weather or not the have a last modified date or not. Is there a IS NULL function or something that can be used to with the select max(LASTMODIFIED) to do this?

Thanks.|||change INNER to LEFT OUTER in two places