November 4, 2013 at 9:39 am
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.
November 4, 2013 at 10:03 am
I am attaching "UnitPricingTrans.sqlplan" file
November 4, 2013 at 10:14 am
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.
November 4, 2013 at 2:15 pm
RVO (11/4/2013)
I compared execution planswith 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
November 4, 2013 at 2:39 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 4, 2013 at 3:34 pm
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.
November 4, 2013 at 3:51 pm
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
November 4, 2013 at 3:54 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 4, 2013 at 6:56 pm
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
November 5, 2013 at 8:54 am
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 SCHEMABINDINGAS
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 5, 2013 at 11:15 am
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 proddb6declare @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.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply