Query Performance (tearing what's left of my hair out)

I really am on my last legs with this. I am trying to improve performance on
the following query. The query runs in 2hrs. When I remove the first two
references to a13.a13.Parent_County_desc and a13.County_desc the query
returns the same amount of rows in 1 minute!!! The only index on the
POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan shows
a
distinct sort using 100% cpu when you include this select criteria and a nic
e
hash aggregate when we don't. The volume of data is quite large and we have
to use this table instead of a lookup due to a known bug. What can I do to
improve performance on this query. Please help.
select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id)
Pr_cover_id,
a11.Pr_cover_desc Pr_cover_desc,
coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
Parent_County_id,
a13.Parent_County_desc Parent_County_desc,
coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
a13.County_desc County_desc,
coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
a13.Area_Desc Area_Desc,
coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
pa3.Po_rd_rating_code) Po_rd_rating_code,
coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
a14.week_desc week_desc,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2,
pa3.WJXBFS1 WJXBFS3
from marc_ZZT5J02027GMD000 pa1(nolock)
full outer join marc_ZZT5J02027GMD001 pa2(nolock)
on (pa1.Area_Id = pa2.Area_Id and
pa1.County_id = pa2.County_id and
pa1.Parent_County_id = pa2.Parent_County_id and
pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
pa1.Pr_cover_id = pa2.Pr_cover_id and
pa1.Ra_gender_id = pa2.Ra_gender_id and
pa1.Week_id = pa2.Week_id)
full outer join marc_ZZT5J02027GMD002 pa3(nolock)
on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
coalesce(pa1.Parent_County_id, pa2.Parent_County_id) = pa3.Parent_County_id
and
coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
pa3.Po_rd_rating_code and
coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
join PRt_lu_Cover a11
on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
a11.Pr_cover_id)
join RAv_lu_Gender a12
on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
a12.Ra_gender_id)
join POt_lu_rd_post_code a13
on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
join TIt_lu_day a14
on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)marcmc
Don't you really need 'WHERE' condition , am I right?
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:122E9FF3-5A9A-4729-B652-EA1B6956D751@.microsoft.com...
> I really am on my last legs with this. I am trying to improve performance
on
> the following query. The query runs in 2hrs. When I remove the first two
> references to a13.a13.Parent_County_desc and a13.County_desc the query
> returns the same amount of rows in 1 minute!!! The only index on the
> POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
> PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan show
s a
> distinct sort using 100% cpu when you include this select criteria and a
nice
> hash aggregate when we don't. The volume of data is quite large and we
have
> to use this table instead of a lookup due to a known bug. What can I do to
> improve performance on this query. Please help.
>
> select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
> pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
> Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id,
pa3.Pr_cover_id)
> Pr_cover_id,
> a11.Pr_cover_desc Pr_cover_desc,
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
> Parent_County_id,
> a13.Parent_County_desc Parent_County_desc,
> coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
> a13.County_desc County_desc,
> coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
> a13.Area_Desc Area_Desc,
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
> pa3.Po_rd_rating_code) Po_rd_rating_code,
> coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
> a14.week_desc week_desc,
> pa1.WJXBFS1 WJXBFS1,
> pa2.WJXBFS1 WJXBFS2,
> pa3.WJXBFS1 WJXBFS3
> from marc_ZZT5J02027GMD000 pa1(nolock)
> full outer join marc_ZZT5J02027GMD001 pa2(nolock)
> on (pa1.Area_Id = pa2.Area_Id and
> pa1.County_id = pa2.County_id and
> pa1.Parent_County_id = pa2.Parent_County_id and
> pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
> pa1.Pr_cover_id = pa2.Pr_cover_id and
> pa1.Ra_gender_id = pa2.Ra_gender_id and
> pa1.Week_id = pa2.Week_id)
> full outer join marc_ZZT5J02027GMD002 pa3(nolock)
> on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
> coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id) =
pa3.Parent_County_id
> and
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
> pa3.Po_rd_rating_code and
> coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
> coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
> coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
> join PRt_lu_Cover a11
> on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
> a11.Pr_cover_id)
> join RAv_lu_Gender a12
> on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
> a12.Ra_gender_id)
> join POt_lu_rd_post_code a13
> on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
> join TIt_lu_day a14
> on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)
>
>|||Honestly I would say yes very much so but this query was generated by a
reporting application so unfortunately I am stuck with it!
"Uri Dimant" wrote:

