Speed Optimization Guidelines for a Stored Procedure That Uses Views
Building on a previous question and with a lot of help from Damir Sudarevich (thanks) I have the following sql code that works great but is very slow. Can anyone suggest how I can speed this up and optimize for speed.
I am now using SQL Server Express 2008 (not 2005 as per my original question).
What this code does is get the parameters and their associated values from multiple tables, and rotate the table in a form that can be easily compared. It works fine for one of the two rows of data, but now I am testing 100 rows and it takes over 7 minutes to run GetJobParameters.
Any advice is greatly appreciated, thank you in an advanced way.
/***********************************************************************************************
** 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
@Sql_output:
SELECT
[Job Name]
,MAX(CASE [Param Type Name] WHEN '001' THEN Val ELSE NULL END) AS [001]
,MAX(CASE [Param Type Name] WHEN '002' THEN Val ELSE NULL END) AS [002]
,MAX(CASE [Param Type Name] WHEN '003' THEN Val ELSE NULL END) AS [003]
,MAX(CASE [Param Type Name] WHEN '004' THEN Val ELSE NULL END) AS [004]
,MAX(CASE [Param Type Name] WHEN '005' THEN Val ELSE NULL END) AS [005]
,MAX(CASE [Param Type Name] WHEN '006' THEN Val ELSE NULL END) AS [006]
,MAX(CASE [Param Type Name] WHEN '007' THEN Val ELSE NULL END) AS [007]
,MAX(CASE [Param Type Name] WHEN '008' THEN Val ELSE NULL END) AS [008]
,MAX(CASE [Param Type Name] WHEN '009' THEN Val ELSE NULL END) AS [009]
,MAX(CASE [Param Type Name] WHEN '010' THEN Val ELSE NULL END) AS [010]
,MAX(CASE [Param Type Name] WHEN '011' THEN Val ELSE NULL END) AS [011]
,MAX(CASE [Param Type Name] WHEN '012' THEN Val ELSE NULL END) AS [012]
,MAX(CASE [Param Type Name] WHEN '013' THEN Val ELSE NULL END) AS [013]
,MAX(CASE [Param Type Name] WHEN '014' THEN Val ELSE NULL END) AS [014]
,MAX(CASE [Param Type Name] WHEN '015' THEN Val ELSE NULL END) AS [015]
,MAX(CASE [Param Type Name] WHEN '016' THEN Val ELSE NULL END) AS [016]
,MAX(CASE [Param Type Name] WHEN 'Acceptance' THEN Val ELSE NULL END) AS [Acceptance]
,MAX(CASE [Param Type Name] WHEN 'AdditionalOptionsAperture1' THEN Val ELSE NULL END) AS [AdditionalOptionsAperture1]
,MAX(CASE [Param Type Name] WHEN 'AdditionalOptionsAperture2' THEN Val ELSE NULL END) AS [AdditionalOptionsAperture2]
,MAX(CASE [Param Type Name] WHEN 'AdditionalOptionsBarcode' THEN Val ELSE NULL END) AS [AdditionalOptionsBarcode]
,MAX(CASE [Param Type Name] WHEN 'AdditionalOptionsFiducial1' THEN Val ELSE NULL END) AS [AdditionalOptionsFiducial1]
,MAX(CASE [Param Type Name] WHEN 'AdditionalOptionsFiducial2' THEN Val ELSE NULL END) AS [AdditionalOptionsFiducial2]
,MAX(CASE [Param Type Name] WHEN 'AlignsFirstPcb' THEN Val ELSE NULL END) AS [AlignsFirstPcb]
,MAX(CASE [Param Type Name] WHEN 'Aperture1DefinedX' THEN Val ELSE NULL END) AS [Aperture1DefinedX]
,MAX(CASE [Param Type Name] WHEN 'Aperture1DefinedY' THEN Val ELSE NULL END) AS [Aperture1DefinedY]
and 200 more lines ...
This is the diagram:
CREATE TABLE Machines (
MachineID UNIQUEIDENTIFIER PRIMARY KEY,
PrinterType NVARCHAR(255), --UNIQUEIDENTIFIER REFERENCES PrinterTypes(TypeId),
Name NVARCHAR(255),
[Desc] NVARCHAR(MAX),
HostName NVARCHAR(255),
IP NVARCHAR(64), -- allows IPv4 and IPv6
Serial NVARCHAR(10),
Location NVARCHAR(255) )
GO
CREATE TABLE JobFiles (
JobID UNIQUEIDENTIFIER PRIMARY KEY,
MachineID UNIQUEIDENTIFIER REFERENCES Machines(MachineID) ON DELETE CASCADE,
[Desc] NVARCHAR(MAX),
Name NVARCHAR(255),
JobOpen BIT,
[CreateDate] DATETIME ,
[ModifyDate] DATETIME ,
[CreatedByUser] NVARCHAR(64) ,
[ModifiedByUser] NVARCHAR(64) )
GO
CREATE TABLE JobParamType (
ParamTypeID UNIQUEIDENTIFIER PRIMARY KEY,
Name NVARCHAR(255),
[Desc] NVARCHAR(MAX),
JobDataType INT)
GO
CREATE TABLE JobParamGroup (
ParamGroupID UNIQUEIDENTIFIER PRIMARY KEY,
Name NVARCHAR(255),
[Desc] NVARCHAR(MAX))
GO
CREATE TABLE JobParams (
ParamID UNIQUEIDENTIFIER PRIMARY KEY,
ParamTypeID UNIQUEIDENTIFIER REFERENCES JobParamType(ParamTypeID) ON DELETE CASCADE,
ParamGroupID UNIQUEIDENTIFIER REFERENCES JobParamGroup(ParamGroupID) ON DELETE CASCADE,
JobFileID UNIQUEIDENTIFIER REFERENCES JobFiles(JobID) ON DELETE CASCADE,
IsEnabled BIT)
GO
-- Text based property
CREATE TABLE JobTrait (
ParamID UNIQUEIDENTIFIER PRIMARY KEY REFERENCES JobParams(ParamID) ON DELETE CASCADE,
Value NVARCHAR(MAX) )
GO
-- Numeric based property
CREATE TABLE JobMeasurement (
ParamID UNIQUEIDENTIFIER PRIMARY KEY REFERENCES JobParams(ParamID) ON DELETE CASCADE,
Value FLOAT,
Format NVARCHAR(20),
Unit NVARCHAR(MAX) )
+2
a source to share
1 answer
Okay, without getting into the details of your database schema, my first question would be, have you created indexes for each of the join predicates used in your view definition?
In response to your comments .....
A typical starting point for a non-performance database is:
- Create a clustered index on each table, usually on the primary key.
- Create nonclustered indexes on all foreign keys, bearing in mind that they will most likely be used in T-SQL Join clauses.
Without a basic indexing strategy in your database, most of your queries will do table scans (i.e. every row in the table is affected), which is bad ...
For a great and concise introduction to SQL Server Indexes, I suggest you read Brad Sure Guide to Indexes
+1
a source to share