Efficient Query

Hi, I need an expert help on this, i have this query, but it seems to
trigger the parallelism on execution, i want to get around that and have a
more efficient query, can some please help me with this query. thank you in
advance.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SELECT DISTINCT
ProductOrder.*,
Agency.AgencyName,
zStatus.Status,
zStatus.StatusID,
Asset.AssetTypeID
FROM
ProductOrder
INNER JOIN
[User] ON ProductOrder.UserGUID = [User].UserGUID
LEFT OUTER JOIN
GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
LEFT OUTER JOIN
[Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
LEFT OUTER JOIN
Agency ON [Group].AgencyGUID = Agency.AgencyGUID
INNER JOIN
ProductOrderItem on ProductOrder.ProductOrderGUID =
ProductOrderItem.ProductOrderGUID
INNER JOIN
zStatus on ProductOrderItem.Status = zStatus.StatusID
LEFT OUTER JOIN
Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
WHERE
zStatus.StatusOrder IN
(SELECT
MIN(zStatus.StatusOrder)
FROM
ProductOrderItem
INNER JOIN
zStatus ON ProductOrderItem.Status = zStatus.StatusID
WHERE
ProductOrderItem.ProductOrderGUID =
ProductOrder.ProductOrderGUID)
AND
ProductOrder.OrderTypeID = 4
AND
zStatus.StatusCompleted = 0
AND
ProductOrder.IsBasket = 0
AND
zStatus.StatusID <> 47
ORDER BY
DateCreated ASC
Just a quick note..
You can set the "Cost Threshold for Parallelism" option to a higher value to
essentially turn off parallel query execution.
You can set it from 0 - 32k. The default is 5.
You may want to read up on the affinity mask (if you are using multiple
processors) and the max degree of parallelism option which can affect the
Cost Threshold.
Rick
"Daniel" <danielk@.adstream.com.au> wrote in message
news:%23wVcefujEHA.2788@.tk2msftngp13.phx.gbl...
> Hi, I need an expert help on this, i have this query, but it seems to
> trigger the parallelism on execution, i want to get around that and have a
> more efficient query, can some please help me with this query. thank you
in
> advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID =
> ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status = zStatus.StatusID
> WHERE
> ProductOrderItem.ProductOrderGUID =
> ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
> ORDER BY
> DateCreated ASC
>
|||Daniel wrote:
> Hi, I need an expert help on this, i have this query, but it seems to
> trigger the parallelism on execution, i want to get around that and
> have a more efficient query, can some please help me with this query.
> thank you in advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID =
> ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status =
> zStatus.StatusID WHERE
> ProductOrderItem.ProductOrderGUID =
> ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
> ORDER BY
> DateCreated ASC
If you add a MAXDOP 1 to the query, it will only use one processor.
David G.
|||thanks for the reply. But as far as i know, setting the cost threshold would
have set from the server. I only want to make changes to this query.
I don't want to have the parallelism because, the result of the query is
quite small, probably less than 20 rows. that's why, I think, this
paralellism is quite expensive for this query. Do you think the query can be
made more efficient, or is it already as efficient as it can be..? thank
you.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:Ovg6youjEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Just a quick note..
> You can set the "Cost Threshold for Parallelism" option to a higher value
> to
> essentially turn off parallel query execution.
> You can set it from 0 - 32k. The default is 5.
>
> You may want to read up on the affinity mask (if you are using multiple
> processors) and the max degree of parallelism option which can affect the
> Cost Threshold.
>
> Rick
>
> "Daniel" <danielk@.adstream.com.au> wrote in message
> news:%23wVcefujEHA.2788@.tk2msftngp13.phx.gbl...
> in
>
|||it doesn't really help, it even make it worse... well, what i'm trying to
reduce is the subtree cost... right now, my subtree cost is 110...
that's way too high... that's because i have a distinct. if i take of the
distinct, it still cost me 31... i'm trying to get it down as low as 10...
anyone can help me with this..?
"David G." <david_nospam@.nospam.com> wrote in message
news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Daniel wrote:
> If you add a MAXDOP 1 to the query, it will only use one processor.
> --
> David G.
|||Daniel wrote:[vbcol=seagreen]
> it doesn't really help, it even make it worse... well, what i'm
> trying to reduce is the subtree cost... right now, my subtree cost
> is 110...
> that's way too high... that's because i have a distinct. if i take
> of the distinct, it still cost me 31... i'm trying to get it down as
> low as 10... anyone can help me with this..?
>
> "David G." <david_nospam@.nospam.com> wrote in message
> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
You said in your original post you want to get rid of the parallelism.
Are you saying that what you really want is the query to run more
efficiently? If so, try either reducing the number of tables (joins) in
the query. Also try removing the zStatus.StatusOrder IN subselect. It
looks like this subselect returns one value using the MIN(). if so, try
grabbing that value first and building the query using sp_executesql
since local variables used as bind variables in a stored procedure may
not be optimized well by SQL Server.
Also, what are the performance stats for the query? What amount of CPU
is used? How many reads? Are table scans being performed? If so, on what
tables? Are indexes in place to prevent the scan operations?
David G.
|||> You said in your original post you want to get rid of the parallelism.
> Are you saying that what you really want is the query to run more
> efficiently? If so, try either reducing the number of tables (joins) in
> the query. Also try removing the zStatus.StatusOrder IN subselect. It
> looks like this subselect returns one value using the MIN(). if so, try
> grabbing that value first and building the query using sp_executesql
> since local variables used as bind variables in a stored procedure may
> not be optimized well by SQL Server.
> Also, what are the performance stats for the query? What amount of CPU
> is used? How many reads? Are table scans being performed? If so, on what
> tables? Are indexes in place to prevent the scan operations?
how can i see all those things...? performance stats, cpu used, reads..
the estimate row counts is 35,700 and the subtreecost is 110.
i don't have any table scan as everything is using either clustered index
scan or index seek.
it looks quite efficient, but it's still expensive. is there any other way
to optimize the query..?
the process that is most expensive is the distinct, but i cannot live
without it...
and i can't remove the zstatusorder min(), as it is checking the the lowest
status of the productorderitem of each productorder
and i don't think i can reduce the tables coz i need all of them...
"David G." <david_nospam@.nospam.com> wrote in message
news:uQagcYxjEHA.1040@.TK2MSFTNGP10.phx.gbl...
> Daniel wrote:
> You said in your original post you want to get rid of the parallelism.
> Are you saying that what you really want is the query to run more
> efficiently? If so, try either reducing the number of tables (joins) in
> the query. Also try removing the zStatus.StatusOrder IN subselect. It
> looks like this subselect returns one value using the MIN(). if so, try
> grabbing that value first and building the query using sp_executesql
> since local variables used as bind variables in a stored procedure may
> not be optimized well by SQL Server.
> Also, what are the performance stats for the query? What amount of CPU
> is used? How many reads? Are table scans being performed? If so, on what
> tables? Are indexes in place to prevent the scan operations?
>
> --
> David G.
>
|||On Tue, 31 Aug 2004 09:19:56 +1000, "Daniel" <danielk@.adstream.com.au>
wrote:

>Hi, I need an expert help on this, i have this query, but it seems to
>trigger the parallelism on execution, i want to get around that and have a
>more efficient query, can some please help me with this query. thank you in
>advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
I'm no expert, but I read loads of times not to use * in the select on
production server. So thats the advice I'll offer here.
Name the ProductOrder columns you want (even if theres 50 of them).
HTH
al.

> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID =
>ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status = zStatus.StatusID
> WHERE
> ProductOrderItem.ProductOrderGUID =
>ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
>ORDER BY
> DateCreated ASC
>
|||On Tue, 31 Aug 2004 17:05:39 +1000, Daniel wrote:

>how can i see all those things...? performance stats, cpu used, reads..
>the estimate row counts is 35,700 and the subtreecost is 110.
>i don't have any table scan as everything is using either clustered index
>scan or index seek.
>it looks quite efficient, but it's still expensive. is there any other way
>to optimize the query..?
>the process that is most expensive is the distinct, but i cannot live
>without it...
>and i can't remove the zstatusorder min(), as it is checking the the lowest
>status of the productorderitem of each productorder
>and i don't think i can reduce the tables coz i need all of them...
Hi Daniel,
A clustered index IS the table, so if you get a clustered index scan, you
have a table scan. There might be room for improvement.
See if supplying a column list instead of SELECT * (as suggested by Harag)
helps. You might also try if it replacing StatusOrder IN (subselect) by
StatusOrder = (subselect) helps. The subselect will never return more than
one value, so they should be equivalent - maybe = instead of IN will give
the optimizer new ideas.
For further help, you'll have to provide more information: the design of
your tables (CREATE TABLE statements, including all constraints and all
indexes but excluding irrelevant columns), some sample data (as INSERT
statements, so we don't have to type them ourselves <g>) and expected
output based on the sample data. Plus a description of the business
problem.
See http://www.aspfaq.com/etiquette.asp?id=5006
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Daniel,
Don't focus too much on an individual 'expensive' part of the query
plan. In the end, the only thing that matters is the total elapsed time
of the query. Proper indexing and rewriting the query can dramatically
change the optimizer strategy (= query plan).
It looks like you can remove your IN by rewriting it as below. Of
course, you need to test if the results are still correct.
set quoted_identifier on
SELECT DISTINCT
ProductOrder.*,
Agency.AgencyName,
zStatus.Status,
zStatus.StatusID,
Asset.AssetTypeID
FROM ProductOrder
INNER JOIN ProductOrderItem ON ProductOrder.ProductOrderGUID =
ProductOrderItem.ProductOrderGUID
INNER JOIN zStatus ON ProductOrderItem.Status =
zStatus.StatusID
INNER JOIN "User" ON ProductOrder.UserGUID =
"User".UserGUID
LEFT JOIN GroupMapping ON "User".UserGUID =
GroupMapping.UserGUID
LEFT JOIN "Group" ON GroupMapping.GroupGUID =
"Group".GroupGUID
LEFT JOIN Agency ON "Group".AgencyGUID =
Agency.AgencyGUID
LEFT JOIN Asset ON Asset.AssetGUID =
ProductOrderItem.AssetGUID
WHERE zStatus.StatusOrder = (
SELECT MIN(zStatus.StatusOrder)
FROM ProductOrderItem
INNER JOIN zStatus ON ProductOrderItem.Status =
zStatus.StatusID
WHERE ProductOrderItem.ProductOrderGUID =
ProductOrder.ProductOrderGUID
)
AND ProductOrder.OrderTypeID = 4
AND zStatus.StatusCompleted = 0
AND ProductOrder.IsBasket = 0
AND zStatus.StatusID <> 47
ORDER BY DateCreated ASC
If you expect just one row for each combination of
(ProductOrder.*,zStatus.Status,Status.StatusID), then you can remove
DISTINCT by rewriting the LEFT OUTER JOINs as scalar subqueries.
For example, you can rewrite
SELECT DISTINCT ... , Asset.AssetTypeID
FROM ...
LEFT JOIN Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
as
SELECT ... , (
SELECT AssetTypeID
FROM Asset
WHERE Asset.AssetGUID = ProductOrderItem.AssetGUID
) AS AssetTypeID
FROM ...
Hope this helps,
Gert-Jan
Daniel wrote:[vbcol=seagreen]
> it doesn't really help, it even make it worse... well, what i'm trying to
> reduce is the subtree cost... right now, my subtree cost is 110...
> that's way too high... that's because i have a distinct. if i take of the
> distinct, it still cost me 31... i'm trying to get it down as low as 10...
> anyone can help me with this..?
> "David G." <david_nospam@.nospam.com> wrote in message
> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
(Please reply only to the newsgroup)