> marcmc
> Don't you really need 'WHERE' condition , am I right?
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:122E9FF3-5A9A-4729-B652-EA1B6956D751@.microsoft.com...
> on
> nice
> have
> pa3.Pr_cover_id)
> pa3.Parent_County_id
>
>|||ps: the problem is massive amounts of data where the county descriptions are
involved and the distinct sort is grouping them.
"marcmc" wrote:

> I really am on my last legs with this. I am trying to improve performance
on
> the following query. The query runs in 2hrs. When I remove the first two
> references to a13.a13.Parent_County_desc and a13.County_desc the query
> returns the same amount of rows in 1 minute!!! The only index on the
> POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
> PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan show
s a
> distinct sort using 100% cpu when you include this select criteria and a n
ice
> hash aggregate when we don't. The volume of data is quite large and we hav
e
> to use this table instead of a lookup due to a known bug. What can I do to
> improve performance on this query. Please help.
>
> select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
> pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
> Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id
)
> Pr_cover_id,
> a11.Pr_cover_desc Pr_cover_desc,
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
> Parent_County_id,
> a13.Parent_County_desc Parent_County_desc,
> coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
> a13.County_desc County_desc,
> coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
> a13.Area_Desc Area_Desc,
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
> pa3.Po_rd_rating_code) Po_rd_rating_code,
> coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
> a14.week_desc week_desc,
> pa1.WJXBFS1 WJXBFS1,
> pa2.WJXBFS1 WJXBFS2,
> pa3.WJXBFS1 WJXBFS3
> from marc_ZZT5J02027GMD000 pa1(nolock)
> full outer join marc_ZZT5J02027GMD001 pa2(nolock)
> on (pa1.Area_Id = pa2.Area_Id and
> pa1.County_id = pa2.County_id and
> pa1.Parent_County_id = pa2.Parent_County_id and
> pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
> pa1.Pr_cover_id = pa2.Pr_cover_id and
> pa1.Ra_gender_id = pa2.Ra_gender_id and
> pa1.Week_id = pa2.Week_id)
> full outer join marc_ZZT5J02027GMD002 pa3(nolock)
> on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
> coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id) = pa3.Parent_County_
id
> and
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
> pa3.Po_rd_rating_code and
> coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
> coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
> coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
> join PRt_lu_Cover a11
> on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
> a11.Pr_cover_id)
> join RAv_lu_Gender a12
> on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
> a12.Ra_gender_id)
> join POt_lu_rd_post_code a13
> on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
> join TIt_lu_day a14
> on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)
>
>|||When you say you must use this table instead of a lookup, can you elaborate?
Is it possible that you can use this without DISTINCT initially and
apply DISTINCT
elsewhere in your process where it will not require as much work? Also,
what is
the known bug you mention? Maybe someone knows it and has a workaround.
Steve Kass
Drew University
marcmc wrote:

>I really am on my last legs with this. I am trying to improve performance o
n
>the following query. The query runs in 2hrs. When I remove the first two
>references to a13.a13.Parent_County_desc and a13.County_desc the query
>returns the same amount of rows in 1 minute!!! The only index on the
>POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
>PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan shows
a
>distinct sort using 100% cpu when you include this select criteria and a ni
ce
>hash aggregate when we don't. The volume of data is quite large and we have
>to use this table instead of a lookup due to a known bug. What can I do to
>improve performance on this query. Please help.
>
>select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
>pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
>Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id)
>Pr_cover_id,
>a11.Pr_cover_desc Pr_cover_desc,
>coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
>Parent_County_id,
>a13.Parent_County_desc Parent_County_desc,
>coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
>a13.County_desc County_desc,
>coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
>a13.Area_Desc Area_Desc,
>coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
>pa3.Po_rd_rating_code) Po_rd_rating_code,
>coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
> a14.week_desc week_desc,
> pa1.WJXBFS1 WJXBFS1,
> pa2.WJXBFS1 WJXBFS2,
> pa3.WJXBFS1 WJXBFS3
>from marc_ZZT5J02027GMD000 pa1(nolock)
> full outer join marc_ZZT5J02027GMD001 pa2(nolock)
> on (pa1.Area_Id = pa2.Area_Id and
> pa1.County_id = pa2.County_id and
> pa1.Parent_County_id = pa2.Parent_County_id and
> pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
> pa1.Pr_cover_id = pa2.Pr_cover_id and
> pa1.Ra_gender_id = pa2.Ra_gender_id and
> pa1.Week_id = pa2.Week_id)
> full outer join marc_ZZT5J02027GMD002 pa3(nolock)
> on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
> coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id) = pa3.Parent_County_i
d
>and
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
>pa3.Po_rd_rating_code and
> coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
> coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
> coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
> join PRt_lu_Cover a11
> on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
>a11.Pr_cover_id)
> join RAv_lu_Gender a12
> on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
>a12.Ra_gender_id)
> join POt_lu_rd_post_code a13
> on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
> join TIt_lu_day a14
> on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)
>
>
>|||thanks all. i have processed the change request and am going for the bug fix
on the dimension itself.
"Steve Kass" wrote:

