Query question

Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
--
Paul G
Software engineer.The CASE statement is there to include or exclude that condition in the
query; however, it is a lousy implementation. You are obviously using
dynamic sql inside of a stored procedure. Other than a convenient place to
put it, dyanmical sql inside a proc reduces the effectiveness of using
stored procedure.
The '%' before and after the passed in parameter are wildcard characters
that allow any string as a substitute. So, any string plus parameter plus
any string becomes the search condition. You are gauranteed to do a table
scan or clustered index scan as that criteria could never be supported by an
index.
As a better solution, try something more like this:
SELECT Col1, Col2, ..., Coln
FROM Tab1 JOIN Tab2
ON Tab1.Key1 = Tab2.Key1
AND Tab1.Key1 = Tab2.Key2
...
AND Tab1.Keyn = Tab2.Keyn
...
...
JOIN Tabn
ON ...
WHERE criterion1 AND criterion2 ... AND criterionN
AND (@.stringvar IS NULL
OR TabX.ColX LIKE (@.stringvar + '%')
)
This is executed directly. There is no need for a variable nor the use of
the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highly
selectable. The query execution plan can be reused.
Hope this helps.
Sincerely,
Anthony Thomas
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
--
Paul G
Software engineer.|||Hi thanks for the response. It did seem like there would be a better way
other than the dynamic sql. Someone had suggested to me from this newsgroup
to use it so I went that route. No official SQL training so just learning by
trail and error.
"AnthonyThomas" wrote:
> The CASE statement is there to include or exclude that condition in the
> query; however, it is a lousy implementation. You are obviously using
> dynamic sql inside of a stored procedure. Other than a convenient place to
> put it, dyanmical sql inside a proc reduces the effectiveness of using
> stored procedure.
> The '%' before and after the passed in parameter are wildcard characters
> that allow any string as a substitute. So, any string plus parameter plus
> any string becomes the search condition. You are gauranteed to do a table
> scan or clustered index scan as that criteria could never be supported by an
> index.
> As a better solution, try something more like this:
> SELECT Col1, Col2, ..., Coln
> FROM Tab1 JOIN Tab2
> ON Tab1.Key1 = Tab2.Key1
> AND Tab1.Key1 = Tab2.Key2
> ...
> AND Tab1.Keyn = Tab2.Keyn
> ...
> ...
> JOIN Tabn
> ON ...
> WHERE criterion1 AND criterion2 ... AND criterionN
> AND (@.stringvar IS NULL
> OR TabX.ColX LIKE (@.stringvar + '%')
> )
> This is executed directly. There is no need for a variable nor the use of
> the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highly
> selectable. The query execution plan can be reused.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
> Hi I have a large stored procedure that performs several queries
> conditionally on a database depending on values that are passed into the
> procedure. Anyhow I am using a string search and have set up
> SELECT @.stringvar='%' + @.stringvar + '%'
> then have
> case when @.stringvar IS NOT NULL then
> 'AND (table.field LIKE @.stringvar)'
> ELSE ''
> It works fine just could not remember why I needed to have the
> SELECT @.stringvar='%' + @.stringvar + '%' statement.
> thanks,
> --
> Paul G
> Software engineer.
>
>

Query question

Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
Paul G
Software engineer.The CASE statement is there to include or exclude that condition in the
query; however, it is a lousy implementation. You are obviously using
dynamic sql inside of a stored procedure. Other than a convenient place to
put it, dyanmical sql inside a proc reduces the effectiveness of using
stored procedure.
The '%' before and after the passed in parameter are wildcard characters
that allow any string as a substitute. So, any string plus parameter plus
any string becomes the search condition. You are gauranteed to do a table
scan or clustered index scan as that criteria could never be supported by an
index.
As a better solution, try something more like this:
SELECT Col1, Col2, ..., Coln
FROM Tab1 JOIN Tab2
ON Tab1.Key1 = Tab2.Key1
AND Tab1.Key1 = Tab2.Key2
..
AND Tab1.Keyn = Tab2.Keyn
..
..
JOIN Tabn
ON ...
WHERE criterion1 AND criterion2 ... AND criterionN
AND (@.stringvar IS NULL
OR TabX.ColX LIKE (@.stringvar + '%')
)
This is executed directly. There is no need for a variable nor the use of
the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highly
selectable. The query execution plan can be reused.
Hope this helps.
Sincerely,
Anthony Thomas
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
Paul G
Software engineer.|||Hi thanks for the response. It did seem like there would be a better way
other than the dynamic sql. Someone had suggested to me from this newsgroup
to use it so I went that route. No official SQL training so just learning b
y
trail and error.
"AnthonyThomas" wrote:

