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