Query longer in SP: plans

Showing posts with label plans. Show all posts

Monday, March 26, 2012

Query Plans of 2005 same in 2000 QA ?

I know there have been changes to certain execution plans in 2005.. For eg:
, there is no more bookmark loolup thats displayed in 2005, but is
represented as a nested loop join..
All i want to know is that if i run my query from SQL 2000 QA connected to
SQL 2005 instance, will it show the new plans if I ask for a display of plan
?
I dont have this setup now so cant test it myself, hence I thought I would
ask you..The information that the GUI query plans are using comes from the engine, so if you run QA against a
2005 server, you will see a nested loop join instead of a bookmark lookup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <Hassan@.hotmail.com> wrote in message news:e8IERBTKHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I know there have been changes to certain execution plans in 2005.. For eg: , there is no more
>bookmark loolup thats displayed in 2005, but is represented as a nested loop join..
> All i want to know is that if i run my query from SQL 2000 QA connected to SQL 2005 instance, will
> it show the new plans if I ask for a display of plan ?
> I dont have this setup now so cant test it myself, hence I thought I would ask you..
>|||On Tue, 26 Dec 2006 12:56:28 -0800, Hassan wrote:
>I know there have been changes to certain execution plans in 2005.. For eg:
>, there is no more bookmark loolup thats displayed in 2005, but is
>represented as a nested loop join..
Hi Hassan,
And this will change yet again on SP2 (that is, the nested loop join
remains, but the second operator of the join will now labeled as a
clustered index lookup rather than a clustered index seek).
>All i want to know is that if i run my query from SQL 2000 QA connected to
>SQL 2005 instance, will it show the new plans if I ask for a display of plan
>?
I tested this with version 8.00.2039 (SP4) of QA and 9.00.2153 (SP1) of
SQL Server 2005, and the answer is: yes, it will display the new plans.
I'm not sure what happens if you try QA against an SP2 version of SQL
Server 2005, since QA has never heard of a "clustered index lookup"
operator. But I can't test that at the moment.
--
Hugo Kornelis, SQL Server MVP

Friday, March 23, 2012

Query Plans differs

Why shoudl the query plan derived by an SP and one on Query Analyser would
differ widely?
I have a system that has been running stably for a while now when one of the
queries started behaving badly. The SP version of the query comes out with
an estimated cost of 0.311 whereas if I take the same TSQL and paste it into
Query Analyser it estimates as 0.153.
Unfortuately, the query plan cost isn't the whole story as when estimated
the SP performs about 5m page reads whereas the explicit code only takes 800
page reads - this makes a big difference in execution time :-)
Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
doesn't effect the QP choosen.
Regards
PaulPaul,
The two pieces of code, TSQL script and stored procedure, are not optimized
the same because of the knowledge available at the time of optimization.
For example, the TSQL script has the variables set with the values that you
want to run.
Here is one thing that causes stored procedures to be less than optimal:
If internally to a stored proc you set a variable, the optimizer (which has
no value for the variable when it optimizes) may have selected a suboptimal
plan. So, if this is your case, rather than:
DECLARE @.MyVal INT
SET @.MyVal = 123
SELECT ... WHERE MyCol = @.MyVal
You could do:
DECLARE @.MyVal INT
SET @.MyVal = 123
EXEC AnotherStoredProcedure @.MyVal
The called stored procedure will optimize with the value supplied in @.MyVal.
Remember, of course, that recompiles of stored procedures are common, so
this is not as simple as black and white.
And, of course, there are other factors that could mislead the optimizer.
RLF
"Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
> Why shoudl the query plan derived by an SP and one on Query Analyser would
> differ widely?
> I have a system that has been running stably for a while now when one of
> the
> queries started behaving badly. The SP version of the query comes out with
> an estimated cost of 0.311 whereas if I take the same TSQL and paste it
> into
> Query Analyser it estimates as 0.153.
> Unfortuately, the query plan cost isn't the whole story as when estimated
> the SP performs about 5m page reads whereas the explicit code only takes
> 800
> page reads - this makes a big difference in execution time :-)
> Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
> doesn't effect the QP choosen.
> Regards
> Paul
>|||Thanks, there was one such variable, so I changed the code as per your
suggestion - reduced it from 5m reads to around 4.5m :-)
The optimiser normally does such a good job that it's just a pain when it
gets it wrong. Given the nature of this query, it's almost impossible to put
any useful hints that wouldn't cause much more pain elsewhere in the system.
I'll have to see if I can program around it with a different query just to
get it back running - previously it took around 100ms now it takes 10s+
Paul
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Paul,
> The two pieces of code, TSQL script and stored procedure, are not
optimized
> the same because of the knowledge available at the time of optimization.
> For example, the TSQL script has the variables set with the values that
you
> want to run.
> Here is one thing that causes stored procedures to be less than optimal:
> If internally to a stored proc you set a variable, the optimizer (which
has
> no value for the variable when it optimizes) may have selected a
suboptimal
> plan. So, if this is your case, rather than:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> SELECT ... WHERE MyCol = @.MyVal
> You could do:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> EXEC AnotherStoredProcedure @.MyVal
> The called stored procedure will optimize with the value supplied in
@.MyVal.
> Remember, of course, that recompiles of stored procedures are common, so
> this is not as simple as black and white.
> And, of course, there are other factors that could mislead the optimizer.
> RLF
> "Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
> news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
would
with
estimated
it
>|||Paul,
Maybe if you post the query in question (accompanied with the relevant
DDL) someone might be able to give a more specific advice...
Gert-Jan
Paul Hatcher wrote:
> Thanks, there was one such variable, so I changed the code as per your
> suggestion - reduced it from 5m reads to around 4.5m :-)
> The optimiser normally does such a good job that it's just a pain when it
> gets it wrong. Given the nature of this query, it's almost impossible to p
ut
> any useful hints that wouldn't cause much more pain elsewhere in the syste
m.
> I'll have to see if I can program around it with a different query just to
> get it back running - previously it took around 100ms now it takes 10s+
> Paul
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> optimized
> you
> has
> suboptimal
> @.MyVal.
> would
> with
> estimated
> it|||Gert-Jan
Thanks for the offer, but it's too complicated - as an example I would guess
that the query pans out to about a 30 table join, mostly self-joins as it's
a triple-based schema, but still fairly hairy.
I've now managed to solve it by a different structured query.
Paul
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422766C1.A0FE26A1@.toomuchspamalready.nl...
> Paul,
> Maybe if you post the query in question (accompanied with the relevant
> DDL) someone might be able to give a more specific advice...
> Gert-Jan
>
> Paul Hatcher wrote:
it
put
system.
to
optimization.
that
optimal:
(which
so
optimizer.
one of
out
it
takes
butsql

Query Plans differs

Have you tryed updating the statistic ?
Try doing an
exec sp_createstats
exec sp_updatestats
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius

>--Original Message--
>Why shoudl the query plan derived by an SP and one on
Query Analyser would
>differ widely?
>I have a system that has been running stably for a while
now when one of the
>queries started behaving badly. The SP version of the
query comes out with
>an estimated cost of 0.311 whereas if I take the same
TSQL and paste it into
>Query Analyser it estimates as 0.153.
>Unfortuately, the query plan cost isn't the whole story
as when estimated
>the SP performs about 5m page reads whereas the explicit
code only takes 800
>page reads - this makes a big difference in execution
time :-)
>Any ideas how to solve this - I've tried WITH RECOMPILE
on the SP, but it
>doesn't effect the QP choosen.
>Regards
>Paul
>
>.
>Yes, the statistics are updated before the run - thanks for the suggestion
though
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
> Have you tryed updating the statistic ?
> Try doing an
> exec sp_createstats
> exec sp_updatestats
> Peter
> "Real knowledge is to know the extent of one's ignorance."
> Confucius
>
> Query Analyser would
> now when one of the
> query comes out with
> TSQL and paste it into
> as when estimated
> code only takes 800
> time :-)
> on the SP, but it|||The only other thing I can think of is to delete your sp
then immediatly re-create it, it does sound like your sp
has gone a bit odd.
BTW my other sugestion about updating the stats was
garbage (when I had a chance to think about it)
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain

>--Original Message--
>Yes, the statistics are updated before the run - thanks
for the suggestion
>though
>Paul
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
ignorance."
while
explicit
>
>.
>|||I've tried re-creating the SP a couple of times with no effect - as I said
it's really odd as this procedure has been in production since November and
it started behaving oddly a couple of days ago, with no real change in the
underlying data volume or distribution :-(
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9a01c5200f$5676c630$a601280a@.phx.gbl...
> The only other thing I can think of is to delete your sp
> then immediatly re-create it, it does sound like your sp
> has gone a bit odd.
> BTW my other sugestion about updating the stats was
> garbage (when I had a chance to think about it)
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
>
> for the suggestion
> wrote in message
> ignorance."
> while
> explicit

query plans different

I have two 4 way server( production and test)
The only difference being IO layout and production has more RAM than the
test box
Same database on both ( did a backup and restore so as to be identical in
schema,etc)
but the query plans are different. it runs in like 1 sec in test and over a
min in production.
How do i go about getting the same query plan from test over to production ?Hassan,
Check to make sure the statistics are current and up-to-date. Out-of-date
statistics can cause the optimizer to choose a different access method.
HTH
Jerry
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:OjHNVyA1FHA.3892@.TK2MSFTNGP12.phx.gbl...
>I have two 4 way server( production and test)
> The only difference being IO layout and production has more RAM than the
> test box
> Same database on both ( did a backup and restore so as to be identical in
> schema,etc)
> but the query plans are different. it runs in like 1 sec in test and over
> a min in production.
> How do i go about getting the same query plan from test over to production
> ?
>

query plans different

I have two 4 way server( production and test)
The only difference being IO layout and production has more RAM than the
test box
Same database on both ( did a backup and restore so as to be identical in
schema,etc)
but the query plans are different. it runs in like 1 sec in test and over a
min in production.
How do i go about getting the same query plan from test over to production ?
Hassan,
Check to make sure the statistics are current and up-to-date. Out-of-date
statistics can cause the optimizer to choose a different access method.
HTH
Jerry
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:OjHNVyA1FHA.3892@.TK2MSFTNGP12.phx.gbl...
>I have two 4 way server( production and test)
> The only difference being IO layout and production has more RAM than the
> test box
> Same database on both ( did a backup and restore so as to be identical in
> schema,etc)
> but the query plans are different. it runs in like 1 sec in test and over
> a min in production.
> How do i go about getting the same query plan from test over to production
> ?
>

Query Plans & Statistics

Gurus,

I'm trying to get an application finished that works like Query Analizer in
terms of returning query plans and statistics.

Problem the co-author is having:

>In using ADO to connect to SQL Server, I'm trying to retrieve multiple
>datasets AND statistics that are usually returned via the OnInfoMessage
>event. For those that are familiar with SQL Server, I need the results
>returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options.
>Anyone had any luck doing this before?

Can anyone shed any light on this please?

Thanks.

BTW if anyone wants to take a look at the tool so far - to see what I'm
delving into:
http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1

Much Appreciated!!How about Object Browser pane? This one comes handy very often, you know?|||I'm not sure what you mean by Object Browser Pane - unless you mean something in Microsoft Query analizer...??

The problem I have is that this is a new tool that doesn't have any panes..just pains...arghhh.

Thanks for the help though!|||Here's some more info from the original post

http://81.130.213.94/myforum/forum_posts.asp?TID=26&PN=1

SET STATISTICS IO ON
SELECT 1 FROM sometable

IO statistics are returned as messages in the 2nd (blank) recordset. Good.

SET STATISTICS PROFILE ON
SELECT 1 FROM sometable

Execution plan is returned in the 2nd recordset. Good.

SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable

2 recordsets are returned, the 2nd containing the execution plan. But the
IO statistics are nowhere to be found. Help!

SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT 1 FROM sometable
PRINT 1

3 recordsets are returned. The 2nd one contains the execution plan, the
third is blank but contains '1' as a info message. The IO statistics are
nowhere to be found. Help!sql

Query plans

I am a bit confused after reading a SQL Server Magazine
article about query plans
If i have a query
DECLARE @.odate AS DATETIME
SET @.odate = '19980506'
SELECT * FROM Orders
WHERE OrderDate >= @.odate
GO
Will above query planbe different than
SELECT * FROM Orders
WHERE OrderDate >= '19980506'
If yes why ?
Bot abobe queries will be compliled and execution plan
will be prepared and then executed
Thanks
Sanjaythere may or may not be a different plan,
there may be slightly difference statistical estimates of
the row count involved
in the first, the optimizer will estimate the number of
rows involved for any generic value of OrderDate.
in the second, it will try to get a better estimate based
on the Orderdate value or '19980506'
>--Original Message--
>I am a bit confused after reading a SQL Server Magazine
>article about query plans
>If i have a query
> DECLARE @.odate AS DATETIME
> SET @.odate = '19980506'
> SELECT * FROM Orders
> WHERE OrderDate >= @.odate
> GO
>Will above query planbe different than
> SELECT * FROM Orders
> WHERE OrderDate >= '19980506'
>If yes why ?
>Bot abobe queries will be compliled and execution plan
>will be prepared and then executed
>Thanks
>Sanjay
>
>.
>|||Sanjay
For a variable in a open-ended range (i.e. < or >) the optimizer will
estimate that 30% of the rows in the table will meet the cristeria. That is
far to many to use an index seek with a nonclustered index, but a clustered
index could be considered.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:311501c373f8$ebe59080$a501280a@.phx.gbl...
> What is that estimate in first case, is it fixed like 10%
> 20% etc, is it selective enough to do a index seek
> Sanjay
> >--Original Message--
> >there may or may not be a different plan,
> >there may be slightly difference statistical estimates of
> >the row count involved
> >in the first, the optimizer will estimate the number of
> >rows involved for any generic value of OrderDate.
> >in the second, it will try to get a better estimate based
> >on the Orderdate value or '19980506'
> >
> >
> >>--Original Message--
> >>I am a bit confused after reading a SQL Server Magazine
> >>article about query plans
> >>
> >>If i have a query
> >> DECLARE @.odate AS DATETIME
> >> SET @.odate = '19980506'
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= @.odate
> >> GO
> >>
> >>Will above query planbe different than
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= '19980506'
> >>
> >>If yes why ?
> >>Bot abobe queries will be compliled and execution plan
> >>will be prepared and then executed
> >>
> >>Thanks
> >>Sanjay
> >>
> >>
> >>
> >>.
> >>
> >.
> >

Query plans

Hi all,
I read somewhere that you should write the fully qualified name for a table(db.dbo.table) in order to have a better chance to match the statement to a queryplan/execution plan..

Ive tested it to some of the tables in your db with different results, in some cases the query without the "complete path" was up to 3 times heavier to execute(relatively to the batch) than the same query with qualified path(i ran the two at the same time with show execution plan).

Sometimes there was no difference at all 50%-50% cost. And i wasent able to find any logic reason for the sometimes quicker execution with qualified path, sometimes a didnt even had any statistics created for the column in the where clause when the question was faster than the other one.

type of statements executed:
Select Col1,Col2 From db.dbo.table1
Select Col1,Col2 From table1

Does anyone know exactly how this works?

Any help would be mostly appreciated
JonasThe times that they were equal - were both queries on the fast side or slow side ?|||"The times that they were equal - were both queries on the fast side or slow side ?"

I really dont know..They where both executed with the same cost. My guess is that both queries where matched to an existing execution plan and should therefor been on the fast side.

Q2: Lets say that we have a stored proc that is compiled and sqlserver has generated a execution plan for it, right. Does the created execution plan uses other already created plans for the statements in the proc or does it creates a completely new one just for the statments in the proc?|||Take a look at Recompilng a Stored Procedure in bol. If you need more information or a better explanation, let me know.

Query plan

I have 2 SQL Servers with same hardware, configuration and database layout,
but the query plans are different on each server. I tried to look on BOL and
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:
>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL and
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!

Query plan

I have 2 SQL Servers with same hardware, configuration and database layout,
but the query plans are different on each server. I tried to look on BOL an
d
websites and have not find an aswer to why they are different even though
they have same data set. If you have experience this situation before or
know the answer, please help. Thanks!If the data is really the same, as well as the rest of the things you
mention, then I would look at updating statistics on both systems and
see if that brings them closer to using the same plans.
Roy Harvey
Beacon Falls, CT
On Wed, 5 Jul 2006 17:08:02 -0700, KTN <KTN@.discussions.microsoft.com>
wrote:

>I have 2 SQL Servers with same hardware, configuration and database layout,
>but the query plans are different on each server. I tried to look on BOL a
nd
>websites and have not find an aswer to why they are different even though
>they have same data set. If you have experience this situation before or
>know the answer, please help. Thanks!

Wednesday, March 21, 2012

Query Performance (am I in the ms right forum for performance Q's

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_idThis query involves many tables, of which you posted no information
(other than the row count). You also did not post or properly describe
the query plan. So there is not enough information to give detailed
advice.
If removing the predicate "a11.Tr_sub_type_id in (430, 433, 3530)"
increases the performance dramatically, then check out which index is
used (for that query), and add Tr_sub_type_id to this index (or create a
new index with this definition).
The query may benefit from a nonclustered index on
POt_lu_policy(Policy_id,Po_corp_unit_id)
You mention that you added an index to the TRt_lu_Trans_Subtype table,
but this seems irrelevant, since this table is not used in the query.
You could also consider running the Index Tuning Wizard.
Other generic advice:
- Make sure your statistics are up to date
- Make sure the expressions in a join clause have the same data type
definition
- Pay special attention to the clustered index definition of the largest
table
Hope this helps,
Gert-Jan
marcmc wrote:
> I get a massive bookmark in the execution plans when I run the below SQL t
o
> 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 disappea
rs
> and I get drastically improved performance. a11 is well indexed. A new ind
ex
> 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 piec
es
> 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 t
o
> 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 disappea
rs
> and I get drastically improved performance. a11 is well indexed. A new ind
ex
> 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 piec
es
> 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
>sql

Query Performance (am I in the ms right forum for performance Q's

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_idThis query involves many tables, of which you posted no information
(other than the row count). You also did not post or properly describe
the query plan. So there is not enough information to give detailed
advice.
If removing the predicate "a11.Tr_sub_type_id in (430, 433, 3530)"
increases the performance dramatically, then check out which index is
used (for that query), and add Tr_sub_type_id to this index (or create a
new index with this definition).
The query may benefit from a nonclustered index on
POt_lu_policy(Policy_id,Po_corp_unit_id)
You mention that you added an index to the TRt_lu_Trans_Subtype table,
but this seems irrelevant, since this table is not used in the query.
You could also consider running the Index Tuning Wizard.
Other generic advice:
- Make sure your statistics are up to date
- Make sure the expressions in a join clause have the same data type
definition
- Pay special attention to the clustered index definition of the largest
table
Hope this helps,
Gert-Jan
marcmc wrote:
> 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
>

Query Performance (am I in the ms right forum for performance Q's

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
This query involves many tables, of which you posted no information
(other than the row count). You also did not post or properly describe
the query plan. So there is not enough information to give detailed
advice.
If removing the predicate "a11.Tr_sub_type_id in (430, 433, 3530)"
increases the performance dramatically, then check out which index is
used (for that query), and add Tr_sub_type_id to this index (or create a
new index with this definition).
The query may benefit from a nonclustered index on
POt_lu_policy(Policy_id,Po_corp_unit_id)
You mention that you added an index to the TRt_lu_Trans_Subtype table,
but this seems irrelevant, since this table is not used in the query.
You could also consider running the Index Tuning Wizard.
Other generic advice:
- Make sure your statistics are up to date
- Make sure the expressions in a join clause have the same data type
definition
- Pay special attention to the clustered index definition of the largest
table
Hope this helps,
Gert-Jan
marcmc wrote:
> 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
>

Tuesday, March 20, 2012

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] 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
Marc,
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
>

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] 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_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
>

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
>

Friday, March 9, 2012

query optimizer

Have two queries that are identical except for the one
parameter that is passed. I am getting two completely
different execution plans. Anyone run across this before.
ThanksIf the final result is different then the execution plan may well be
different. For example a query that returns just 1 row from a table may be
more likely to use an index seek than the same query with a different
parameter that returns 1 million rows from the table.
--
David Portas
--
Please reply only to the newsgroup
--
"cheilig" <cheilig@.utmck.edu> wrote in message
news:1def01c3839b$92a96390$a001280a@.phx.gbl...
> Have two queries that are identical except for the one
> parameter that is passed. I am getting two completely
> different execution plans. Anyone run across this before.
> Thanks|||The following might be helpfull ( I found it in
programming section)
Subject: Re: Slow Stored Procedures -- Executes fine in
Query Analyizer
From: "Erland Sommarskog" <sommar@.algonet.se> Sent:
8/25/2003 1:39:09 PM
Darchele Webb (dwebb@.insituform.com) writes:
> I have a parameterized stored procedure that when
executed
> from Query Analyzer as:
> exec sp_storedProcedureName parameter1, parameter2,
etc...
> takes 1:07 minutes and only takes 0:04 minutes when the
> stored procedure is copied into Query Analyzer and run.
> This is a huge difference and I can't figure out way.
> It's the same exact query.
There are several possible reasons. One has to do with the
fact
that when SQL Server builds a query plan for a stored
procedure,
it knows about values of constants and input parameter,
but not
variables. So when you say:
SELECT * FROM tbl WHERE col = 12
it knows that the search argument is 12, and it consult
its statistics
to find that 12 is a good value for using the non-
clustered index on
col. But if you say:
SELECT * FROM tbl WHERE col = @.val
It will have to guess, and it may find that there are many
values for
which the index on col would be no good. If @.val is an
input parameter,
SQL Server will use the value on first call, but that
means that for the
next invocation, the plan is less good. There are a few
more issues
about input parameter values, that I ignore for now.
A completely different reason is that there is an indexed
view or an
index of a computed column involved. In this case, it is
important
how the procedure was created. If you do
SELECT objectproperty(object_id
('procname'), 'IsQuotedIdentOn'),
objectproperty(object_id
('procname'), 'IsAnsiNullsOn')
This should return (1, 1) for the indexes on views and
computed
columns to be used.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books
.asp
Mirna
>--Original Message--
>Have two queries that are identical except for the one
>parameter that is passed. I am getting two completely
>different execution plans. Anyone run across this before.
>Thanks
>.
>

Monday, March 26, 2012

Query Plans of 2005 same in 2000 QA ?

Friday, March 23, 2012

Query Plans differs

Query Plans differs

query plans different

query plans different

query plans different

Query Plans & Statistics

Query plans

Query plans

Query plan

Query plan

Wednesday, March 21, 2012

Query Performance (am I in the ms right forum for performance Q's

Query Performance (am I in the ms right forum for performance Q's

Query Performance (am I in the ms right forum for performance Q's

Tuesday, March 20, 2012

Query Performance

Query Performance

Query Performance

Query Performance

Friday, March 9, 2012

query optimizer

Query longer in SP

Blog Archive

About Me