Query Performance

I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] O
N
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id])
ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_id
I get a massive bookmark in the execution plans when I run the below SQL to
return 23 rows of data...The volumens are as below. If I take out the
reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark disappears
and I get drastically improved performance. a11 is well indexed. A new index
was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype] O
N
[dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id])
ON [PRIMARY]
GO
and it has provided many other performance gains on a number of other pieces
of SQL. How do I begin to think about/code for this lack of performance.
Don't worry I don't expect you to understand the tables or business but if
any experiences have been overcome please post.
select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
select count(*) from prt_lu_product(nolock) -- Rows: 1719900
select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
select count(*) from ITv_lu_day(nolock) -- Rows: 4831
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Duration: 0:05:02.00 - 23 rows
select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
a15.ITv_year_id year_id,
count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
a11.Policy_id end)) WJXBFS1
into #ZZT5J0300BKMD00J
from fat_bse_po_risk_detail a11
join prt_lu_product a12
on (a11.Product_id = a12.Product_id)
join POt_lu_policy a13
on (a11.Policy_id = a13.Policy_id)
join vht_lu_vehicle a14
on (a11.Vehicle_id = a14.Vehicle_id)
join ITv_lu_day a15
on (a11.Inception_date_id = a15.Inception_date_id)
where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a11.Tr_sub_type_id in (430, 433, 3530)
and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
and a12.Pr_Group_id in (2, 3)
and a11.f_Ren_Flag = '4'
and a11.Po_tr_bus_type_id > 0)
group by a14.Vh_VhAll_group_id,
a15.ITv_year_idMarc,
reconstruct you query, not using a11.Tr_sub_type_id in (430, 433, 3530), but
use a union (all), each with its own a11.Tr_sub_type_id = condition.
Quentin
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:19F88AA4-D9D6-4B1C-BAAD-A8DE2259F182@.microsoft.com...
>I get a massive bookmark in the execution plans when I run the below SQL to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark
> disappears
> and I get drastically improved performance. a11 is well indexed. A new
> index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype]
ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id
]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other
> pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
>
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>
>
> I get a massive bookmark in the execution plans when I run the below SQL
> to
> return 23 rows of data...The volumens are as below. If I take out the
> reference to a11.Tr_sub_type_id in (430, 433, 3530), the bookmark
> disappears
> and I get drastically improved performance. a11 is well indexed. A new
> index
> was to add the CREATE UNIQUE CLUSTERED INDEX [IX_TRt_lu_Trans_Subtype]
ON
> [dbo].[TRt_lu_Trans_Subtype]([Tr_sub_type_id], [Tr_type_id
]) ON [PRIMARY]
> GO
> and it has provided many other performance gains on a number of other
> pieces
> of SQL. How do I begin to think about/code for this lack of performance.
> Don't worry I don't expect you to understand the tables or business but if
> any experiences have been overcome please post.
>
> select count(*) from fat_bse_po_risk_detail(nolock) -- Rows: 11674571
> select count(*) from POt_lu_policy(nolock) -- Rows: 2967597
> select count(*) from prt_lu_product(nolock) -- Rows: 1719900
> select count(*) from TRt_lu_Trans_Subtype(nolock) -- Rows: 9326
> select count(*) from vht_lu_vehicle(nolock) -- Rows: 3154009
> select count(*) from ITv_lu_day(nolock) -- Rows: 4831
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> -- Duration: 0:05:02.00 - 23 rows
> select a14.Vh_VhAll_group_id Vh_VhAll_group_id,
> a15.ITv_year_id year_id,
> count(distinct(case when a12.Pr_Group_id = 5 then a11.Vehicle_id else
> a11.Policy_id end)) WJXBFS1
> into #ZZT5J0300BKMD00J
> from fat_bse_po_risk_detail a11
> join prt_lu_product a12
> on (a11.Product_id = a12.Product_id)
> join POt_lu_policy a13
> on (a11.Policy_id = a13.Policy_id)
> join vht_lu_vehicle a14
> on (a11.Vehicle_id = a14.Vehicle_id)
> join ITv_lu_day a15
> on (a11.Inception_date_id = a15.Inception_date_id)
> where (a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a11.Tr_sub_type_id in (430, 433, 3530)
> and a11.Inception_date_id >= CONVERT(datetime, '2003-04-01 00:00:00', 120)
> and a11.Inception_date_id < CONVERT(datetime, '2005-04-01 00:00:00', 120)
> and a13.Po_corp_unit_id in ('GEI', 'GNI', 'GED')
> and a12.Pr_Group_id in (2, 3)
> and a11.f_Ren_Flag = '4'
> and a11.Po_tr_bus_type_id > 0)
> group by a14.Vh_VhAll_group_id,
> a15.ITv_year_id
>