Query Perormance (Problem bookmark)

I have a bookmark caused by the a15.Tr_type_id where condition below. I thin
k
it is because the query tries to satisfy the date condition before going to
get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
bookmark disappears and performance boosts. I tried a clustered index on
a15.Tr_type_id and it improved it a bit more. Any recommendations much
appreciated?
select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
a14.Ra_gender_id Ra_gender_id,
a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
a14.RA_yr_band_New_id RA_yr_band_New_id,
a12.Pr_cover_id Pr_cover_id,
count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_id
end)) WJXBFS1
into #ZZT5J0302LPMD004
from Z_fat_bse_po_risk_detail a11
join Z_prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join Z_POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join Z_RAt_lu_Rated a14
on (a11.Rated_driver_id = a14.Rated_driver_id)
join Z_TRt_lu_Trans_Subtype a15
on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
where (a12.Pr_cover_id in ('C', 'F')
and a14.Ra_gender_id in ('F', 'M')
and a14.Ra_licence_id in ('F', 'P')
and a12.Pr_Group_id in (2, 3)
and a14.RA_yr_band_New_id not in (1)
and a11.Po_tr_bus_type_id in (0)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
and CONVERT(datetime, '2005-03-05 00:00:00', 120)
and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a13.Po_corp_unit_id in ('GEI')
and a11.Entered_by_id not in (7561, 7570)
and a12.Pr_cover_id in ('C', 'F', 'T')
and a14.Ra_gender_id in ('F', 'M')
and a13.Po_market_src_id not in (14))
group by a14.Ra_licence_Group_desc,
a14.Ra_gender_id,
a14.RA_yr_band_HFI_id,
a14.RA_yr_band_New_id,
a12.Pr_cover_idgolden rule, check statistics job has run before you believe an end user!
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||ps: do MVP's ever visit this forum
"marcmc" wrote:

> I have a bookmark caused by the a15.Tr_type_id where condition below. I th
ink
> it is because the query tries to satisfy the date condition before going t
o
> get the a15.Tr_type_id condition. If I comment out the a15.Tr_type_id, the
> bookmark disappears and performance boosts. I tried a clustered index on
> a15.Tr_type_id and it improved it a bit more. Any recommendations much
> appreciated?
> select a14.Ra_licence_Group_desc Ra_licence_Group_desc,
> a14.Ra_gender_id Ra_gender_id,
> a14.RA_yr_band_HFI_id RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id RA_yr_band_New_id,
> a12.Pr_cover_id Pr_cover_id,
> count((case when a12.Pr_Group_id = 5 then a11.Vehicle_id else a11.Policy_
id
> end)) WJXBFS1
> into #ZZT5J0302LPMD004
> from Z_fat_bse_po_risk_detail a11
> join Z_prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join Z_POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join Z_RAt_lu_Rated a14
> on (a11.Rated_driver_id = a14.Rated_driver_id)
> join Z_TRt_lu_Trans_Subtype a15
> on (a11.Tr_sub_type_id = a15.Tr_sub_type_id)
> where (a12.Pr_cover_id in ('C', 'F')
> and a14.Ra_gender_id in ('F', 'M')
> and a14.Ra_licence_id in ('F', 'P')
> and a12.Pr_Group_id in (2, 3)
> and a14.RA_yr_band_New_id not in (1)
> and a11.Po_tr_bus_type_id in (0)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Cur_trn_dt between CONVERT(datetime, '2004-03-07 00:00:00', 120)
> and CONVERT(datetime, '2005-03-05 00:00:00', 120)
> and a15.Tr_type_id in ('HNB', 'HNC', 'HRN', 'INB', 'IRN', 'HPR')
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a13.Po_corp_unit_id in ('GEI')
> and a11.Entered_by_id not in (7561, 7570)
> and a12.Pr_cover_id in ('C', 'F', 'T')
> and a14.Ra_gender_id in ('F', 'M')
> and a13.Po_market_src_id not in (14))
> group by a14.Ra_licence_Group_desc,
> a14.Ra_gender_id,
> a14.RA_yr_band_HFI_id,
> a14.RA_yr_band_New_id,
> a12.Pr_cover_id
>|||"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> ps: do MVP's ever visit this forum
Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, and
exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, and
Sue Hoegemeier.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.|||Ahh I see now. It's just that when I used to post on compact framework site
there was a little bubble with mvp which was easily noticeable. Thx.
"Stephen Dybing [MSFT]" wrote:

> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E13FCDE5-D0FD-4D1F-922B-CAEC5C9A584A@.microsoft.com...
> Yes. I see 12 messages posted here since 2:09PM PST yesterday, the 9th, an
d
> exactly half of those posts came from MVPs Mike Epprecht, Jasper Smith, an
d
> Sue Hoegemeier.
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Please reply to the newsgroups only, thanks.
>
>|||> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable.
I guess the SQL Server MVPs are a bit more low-key :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...[vbcol=seagreen]
> Ahh I see now. It's just that when I used to post on compact framework
> site
> there was a little bubble with mvp which was easily noticeable. Thx.
> "Stephen Dybing [MSFT]" wrote:
>|||I just noticed...if we post through the web based news reader on Microsoft's
communities site, it puts the little MVP bubble thing by our names. My posts
earlier using Agent newsreader (over 99% of my posts) don't have the bubble
thing.
So if you want a bubble by your name, that's how you can have one!
-Sue
"Dan Guzman" wrote:

> I guess the SQL Server MVPs are a bit more low-key :-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:0A8A3596-B262-4A78-93B3-861293435199@.microsoft.com...
>
>