Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Multi-Select Parameters for Reporting Services Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2007 12:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/ADavel/3138.asp
Post #386759
Posted Monday, August 13, 2007 1:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 09, 2009 1:43 AM
Points: 1, Visits: 4
Have you tried to have it stored in a table with that function of yours and do a select statement but instead of join using @parameter in (select parameter from @table) to see if that boost the performance?

And could you publish de code of said function?

Thanks in advance
Post #390067
Posted Monday, August 13, 2007 2:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 07, 2014 1:54 AM
Points: 363, Visits: 355

Adriaan,

I'm rather surprised at the blow-out to ten minutes for that basic shred-the-tokenised-string-to-a-table approach.  I've done this several times as a way of dealing with SSRS multi-select, and found it fine for performance (although really it's a bit crude and hack-y).

Was there anything obvious in the  query plan (of the porly performing query) to indicate why this was such a bad thing to do? 

 




Post #390069
Posted Monday, August 13, 2007 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 02, 2009 11:10 AM
Points: 5, Visits: 12

Thanks for the post Adriaan. It would be great if you could paste some sample code here!

Nick

Post #390074
Posted Monday, August 13, 2007 3:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 5:02 AM
Points: 530, Visits: 945

no need for dynamic SQL, just use something like this:

WHERE charindex(',' + field1 + ',',',' + @param1 + ',') > 0

 

... where @param1 is a string of comma-delimited values.

Post #390081
Posted Monday, August 13, 2007 3:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 01, 2013 10:44 PM
Points: 272, Visits: 143
If you use parameterized dynamic sql in the stored procedure, it shouldn't differ from the query within the report.

What I'd try is dump the SSRS parameters, do it with my own code, and pass an xml full of parameters (sql 2005).

Post #390082
Posted Monday, August 13, 2007 6:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 10:29 AM
Points: 6, Visits: 135
I would love to see a code example from Adriaan's article as well.
Post #390105
Posted Monday, August 13, 2007 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 25, 2012 8:14 AM
Points: 6, Visits: 91
i approach it a little differently. I use a function to split a parameter value into its different pieces

I also provide an All option with -1 being the value passed to the stored procedure.


my stored procedure has this line:

AND (s.ORDERCODE IN (SELECT Item FROM dbo.Split(@OrderCode,',')) OR '-1' IN(@OrderCode))

and function Split looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split]
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS

BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, ''' ''', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END





Post #390112
Posted Monday, August 13, 2007 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2008 1:06 PM
Points: 1, Visits: 2

We faced this problem a while back at our company as well.  And one of our DBAs came up with the following solution:

WHERE (@CommaDelimtedList + ',' LIKE '%'+ TableColumnToCompareTo + ',%' OR @CommaDelimtedList = '')

Just thought I'd share with everyone how we handle this situation.

Post #390144
Posted Monday, August 13, 2007 8:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 03, 2013 3:53 PM
Points: 22, Visits: 85

Adriaan,

When you create the temp table, was included a index for the join column ?, the performance between a table without index vs an indexes table in this case can be very big.

Example: The customer table have the next structure:

create table customer (
  id         int primary key
, state    int
, LName   varchar(100)
, rname    varchar(100))

 

and supose that table have an index on the state column
create index IDX_Customer_State on Customer (state)

so, we temp table have the next code

declare @TempJoinTable (IdState    int   primary key) //note the primary key, we can index a @temptable like anyone

the next step is to populate the temp table with this code

-- the @FilterValue contain the filter values with comma

DECLARE @posicion     INT
DECLATE @Value        VARCHAR(100)

WHILE (Len(@FilterValue))
BEGIN

     SET @Posicion = PATINDEX('%,%',@FilterValue)
     IF (@Posicion = 0)
     BEGIN
          SET @Value = @FilterValue
          SET @FilterValue = ''
     END
     ELSE
     BEGIN
          SET @Value = SUBSTRING(@FilterValue,1,@Posicion-1)
          SET @FilterValue = SUBSTRING(@FilerValue,@Posicion+1,LEN(@FilterValue)
     END

     INSERT INTO @TempJoinTable VALUES (@Value)

END

finally, just join the tables and compare the execution time

SELECT *
FROM customer CUS
     INNER JOIN @TempJoinTable TT
         ON CUS.State = TT.IdState   

 



Sebastián Rodríguez R.
Licenciado en Informática de Gestión
sebas_rod@hotmail.com
(569) - 98626471
Post #390160
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse