Sp runs from 7 sec to 10 min (UnitPricingTrans)

Question

Post reply

Sp runs from 7 sec to 10 min (UnitPricingTrans)

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

RVO SSCertifiable Points: 5495 More actions · Answer 1

Looks like it was cached with @P_UNIT_TYPE = 'VEH' (runs 7 seconds).

With all other parameter values it runs 2.5 minutes now.

I tried "WITH RECOMPILE" to clear cache but nothing changed.

7 seconds with 'VEH' - 2.5 min with all other values.

I attached all indexes from [prj_detail] table.

RVO SSCertifiable Points: 5495 More actions · Answer 2

RVO

SSCertifiable

Points: 5495

November 4, 2013 at 10:03 am

#1663894

I am attaching "UnitPricingTrans.sqlplan" file

RVO SSCertifiable Points: 5495 More actions · Answer 3

I compared execution plans

with parameter 'VEH' and 'COM'

and in both cases it is this step that is taking up all the resources:

Index Scan (NonClustered)

[prj_detail].[IXN_prj_detail__per_end_date__detail_type_ind__evc_code__evc_type_code]

Cost: 91%

Another pattern I noticed is that execution time heavily depends on the number on records returned.

It runs from 2 to 12 seconds when 5-100 records returned.

It runs 2.5 minutes when 7,000 are being returned.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

RVO (11/4/2013)
I compared execution plans
with parameter 'VEH' and 'COM'
and in both cases it is this step that is taking up all the resources:
Index Scan (NonClustered)
[prj_detail].[IXN_prj_detail__per_end_date__detail_type_ind__evc_code__evc_type_code]
Cost: 91%
Another pattern I noticed is that execution time heavily depends on the number on records returned.
It runs from 2 to 12 seconds when 5-100 records returned.
It runs 2.5 minutes when 7,000 are being returned.

Looks like stale statistics is one of the issues, you are fetching 1million records and query is estimating only 1 record. try updating the stats with fullscan if possible.

There is also a missing index, try to create a covering index with all the columns in the query

ScottPletcher SSC Guru Points: 101119 More actions · Answer 5

Please post the DDL, including all indexes, for the "prj_detail" table.

If the clustered index is on an identity column, you should almost certainly change it to "accounting_period" instead. This is especially true if, as is extremely likely, you (almost) always specify an accounting_period / period range in your queries on this table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

RVO SSCertifiable Points: 5495 More actions · Answer 6

I am attaching DDL script with indexes for [prj_detail] table.

STATS last updated on October 6th.

In Production it is October 27th.

I don't know if it's good or bad.

RVO SSCertifiable Points: 5495 More actions · Answer 7

Another interesting thing with [ prj_detail ] table.

A very simple query runs 4 seconds with hard_coded value co_code = '7001'

and it runs

1 minute using parameter.

use proddb6

declare @P_CO_CODE varchar(4)

set @P_CO_CODE = '7001'

select

accounting_period

from

prj_detail

where

co_code = @P_CO_CODE

and sys_doc_type_code = 'UP'

group by

accounting_period

order by

accounting_period

ScottPletcher SSC Guru Points: 101119 More actions · Answer 8

I think the clustered index should be ( accounting_period ) or, if you prefer, ( accounting_period, prj_code ) rather than the other way around. I don't think phase_code needs to be in the clus key in this case, although I'm not against multi-column clus keys per se.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

RVO SSCertifiable Points: 5495 More actions · Answer 9

ScottPletcher,

We don't have authority to alter table definitions.

This is Financial Application database by third party.

We don't own the database.

We even create all our custom code in a separate (Diff_Data) database

that has views with the same names as table names in the main database.

And in our stored procedures we point to those views.

I tried to create an index on a view

but I get an error

"Cannot schema bind view 'prj_detail' because name 'proddb6..prj_detail' is invalid for schema binding."

Here is my syntax to create a view WITH SCHEMABINDING:

CREATE VIEW prj_detail WITH SCHEMABINDING

AS

select

[prj_code]

,[accounting_period]

,[co_code]

FROM

Proddb6.dbo.prj_detail

ScottPletcher SSC Guru Points: 101119 More actions · Answer 10

RVO (11/4/2013)
ScottPletcher,
We don't have authority to alter table definitions.
This is Financial Application database by third party.
We don't own the database.
We even create all our custom code in a separate (Diff_Data) database
that has views with the same names as table names in the main database.
And in our stored procedures we point to those views.
I tried to create an index on a view
but I get an error
"Cannot schema bind view 'prj_detail' because name 'proddb6..prj_detail' is invalid for schema binding."
Here is my syntax to create a view WITH SCHEMABINDING:
CREATE VIEW prj_detail WITH SCHEMABINDING
AS
select
[prj_code]
,[accounting_period]
,[co_code]
FROM
Proddb6.dbo.prj_detail

Ouch! If you can't change the clustered or non-clustered indexes, it will be extremely difficult to tune queries.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 11

RVO (11/4/2013)
Another interesting thing with [ prj_detail ] table.
A very simple query runs 4 seconds with hard_coded value co_code = '7001'
and it runs
1 minute using parameter.
use proddb6
declare @P_CO_CODE varchar(4)
set @P_CO_CODE = '7001'
select
accounting_period
from
prj_detail
where
co_code = @P_CO_CODE
and sys_doc_type_code = 'UP'
group by
accounting_period
order by
accounting_period

Code above is using rather variable than parameter and therefore

optimizer estimates that query returns 30% of data.

http://m.sqlmag.com/t-sql/optimizing-variables-and-parameters

I think this the reason for 1 minute run.