Need your help to optimze the SP.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need your help to optimze the SP.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-30 : 17:08:44

[code]
I wonder if there is a way to combine into 1 or 2 statements instead of branch to multiple IF..ELSE statement.
Any help is greatly appreciate. I am using SQL 2008.

Thanks.

------------------------------------------------------------------

IF OBJECT_ID('dbo.spGetMetrics1', 'p') IS NOT NULL
DROP PROCEDURE [dbo].[spGetMetrics1]
GO

CREATE PROCEDURE [dbo].[spGetMetrics1]
(
@OperationId INT = NULL,
@FromDateTime DATETIME = NULL,
@ToDateTime DATETIME = NULL,
@Stores NVARCHAR(2000) = NULL,
@Workstations NVARCHAR(2000) = NULL,
@SelectMode TINYINT = 0
)
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

IF ( (@SelectMode = 0 )
AND (@Stores = '' OR @Stores IS NULL)
AND (@Workstations = '' OR @Workstations IS NULL) )
BEGIN
PRINT 'Go to the first 1.'
SELECT (b.MSecs/1000.0) AS MSecs,
b.Operation,
b.WSKey,
b.ServTime,
a.Store_No
FROM WSCONFIGHT AS a
JOIN MetricsHt AS b
ON (a.P_Key = b.WSKey)
WHERE ( b.Operation = @OperationId )
AND ( b.ServTime >= @FromDateTime )
AND ( b.ServTime < @ToDateTime )
END
ELSE
IF ( @SelectMode = 1 )
AND (@Stores > '')
AND ( @Workstations = '' OR @Workstations IS NULL)
BEGIN
PRINT 'Go to #2.'
SELECT (b.MSecs/1000.0) AS MSecs,
b.Operation,
b.WSKey,
b.ServTime,
a.Store_No
FROM WSCONFIGHT AS a
JOIN MetricsHt AS b
ON (a.P_Key = b.WSKey)
WHERE ( b.Operation = @OperationId )
AND ( b.ServTime >= @FromDateTime )
AND ( b.ServTime < @ToDateTime )
AND ( a.Store_No IN ( SELECT s
FROM SPLITSTRING(@Stores, ',') )
)
END
ELSE
BEGIN
PRINT 'Go to #3'
SELECT (b.MSecs/1000.0) AS MSecs,
b.Operation,
b.WSKey,
b.ServTime,
a.Store_No
FROM WSCONFIGHT AS a
JOIN MetricsHt AS b
ON (a.P_Key = b.WSKey)
WHERE ( b.Operation = @OperationId )
AND ( b.ServTime >= @FromDateTime )
AND ( b.ServTime < @ToDateTime )
AND ( b.WSKey IN (SELECT s
FROM SPLITSTRING(@Workstations, ',') )
)
END;
GO

-------------------------------------------------------------

--Testing...
--1
EXECute dbo.spGetMetrics1 @SelectMode = 0,
@OperationId = 10400,
@FromDateTime = '2012-01-30 10:05:04.553',
@ToDateTime = '2012-03-30 10:05:04.553',
@Stores = '',
@Workstations = ''

--#2 Call with stores

EXECute dbo.spGetMetrics1 @SelectMode = 1,
@OperationId= 10400,
@FromDateTime= '2012-01-30 10:05:04.553',
@ToDateTime = '2012-03-30 10:05:04.553',
@Stores = N'100,230,316,327',
@Workstations= N'';

-- 3 call with workstations
EXECute dbo.spGetMetrics1 @SelectMode = 2,
@OperationId = 10400,
@FromDateTime = '2012-01-30 10:05:04.553',
@ToDateTime = '2012-03-30 10:05:04.553',
@Stores = N'100,230,316,327',
@Workstations = N'733,852,80000087,80000107';
go[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-30 : 17:13:51

Read uncommitted!?

Show us the statistics io and time plus the execution plan.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-30 : 18:06:19

[code]Here is the statistic.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
MSecs Operation WSKey ServTime Store_No
--------------------------------------- --------- ----------- ----------------------- --------
1.625000 10400 712 2012-02-14 16:42:59.000 343
1.796000 10400 80000009 2012-02-14 16:42:24.000 88
2.172000 10400 746 2012-02-14 16:42:22.000 322

Table 'WSCONFIGHT'. Scan count 9, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'METRICSHT'. Scan count 9, logical reads 347041, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 19440 ms, elapsed time = 5442 ms.

SQL Server Execution Times:
CPU time = 19440 ms, elapsed time = 5443 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Query time (minutes:seconds) 0 :5 [/code]

quote:
Originally posted by tkizer

Read uncommitted!?

Show us the statistics io and time plus the execution plan.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-30 : 18:08:52

You are clearly missing an index on the METRICSHT table as per the logical reads shown in that output. Could you show us your indexes on that table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-30 : 18:53:25

[code]Here is the indexes on MetricHT table. Thanks.

EXECute sp_helpindex MetricsHt;
go

index_name index_description index_keys
------------------------ ---------------------------------------------------- ----------------------
nci_OperationServTime nonclustered located on PRIMARY Operation, ServTime
nci_WSKey nonclustered located on PRIMARY WSKey
XPKPKey clustered, unique, primary key located on PRIMARY P_KEY[/code]

quote:
Originally posted by tkizer

You are clearly missing an index on the METRICSHT table as per the logical reads shown in that output. Could you show us your indexes on that table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-30 : 19:08:33

Try adding WSKey to the nci_OperationServTime index and also add MSecs as an include column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-30 : 19:22:46

Also bear this in mind: http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
--
Gail Shaw
SQL Server MVP

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-03-30 : 19:24:17

[code]Can you please explain why?. Because WSKey is already index. MSecs column is not used in the WHERE clause. Thanks.[/code]

quote:
Originally posted by tkizer

Try adding WSKey to the nci_OperationServTime index and also add MSecs as an include column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-31 : 00:24:12

Because a covering index is best.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources