Stored Procedure Performance Issue 2

[willpash]

Hi All,

I have inherited a stored procedure that forms the datasource for a linked subreport in MS Reporting Services 2005.

I have spent the best part of the weekend optimising this to improve the execution speed. It currently is taking 195ms to execute the query of the subreport, with the subreport taking 555ms to compile, with 2 values for the sample id, and a maximum of 10 rows being returned.

Any advice and/or observations would be greatly appreciated.

USE [HPLCData]
GO
/****** Object: StoredProcedure [dbo].[pr_HPLCRawData] Script Date: 08/30/2009 21:03:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pr_HPLCRawData]
@maxcol int = 5,
@sampleId int,
@samplesql varchar(8000) output
AS
BEGIN
-- Create a temporary table populated by results for a given sampleId, and given a max number of columns
-- construct an output @samplesql string to query this table of the form:
-- Select SampleId, Summary, Col1, Col2, Col3, Col4, Col5 from ##tmp_results_table union all Select SampleId, Summary, Col6, Col7, Col8, Col9, Col10 from ##tmp_results_table union all Select ... etc ... from ##tmp_results_table union all Select SampleId, Summary, Col51, Col52,' ' as Col52,' ' as Col52,' ' as Col52 from ##tmp_results_table
--
Declare @sql1 varchar(8000)
Declare @sql2 varchar(8000)
Declare @sql2b varchar(8000)
Declare @sql2c varchar(8000)
Declare @sql2d varchar(8000)
Declare @sql3 varchar(8000)
Declare @sql4 varchar(8000)
Declare @sql5a varchar(8000)
Declare @sql5b varchar(8000)
Declare @sql5c varchar(8000)
Declare @sql5d varchar(8000)
Declare @sql6 varchar(8000)
Declare @query1a varchar(8000)
Declare @query1b varchar(8000)
Declare @query1c varchar(8000)
Declare @query1d varchar(8000)
Declare @query2 varchar(8000)
Declare @query2b1 varchar(8000)
Declare @query2b2 varchar(8000)
Declare @query2b3 varchar(8000)
Declare @query2b4 varchar(8000)
Declare @query2b5 varchar(8000)
Declare @query3 varchar(8000)
Declare @query3b1 varchar(8000)
Declare @query3b2 varchar(8000)
Declare @query3b3 varchar(8000)
Declare @query3b4 varchar(8000)
Declare @query3b5 varchar(8000)
Declare @query4 varchar(8000)
Declare @query4b1 varchar(8000)
Declare @query4b2 varchar(8000)
Declare @query4b3 varchar(8000)
Declare @query4b4 varchar(8000)
Declare @query4b5 varchar(8000)
Declare @query5a varchar(8000)
Declare @query5b varchar(8000)
Declare @query5b2 varchar(8000)
Declare @query5b3 varchar(8000)
Declare @query5b4 varchar(8000)
Declare @query5c varchar(8000)
Declare @sqlline varchar(8000)
Declare @lines int
Declare @line int

DECLARE @max_nbr_cols int
DECLARE @counter int

--Drop temporary tables
if exists(select * from tempdb.information_schema.tables where table_name = '##tmp_pivot_table')
drop table ##tmp_pivot_table
if exists(select * from tempdb.information_schema.tables where table_name = '##tmp_results_table')
drop table ##tmp_results_table


--Retrieve the total number of elements in a sample
SELECT @max_nbr_cols = COUNT(*) FROM elementlines l WHERE l.sampleid = @sampleId

-- if there is no data for this section, return an empty string
IF NOT @max_nbr_cols > 0
BEGIN
set @samplesql = ''
return
END

--Dynamically build queries to create ##tmp_pivot_table and ##tmp_results_table
SET @sqlline = 'Col1 varchar(20) NULL'
SET @counter = 2
WHILE @counter <= @max_nbr_cols
BEGIN
SET @sqlline = @sqlline + ',Col' + CONVERT(varchar, @counter) + ' varchar(20) NULL'
SET @counter = @counter + 1
END

SET @sql5a = 'CREATE TABLE ##tmp_pivot_table ( ' + @sqlline + ')'
SET @sql6 = 'CREATE TABLE ##tmp_results_table (SampleId int NULL, Summary varchar(50) NULL, ' + @sqlline + ')'

EXEC(@sql5a) -- creates the ##tmp_pivot_table

--Retrieve the total number of elements in a sample
SET @lines = @max_nbr_cols

SET @sql5a = 'INSERT INTO ##tmp_pivot_table SELECT MAX(CASE lineindex WHEN 1 THEN field1 ELSE '''' END) AS Col1'
SET @sql5b = ''
SET @sql5c = ''
SET @sql5d = ''
SET @sqlline = ''
SET @counter = 2
WHILE @counter <= @max_nbr_cols
BEGIN
SET @sqlline = ',MAX(CASE lineindex WHEN ' + CONVERT(varchar, @counter) + ' THEN field1 ELSE '''' END) AS Col' + CONVERT(varchar, @counter)

if(len(@sql5a) < 7600)
SET @sql5a = @sql5a + @sqlline
else if(len(@sql5b) < 7600)
SET @sql5b = @sql5b + @sqlline
else if(len(@sql5c) < 7600)
SET @sql5c = @sql5c + @sqlline
else if(len(@sql5d) < 7600)
SET @sql5d = @sql5d + @sqlline

SET @counter = @counter +1
END


SET @sql5d = @sql5d + ' FROM (Select l.lineIndex,l.elementname as field1 From elementlines l
Where l.sampleid = ' + cast(@sampleId as varchar(50)) + ') as t'
SET @sql5d = @sql5d + ' SELECT ' + cast(@sampleId as varchar(50)) + ' as SampleId, ''Element/Wavelength'' as Summary,* FROM ##tmp_pivot_table '

-- Retrieve average of repeats
Select @sql1 = 'Select ' + cast(@sampleId as varchar(50)) + ' as SampleId, ''Avg. of Repeats:'' as ''Summary''' + char(10)
Select @sql2 = ',MAX(CASE lineindex WHEN 1 THEN field1 ELSE '''' END) AS Col1' + char(10)
Select @sql2b = ''
Select @sql2c = ''
Select @sql2d = ''
select @sqlline = ''
Select @line = 2
while(@line <= @lines)
begin
SET @sqlline = ',MAX(CASE lineindex WHEN ' + CONVERT(varchar, @line) + ' THEN field1 ELSE '''' END) AS Col' + CONVERT(varchar, @line) + char(10)

if(len(@sql2) < 7600)
select @sql2 = @sql2 + @sqlline
else if(len(@sql2b) < 7600)
select @sql2b = @sql2b + @sqlline
else if(len(@sql2c) < 7600)
select @sql2c = @sql2c + @sqlline
else if(len(@sql2d) < 7600)
select @sql2d = @sql2d + @sqlline

select @line = @line + 1
end

Select @sql3 = 'From (Select s.lineIndex,
RTRIM(s.FailFlags_IR) COLLATE database_default+char(32)+(CASE WHEN RTRIM(s.FailFlags_IR) COLLATE database_default IN (''*'', ''^'', ''?'',''Z'', ''P'', ''D'',''N'') THEN ''*****'' ELSE CONVERT(varchar,s.averageresult_ir) END) AS field1
From tbloutputmoderesultstats s
Where s.sampleid = ' + cast(@sampleId as varchar(50)) + ') as t'

Select @query2 = @sql1
Select @query2b1 = @sql2
Select @query2b2 = @sql2b
Select @query2b3 = @sql2c
Select @query2b4 = @sql2d
Select @query2b5 = @sql3

--Retrieve Standard deviation
Select @sql1 = 'Select ' + cast(@sampleId as varchar(50)) + ' as SampleId, ''Std Dev:'' as ''Summary''' + char(10)
Select @sql3 = 'From (Select s.lineIndex,
(CASE WHEN RTRIM(s.FailFlags_IR) COLLATE database_default IN (''*'', ''^'', ''?'',''Z'', ''P'', ''D'',''N'') THEN ''----'' ELSE CONVERT(varchar,s.standarddeviation_ir) END) AS field1
From tbloutputmoderesultstats s
Where s.sampleid = ' + cast(@sampleId as varchar(50)) + ') as t'

Select @query3 = char(10) + 'Union All' + char(10) + @sql1
Select @query3b1 = @sql2
Select @query3b2 = @sql2b
Select @query3b3 = @sql2c
Select @query3b4 = @sql2d
Select @query3b5 = @sql3

--Retrieve percentage RSD
Select @sql1 = 'Select ' + cast(@sampleId as varchar(50)) + ' as SampleId, ''%RSD:'' as ''Summary''' + char(10)
Select @sql3 = 'From (Select s.lineIndex,
(CASE WHEN RTRIM(s.FailFlags_IR) COLLATE database_default IN (''*'', ''^'', ''?'',''Z'', ''P'', ''D'',''N'') THEN ''----'' ELSE CONVERT(varchar,s.percentrsd_ir) END) AS field1
From tbloutputmoderesultstats s
Where s.sampleid = ' + cast(@sampleId as varchar(50)) + ') as t'

Select @query4 = char(10) + 'Union All' + char(10) + @sql1
Select @query4b1 = @sql2
Select @query4b2 = @sql2b
Select @query4b3 = @sql2c
Select @query4b4 = @sql2d
Select @query4b5 = @sql3

--Retrieve repeat information
Select @sql1 = 'Select ' + cast(@sampleId as varchar(50)) + 'AS SampleId, ''Repeat:'' + cast(exposurenumber as varchar(10)) as ''Summary''' + char(10)
Select @sql3 = 'From (Select m.lineindex,m.exposurenumber,
(CASE WHEN RTRIM(m.FailFlag_IR) COLLATE database_default IN (''*'', ''^'', ''?'',''Z'', ''P'', ''D'',''N'') THEN RTRIM(m.FailFlag_IR) COLLATE database_default+char(32)+''----'' ELSE CONVERT(varchar,sum(m.result_ir)) END) AS field1
From tbloutputmoderesults m
Where m.sampleid = ' + cast(@sampleId as varchar(50)) + ' Group By m.FailFlag_IR,m.exposurenumber,m.lineindex) as t
Group By exposurenumber'

Select @query1a = @sql5a
Select @query1b = @sql5b
Select @query1c = @sql5c
Select @query1d = @sql5d + char(10) + 'Union All' + char(10)
Select @query5a = char(10) + 'Union All' + char(10) + @sql1
Select @query5b = @sql2
Select @query5b2 = @sql2b
Select @query5b3 = @sql2c
Select @query5b4 = @sql2d
Select @query5c = @sql3

exec(@sql6) -- create the ##tmp_results_table

Insert ##tmp_results_table exec(@query1a + @query1b + @query1c + @query1d + @query2 + @query2b1 + @query2b2 + @query2b3 + @query2b4 + @query2b5 + @query3 + @query3b1 + @query3b2 + @query3b3 + @query3b4 + @query3b5 + @query4 + @query4b1 + @query4b2 + @query4b3 + @query4b4 + @query4b5 + @query5a + @query5b + @query5b2 + @query5b3 + @query5b4 + @query5c)

declare @sqlpages varchar(8000)
exec pr_GeneratePages @sqlpages out,@maxcol,@max_nbr_cols
select @samplesql = @sqlpages

-- Drop temporary tables
if exists(select * from tempdb.information_schema.tables where table_name = '##tmp_pivot_table')
drop table ##tmp_pivot_table
END

Thank you in advanced for your time

willpash

 

[mrdenny]

I'm pretty sure I'm going to have nightmares about that code. Do the schemas of your base tables change so often that you need to change your reporting procedures that often?

From this mess there's really no way to know if it can be optimized. There isn't a lot of T/SQL in there to look at. It's all being generated.

Look at the execution plans after it's been run and see where indexes are missing, or need adjusting.

195ms to create the dynamic SQL, compile it to get an execution plan and run it, isn't that bad.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[willpash]

Hi MrDenny,

Thank you for your comments, as I said I have been looking at the code over the weekend, and I thought I was going crazy!!!

The problem is that I have inherited 12 stored procedures of this type, which all generate pivot tables of data for 11 subreports. This was one of the stored procedures which was taking the longest to execute.

The base schema does not change, but the report is based on a number of parameters, for the maximum no of columns to be returned, the method, and then a multi value the sample data id.

Those timings of 555ms represent returning 21 rows of data. where I have set the max columns to 10 and have 2 sample ids.

The report is taking in all 1 min and 15 secs, but it needs to execute within 20 secs.

If there is very little T/SQL, would I be better off just creating a view?

Thanks

willpash

 

[TysonLPrice]

Could you use table variables instead of the temp tables? You could put an index on them also by creating a primary key column. That idea came from gmmastros via a reply to a question from me one time:

"To create an index on a table variable, you can create a primary key column which is implemented through an index, so....Declare @EmployerList table(pkEmployer Int Primary Key, Blah VarChar(20), etc....)"

 

[gmmastros]

Someone was listening.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

Thanks George / Tyson,

I was looking for info on that and it's helped me a treat!

Rgds,

M.