> The CASE statement is there to include or exclude that condition in the
> query; however, it is a lousy implementation. You are obviously using
> dynamic sql inside of a stored procedure. Other than a convenient place t
o
> put it, dyanmical sql inside a proc reduces the effectiveness of using
> stored procedure.
> The '%' before and after the passed in parameter are wildcard characters
> that allow any string as a substitute. So, any string plus parameter plus
> any string becomes the search condition. You are gauranteed to do a table
> scan or clustered index scan as that criteria could never be supported by
an
> index.
> As a better solution, try something more like this:
> SELECT Col1, Col2, ..., Coln
> FROM Tab1 JOIN Tab2
> ON Tab1.Key1 = Tab2.Key1
> AND Tab1.Key1 = Tab2.Key2
> ...
> AND Tab1.Keyn = Tab2.Keyn
> ...
> ...
> JOIN Tabn
> ON ...
> WHERE criterion1 AND criterion2 ... AND criterionN
> AND (@.stringvar IS NULL
> OR TabX.ColX LIKE (@.stringvar + '%')
> )
> This is executed directly. There is no need for a variable nor the use of
> the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highl
y
> selectable. The query execution plan can be reused.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
> Hi I have a large stored procedure that performs several queries
> conditionally on a database depending on values that are passed into the
> procedure. Anyhow I am using a string search and have set up
> SELECT @.stringvar='%' + @.stringvar + '%'
> then have
> case when @.stringvar IS NOT NULL then
> 'AND (table.field LIKE @.stringvar)'
> ELSE ''
> It works fine just could not remember why I needed to have the
> SELECT @.stringvar='%' + @.stringvar + '%' statement.
> thanks,
> --
> Paul G
> Software engineer.
>
>

Query Problem in Access

hi friends i need help in this sql query

i have table like,

id fid
__ _____
autonumber text

and i am storing values like

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

now to find values i am using query

sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%

now from this group some one give me the answer of this query

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line

now this query is running perfectly in other database except msaccess
2000. can anyone solve this problem. this query is not giving any
answer. it checks all those records which are singleton but not middle
of line records. and it seems to be problem in access only not in
mysql. it is working perfectly in mysql but not in access and as access
is my database in application i have to use access and i am really
irritate when i find in help that either i can use ' * ' or ' % ' in
expression on any one side like '%,1' or '%1,' but not like middle of
line that i am using '%,1,%'

here is example of my problem

sample table:=

id fid
___________________________________
1 1,2,3,4,5

2 11,12,13,14,15

query like

select *
from test
where fid = '1' -- singleton
or fid like '1,%' -- beginning of line
or fid like '%,1,%' -- middle of line
or fid like '%,1' -- end of line

will result id=1 perfectly but when i search

select *
from test
where fid = '2' -- singleton
or fid like '2,%' -- beginning of line
or fid like '%,2,%' -- middle of line
or fid like '%,2' -- end of line

it will not give me no output. plz help me i dont know what is the
problem if anyone can solve this i will be really thankful.On 19 Oct 2006 00:57:20 -0700, hardik wrote:

Quote:

Originally Posted by

>hi friends i need help in this sql query
>
>i have table like,
>
>id fid
>__ _____
>autonumber text
>
>and i am storing values like
>
>id fid
>___________________________________
>1 1,2,3,4,5
>
>2 11,12,13,14,15
>
>now to find values i am using query
>
>sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')

(snip)

Hi hardik,

You should really change this design. The fid column violates the
principle of first normal form. That makes many queries needlessly
complex and slow. A proper design would split the comma-delimited list
in fid into seperate rows:

id fid
1 1
1 2
1 3
1 1
1 1
2 11
2 12
2 13
2 14
2 15

Then, you'd just use SELECT * FROM better_table WHERE fid = '1'

(snip)

Quote:

Originally Posted by

>now from this group some one give me the answer of this query
>
>select *
>from test
>where fid = '1' -- singleton
>or fid like '1,%' -- beginning of line
>or fid like '%,1,%' -- middle of line
>or fid like '%,1' -- end of line

Works, but there is a shorter kludge possible:
SELECT * FROM test WHERE ',' + fld + ',' LIKE '%,1,%'

Quote:

Originally Posted by

>now this query is running perfectly in other database except msaccess
>2000.

Access doesn't use the ANSI standard wildcards for LIKE searches. In
Access, you have t replace the '%' character with '*'.

But the best solution is: fix the design!!

--
Hugo Kornelis, SQL Server MVP

query performance

The folling query works but is it the most efficient way to write it from a
performance aspect?
The parameter can be set with the values (0,1 and %) these can be changed if
needed for the final query version.
The Detail.Automated database field is a datatype of bit
Should I be writing this to avoid the LIKE keyword?
-- only used for testing
DECLARE @.Auto AS char (1)
SET @.Auto = '%'
-- end test
SELECT DISTINCT Status.ID,
Info.ID,
Info.Text,
Detail.Automated,
Status.Status
FROM Status INNER JOIN Info
ON Status.ID = Info.ID
INNER JOIN Detail
ON Info.ID = Detail.ID
WHERE (Status.ID = 4)
AND (Detail.Automated LIKE @.AutoJim Abel (JimAbel@.discussions.microsoft.com) writes:

> The folling query works but is it the most efficient way to write it
> from a performance aspect? The parameter can be set with the values (0,1
> and %) these can be changed if needed for the final query version.
> The Detail.Automated database field is a datatype of bit
> Should I be writing this to avoid the LIKE keyword?
Using LIKE with bit looks quite strange. I would rather write:
AND (Detail.Automated = @.Auto OR @.Auto IS NULL)
And of course @.Auto would be declared as bit.
Whether the query you have is the best from the point of view of
performance is impossible to say, as this requires knowledge about
the tables, indexes, and the amount and distribution of the data in
the tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Query Parameter Dialog Box

When I am looking at a dataset and use the "!" execute button I get the pop
up dialog box for parameters. How must I enter my values if i want it to
test multi-value selection?
I've tried
ABC, DEF
'ABC,DEF'
'ABC','DEF'
"ABC","DEF"
ABC;DEF
'ABC';'DEF'
I can't get it to work. Can you only use single values in this view?how do you have the parameter specified? you must use in(@.param) when using
expecting multivalues
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:9E5DF01E-77AA-4D98-8C94-CFEEE2A4E48D@.microsoft.com...
> When I am looking at a dataset and use the "!" execute button I get the
> pop
> up dialog box for parameters. How must I enter my values if i want it to
> test multi-value selection?
> I've tried
> ABC, DEF
> 'ABC,DEF'
> 'ABC','DEF'
> "ABC","DEF"
> ABC;DEF
> 'ABC';'DEF'
> I can't get it to work. Can you only use single values in this view?

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)
bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.
|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Query optimization - joining a view and a Table

I am having the following situation - there is a view that aggregates and computes some values and a table that I need the details from so I join them filtering on the primary key of the table. The execution plan shows that the view is executed without any filtering so it returns 140 000 rows which are later filtered by the join operation a hash table match. This hash table match takes 47% of the query cost. I tried selecting the same view but directly giving a where clause without the join – it gave a completely different execution plan. Using the second method is in at least 4 folds faster and is going only through Index Seeks and nested loops.

So I tried modifying the query with third version. It gave almost the same execution plan as the version 1 with the join operation.

It seams that by giving the where clause directly the execution plan chosen by the query optimizer is completely different – it filters the view and the results from it and returns it at the same time, in contrast to the first version where the view is executed and return and later filtered. Is it possible to change the query some how so that it filters the view before been joined to the table.

Any suggestions will be appreciated greatly

Stoil Pankov

"vHCItemLimitUsed" - this is the view

"tHCContractInsured" - this is the table

"ixHCContractInsuredID" - is the primary key of the table

Here is a simple representation of the effect:

Version 1:

select *
from dbo.vHCItemLimitUsed
inner join tHCContractInsured on
vHCItemLimitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID
where tHCContractInsured.ixHCContractInsuredID in (9012,9013,9014,9015)

Version 2:

select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)

Version 3:

select *

from dbo.vHCItemLimitUsed

where ixHCContractInsuredID in

(select ixHCContractInsuredID

from tHCContractInsured

where ixHCContractInsuredID in (9012,9013,9014,9015))

Are we talking milliseconds, seconds, minutes, or hours? If seconds, then it just might be one of those case where it is too costly to do the whole optimization process and it is just faster to execute the query. What version/edition of SQL Server also?

Another version that might work for you is:

select *
from (select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)) as limitUsed
join tHCContractInsured on
limitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID

If we are talking about minutes then it might be a bug. Can you post some more information:

The plans of the queries
The structure of the view
The structure of the tables
The amount of data

Query Operating System

Is it possible to query the operating system for disk space, memory size,
and other values. Are these values kept in a system database?
Thanks,
nivek
Hi Nivek,
Yes - you can definitely use Transact-SQL to query these values. For
example, to query what drive letters are on your machine, and determine how
much space is available you can run this:
EXEC master..xp_fixeddrives
To figure out memory available and other performance counters, you can query
the master.dbo.sysperfinfo system table.
Best Regards,
Joseph Sack
www.JoeSack.com
Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
"nivek" wrote:

> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>
|||You can also use xp_regread to query windows registry for a broader range of
values.
"Joseph Sack" <JosephSack@.discussions.microsoft.com> wrote in message
news:57A80ACA-ECE5-4D3E-879C-125B1057D773@.microsoft.com...[vbcol=seagreen]
> Hi Nivek,
> Yes - you can definitely use Transact-SQL to query these values. For
> example, to query what drive letters are on your machine, and determine
> how
> much space is available you can run this:
> EXEC master..xp_fixeddrives
> To figure out memory available and other performance counters, you can
> query
> the master.dbo.sysperfinfo system table.
> Best Regards,
> Joseph Sack
> www.JoeSack.com
> Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
> "nivek" wrote:
|||Am using this SP to get disk space of drives on the server... It might be
helpful to you also...
I have created a job which calls this SP in the following manner,
exec sp_diskalert 'my email addr', 100000
Where 100000 is the limit in MB's. You can set it to any size. This limit
gives me the list of drives which are below 100 gig.
SP script is pasted below.
HTH
GYK
**************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure sp_diskalert
@.RCPT VARCHAR(500),
@.LIMIT INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
/* GENERATE THE MESSAGE */
IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@.RCPT) > 0 --CHECK THERE IS SOME
DATA AND A RECIPIENT
BEGIN
DECLARE @.MSG VARCHAR(400),
@.DLETTER VARCHAR(5),
@.DSPACE INT
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @.LIMIT
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) --PUT THE VARS
INTO A MSG
+ 'MB' + CHAR(13) + CHAR(10)
WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @.LIMIT AND DRVLETTER >
@.DLETTER) > 0
BEGIN--LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1
WHERE DRVSPACE < @.LIMIT
AND DRVLETTER > @.DLETTER
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.MSG + @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) + 'MB'
+ CHAR(13) + CHAR(10)
END
/* SEND THE MESSAGE */
IF CHARINDEX('@.',@.RCPT) > 0 --THERE IS AN @. SYMBOL IN THE RECIPIENT - SEND
EMAIL
BEGIN
DECLARE @.EMAIL VARCHAR(600)
SET @.EMAIL = 'EXEC master.dbo.xp_sendmail
@.recipients = ''' + @.RCPT + ''',
@.message = ''' + @.MSG + ''',
@.subject = ''!! LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' !!'''
EXEC (@.EMAIL)
END
ELSE IF CHARINDEX('@.',@.RCPT) = 0 --THERE IS NO @. SYMBOL IN THE RECIPIENT -
NET SEND
BEGIN
--DETERMINE IF XP_CMDSHELL EXISTS
DECLARE @.FLAG BIT
SET @.FLAG = 1
IF NOT EXISTS(SELECT name FROM master..sysobjects WHERE name =
'XP_CMDSHELL')
SET @.FLAG = 0
--IF NOT RECREATE IT
IF @.FLAG = 0
BEGIN
EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
PRINT 'ADDING XP_CMDSHELL'
END
--NET SEND MSG
DECLARE @.NETSEND VARCHAR(600)
SET @.MSG = 'ALERT - LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' : ' + @.MSG
SET @.NETSEND = 'xp_cmdshell ''net send "' + RTRIM(@.RCPT) + '" '
+ LEFT(RTRIM(REPLACE(@.MSG,CHAR(13) + CHAR(10),', ')),LEN(@.MSG)-2) + ''''
EXEC (@.NETSEND)
--DROP XP_CMDSHELL IF IT DIDN'T EXIST
IF @.FLAG = 0
BEGIN
EXEC sp_dropextendedproc 'xp_cmdshell'
PRINT 'DROPPING XP_CMDSHELL'
END
END
END
/* CLEANUP */
DROP TABLE #T1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
********************************************
"nivek" wrote:

> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>

Query Operating System

Is it possible to query the operating system for disk space, memory size,
and other values. Are these values kept in a system database?
Thanks,
nivekHi Nivek,
Yes - you can definitely use Transact-SQL to query these values. For
example, to query what drive letters are on your machine, and determine how
much space is available you can run this:
EXEC master..xp_fixeddrives
To figure out memory available and other performance counters, you can query
the master.dbo.sysperfinfo system table.
Best Regards,
Joseph Sack
www.JoeSack.com
Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
"nivek" wrote:
> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>|||You can also use xp_regread to query windows registry for a broader range of
values.
"Joseph Sack" <JosephSack@.discussions.microsoft.com> wrote in message
news:57A80ACA-ECE5-4D3E-879C-125B1057D773@.microsoft.com...
> Hi Nivek,
> Yes - you can definitely use Transact-SQL to query these values. For
> example, to query what drive letters are on your machine, and determine
> how
> much space is available you can run this:
> EXEC master..xp_fixeddrives
> To figure out memory available and other performance counters, you can
> query
> the master.dbo.sysperfinfo system table.
> Best Regards,
> Joseph Sack
> www.JoeSack.com
> Author of "SQL Server 2000 Fast Answers for DBAs and Developers"
> "nivek" wrote:
>> Is it possible to query the operating system for disk space, memory size,
>> and other values. Are these values kept in a system database?
>> Thanks,
>> nivek
>>|||Am using this SP to get disk space of drives on the server... It might be
helpful to you also...
I have created a job which calls this SP in the following manner,
exec sp_diskalert 'my email addr', 100000
Where 100000 is the limit in MB's. You can set it to any size. This limit
gives me the list of drives which are below 100 gig.
SP script is pasted below.
HTH
GYK
**************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure sp_diskalert
@.RCPT VARCHAR(500),
@.LIMIT INT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)
INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
/* GENERATE THE MESSAGE */
IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@.RCPT) > 0 --CHECK THERE IS SOME
DATA AND A RECIPIENT
BEGIN
DECLARE @.MSG VARCHAR(400),
@.DLETTER VARCHAR(5),
@.DSPACE INT
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @.LIMIT
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) --PUT THE VARS
INTO A MSG
+ 'MB' + CHAR(13) + CHAR(10)
WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @.LIMIT AND DRVLETTER >
@.DLETTER) > 0
BEGIN --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE
SET @.DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1
WHERE DRVSPACE < @.LIMIT
AND DRVLETTER > @.DLETTER
ORDER BY DRVLETTER ASC)
SET @.DSPACE = (SELECT DRVSPACE FROM #T1
WHERE DRVLETTER = @.DLETTER)
SET @.MSG = @.MSG + @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) + 'MB'
+ CHAR(13) + CHAR(10)
END
/* SEND THE MESSAGE */
IF CHARINDEX('@.',@.RCPT) > 0 --THERE IS AN @. SYMBOL IN THE RECIPIENT - SEND
EMAIL
BEGIN
DECLARE @.EMAIL VARCHAR(600)
SET @.EMAIL = 'EXEC master.dbo.xp_sendmail
@.recipients = ''' + @.RCPT + ''',
@.message = ''' + @.MSG + ''',
@.subject = ''!! LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' !!'''
EXEC (@.EMAIL)
END
ELSE IF CHARINDEX('@.',@.RCPT) = 0 --THERE IS NO @. SYMBOL IN THE RECIPIENT -
NET SEND
BEGIN
--DETERMINE IF XP_CMDSHELL EXISTS
DECLARE @.FLAG BIT
SET @.FLAG = 1
IF NOT EXISTS(SELECT name FROM master..sysobjects WHERE name ='XP_CMDSHELL')
SET @.FLAG = 0
--IF NOT RECREATE IT
IF @.FLAG = 0
BEGIN
EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
PRINT 'ADDING XP_CMDSHELL'
END
--NET SEND MSG
DECLARE @.NETSEND VARCHAR(600)
SET @.MSG = 'ALERT - LOW FREE DISK SPACE ON ' + @.@.SERVERNAME + ' : ' + @.MSG
SET @.NETSEND = 'xp_cmdshell ''net send "' + RTRIM(@.RCPT) + '" '
+ LEFT(RTRIM(REPLACE(@.MSG,CHAR(13) + CHAR(10),', ')),LEN(@.MSG)-2) + ''''
EXEC (@.NETSEND)
--DROP XP_CMDSHELL IF IT DIDN'T EXIST
IF @.FLAG = 0
BEGIN
EXEC sp_dropextendedproc 'xp_cmdshell'
PRINT 'DROPPING XP_CMDSHELL'
END
END
END
/* CLEANUP */
DROP TABLE #T1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
********************************************
"nivek" wrote:
> Is it possible to query the operating system for disk space, memory size,
> and other values. Are these values kept in a system database?
> Thanks,
> nivek
>
>

Query of counts

I have a table with three fields: AcctNo INT, Code CHAR(1), Amount MONEY

The Code has three values: 'A', 'B', or 'C'.

Each AcctNo has 1, 2, or all 3 of the Codes assigned to it (ie. AcctNo is not unique)

I need to know how many AcctNos have one value assigned to the Code field, how many have two, and how many have three and I need to know the sum of the Amount for each group.

Can this be done in one statement or do I need three statements?

Fred

The query below returns the results as requested. Is this what you're looking for?

Chris

DECLARE @.Values TABLE (AcctNo INT, Code CHAR(1), Amount MONEY)

INSERT INTO @.Values(AcctNo, Code, Amount)

SELECT 1, 'A', 1.00 UNION

SELECT 1, 'B', 2.50 UNION

SELECT 2, 'C', 1.25 UNION

SELECT 3, 'C', 1.43 UNION

SELECT 3, 'A', 1.96 UNION

SELECT 3, 'B', 2.00 UNION

SELECT 4, 'C', 1.43 UNION

SELECT 4, 'A', 1.96 UNION

SELECT 4, 'B', 2.10 UNION

SELECT 5, 'B', 0.92 UNION

SELECT 5, 'A', 1.24 UNION

SELECT 6, 'C', 0.02 UNION

SELECT 7, 'B', 0.11

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

|||

Yes, that is what I wanted first.

But I need to add another level of complexity. There are duplicates of the AccountNo and Code, only the Amount is different. I need to count duplicates as one. (or anything more than one; there were some with three times and four)

Thanks,

Fred

|||

All you should need is an extra DISTINCT, see below.

Chris

SELECT SUM([AccountNoCount]), SUM(TotalAmount), [CodeCount]

FROM (SELECT COUNT(DISTINCT AcctNo) AS [AccountNoCount], SUM(Amount) AS TotalAmount, COUNT(DISTINCT Code) AS [CodeCount]

FROM @.Values

GROUP BY AcctNo) t

GROUP BY [CodeCount]

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