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
Output of @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 another 200 lines...
This is the schema:
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) )