> When you say you must use this table instead of a lookup, can you elaborat
e?
> Is it possible that you can use this without DISTINCT initially and
> apply DISTINCT
> elsewhere in your process where it will not require as much work? Also,
> what is
> the known bug you mention? Maybe someone knows it and has a workaround.
> Steve Kass
> Drew University
> marcmc wrote:
>
>

Query Performance

HI to All!
Is there anyway to improve the performance of Queries involving
Substring clause. i have indexes on the query columns but performance is
not good. e.g if i use a query without substring it gives me result in
less than 1 seconds over a more than 1.3 million rows and when i use
substring it takes more than 10 seconds.
Regards
Farid
*** Sent via Developersdex http://www.examnotes.net ***Sometimes there is and sometimes there is not. Either way - it starts with
you posting DDL and sample data.
Maybe you don't even need the substring function. How can we tell?
ML|||
Dear ML
i have to use Substring function because i have data like
600600-02-12345 here i 600600 means my center. i have to substring this
to identify records related to 600600 center.
i m issuing following query
select count(distinct(substring(output_ref,1,6)
)) as ns123
from tbl_Main_Con where day(load_date)= '7' and month(load_date)= '9'
and year(load_date)= '2005'
this query returns result in more than 10 seconds. how can i improve its
performance.
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Change your WHERE clause to:
where
load_date >= '20050907'
and load_date < '20050908'
You may want to create an index on load_date or (load_date, output_ref).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:OVWKl75sFHA.1256@.TK2MSFTNGP09.phx.gbl...
Dear ML
i have to use Substring function because i have data like
600600-02-12345 here i 600600 means my center. i have to substring this
to identify records related to 600600 center.
i m issuing following query
select count(distinct(substring(output_ref,1,6)
)) as ns123
from tbl_Main_Con where day(load_date)= '7' and month(load_date)= '9'
and year(load_date)= '2005'
this query returns result in more than 10 seconds. how can i improve its
performance.
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Try using the LEFT function, although I doubt it would make any difference.
Are frequently sought columns indexed?
If you can, consider redesigning the table to store this piece of
information separately. Maybe even in a computed column, e.g. using the
following expression: left(output_ref, 6).
Read Tom's reply for another suggestion.
ML|||Just check with this
Initialize the parameter to NULL
SET @.Auto = 0
Detail.Automated = ISNULL(@.Auto ,Detail.Automated)
NOTE : Constraint Detail.Automated is not null
Detail.Automated = ISNULL(@.Auto ,Detail.Automated) and Detail.Automated IS
NULL
NOTE : Constraint Detail.Automated can have NULL values.
and Also check whether any index on the search arguments in the where caluse
.
HTH
Rajesh Peddireddy
"Jim Abel" wrote:

> 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 @.Auto|||check also indexes on the join fields
HTH
Rajesh Peddireddy
"Rajesh" wrote:
> Just check with this
> Initialize the parameter to NULL
> SET @.Auto = 0
> Detail.Automated = ISNULL(@.Auto ,Detail.Automated)
> NOTE : Constraint Detail.Automated is not null
> Detail.Automated = ISNULL(@.Auto ,Detail.Automated) and Detail.Automated I
S
> NULL
> NOTE : Constraint Detail.Automated can have NULL values.
> and Also check whether any index on the search arguments in the where calu
se.
> HTH
> Rajesh Peddireddy
> "Jim Abel" wrote:
>