Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Sp runs from 7 sec to 10 min (UnitPricingTrans) Expand / Collapse
Author
Message
Posted Monday, November 4, 2013 3:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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.


  Post Attachments 
DDL.txt (11 views, 40.51 KB)
Post #1511317
Posted Monday, November 4, 2013 3:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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

Post #1511319
Posted Monday, November 4, 2013 3:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 2,328, Visits: 3,505
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1511320
Posted Monday, November 4, 2013 6:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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

Post #1511340
Posted Tuesday, November 5, 2013 8:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 2,328, Visits: 3,505
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1511520
Posted Tuesday, November 5, 2013 11:15 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 98, Visits: 2,614
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.
Post #1511572
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse