Advice on optimzing speed for a Stored Procedure that uses Views
- by Belliez
Based on a previous question and with a lot of help from Damir Sudarevic (thanks) I have the following sql code which works great but is very slow. Can anyone suggest how I can speed this up and optimise for speed.
I am now using SQL Server Express 2008 (not 2005 as per my original question).
What this code does is retrieves parameters and their associated values from several tables and rotates the table in a form that can be easily compared. Its great for one of two rows of data but now I am testing with 100 rows and to run GetJobParameters takes over 7 minutes to complete?
Any advice is gratefully accepted, thank you in advanced.
/***********************************************************************************************
** CREATE A VIEW (VIRTUAL TABLE) TO ALLOW EASIER RETREIVAL OF PARMETERS
************************************************************************************************/
CREATE VIEW dbo.vParameters AS
SELECT m.MachineID AS [Machine ID]
,j.JobID AS [Job ID]
,p.ParamID AS [Param ID]
,t.ParamTypeID AS [Param Type ID]
,m.Name AS [Machine Name]
,j.Name AS [Job Name]
,t.Name AS [Param Type Name]
,t.JobDataType AS [Job DataType]
,x.Value AS [Measurement Value]
,x.Unit AS [Unit]
,y.Value AS [JobDataType]
FROM dbo.Machines AS m
JOIN dbo.JobFiles AS j ON j.MachineID = m.MachineID
JOIN dbo.JobParams AS p ON p.JobFileID = j.JobID
JOIN dbo.JobParamType AS t ON t.ParamTypeID = p.ParamTypeID
LEFT JOIN dbo.JobMeasurement AS x ON x.ParamID = p.ParamID
LEFT JOIN dbo.JobTrait AS y ON y.ParamID = p.ParamID
GO
-- Step 2
CREATE VIEW dbo.vJobValues AS
SELECT [Job Name]
,[Param Type Name]
,COALESCE(cast([Measurement Value] AS varchar(50)), [JobDataType]) AS [Val]
FROM dbo.vParameters
GO
/***********************************************************************************************
** GET JOB PARMETERS FROM THE VIEW JUST CREATED
************************************************************************************************/
CREATE PROCEDURE GetJobParameters
AS
-- Step 3
DECLARE @Params TABLE (
id int IDENTITY (1,1)
,ParamName varchar(50)
);
INSERT INTO @Params (ParamName)
SELECT DISTINCT [Name]
FROM dbo.JobParamType
-- Step 4
DECLARE @qw TABLE(
id int IDENTITY (1,1)
, txt nchar(300)
)
INSERT INTO @qw (txt)
SELECT 'SELECT' UNION
SELECT '[Job Name]' ;
INSERT INTO @qw (txt)
SELECT ',MAX(CASE [Param Type Name] WHEN ''' + ParamName
+ ''' THEN Val ELSE NULL END) AS [' + ParamName + ']'
FROM @Params
ORDER BY id;
INSERT INTO @qw (txt)
SELECT 'FROM dbo.vJobValues' UNION
SELECT 'GROUP BY [Job Name]' UNION
SELECT 'ORDER BY [Job Name]';
-- Step 5
--SELECT txt FROM @qw
DECLARE @sql_output VARCHAR (MAX)
SET @sql_output = '' -- NULL + '' = NULL, so we need to have a seed
SELECT @sql_output = -- string to avoid losing the first line.
COALESCE (@sql_output + txt + char (10), '')
FROM @qw
EXEC (@sql_output)
GO
