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 Monday, August 13, 2007 11:12 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:10 PM
Points: 49, Visits: 279
Hello,
I have been using all these solutions and finally settled for a CLR function that I found on the Internet.
It works great for me.
Regards,
Philippe

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, TableDefinition = "SQLStr nvarchar(4000)", FillRowMethodName = "FillSplitCommaDelimitedStringToStr")]
public static System.Collections.IEnumerable clrfn_SplitCommaDelimitedString(SqlString str)
{
string x = str.Value;
if (!string.IsNullOrEmpty(x))
{
return x.Split(',');
}
else
{
return null;
}
}
private static void FillSplitCommaDelimitedStringToStr(object obj, out SqlString str)
{
if (obj != null)
str = (String)(obj);
else
str = String.Empty;
}
};

''I call it like that
Where (@PTI2 = '##' or b.PTI2_Cd in (select ltrim(SQLstr) from ONGlobals.dbo.clrfn_SplitCommaDelimitedString(@Pti2) ) )
''The ## is used for cases where we want all values.


BI Guy
Post #390271
Posted Tuesday, August 14, 2007 7:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 1, 2010 7:01 PM
Points: 59, Visits: 9

If you want to use an IN try this?

WHERE RealTableColumn IN (SELECT TempTableColumn FROM TempTable)

EXISTS can be fast too but not sure in this scenario.

WHERE EXISTS (SELECT TempTableColumn FROM TempTable WHERE TempTableColumn = RealTableColumn)

However a JOIN on an indexed TempTableField should be as fast as anything in my experience.

I would normally use the IN option as it is clearer.

Post #390534
Posted Wednesday, August 15, 2007 6:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:52 AM
Points: 20, Visits: 87

If you want to avoid dynamic SQL, you could use an UDF that will convert comma-delimited string to a table-type variable, and then use IN as usual.

SELECT * FROM customers

WHERE ISNULL(@cust_id_multiple, '') = '' OR customers.cust_id IN (SELECT value FROM dbo.LIST_TO_TABLE(@cust_id_multiple))

 

Below is a sample UDF that does the trick.

--converts list of values to a temporary table. Used to avoid dynamic SQL statements when IN clause is used as filter expression
--sList: list of numeric values in comma-separated form, to be converted to table
--return value: table-type variable containing one column. The table is filled with numeric values from @sList
CREATE FUNCTION LIST_TO_TABLE (@sList AS VARCHAR(255))
RETURNS  @retTable TABLE(value VARCHAR(50))  AS 

BEGIN

 IF @sList IS NULL RETURN

 --preparing the input list, removing IN keyword and parenthesis
 SET @sList = REPLACE(@sList, 'IN(','')
 SET @sList = REPLACE(@sList, 'IN (','')
 SET @sList = REPLACE(@sList, '(','')
 SET @sList = REPLACE(@sList, ')','')
 SET @sList = LTRIM(RTRIM(@sList))


 --scrolling thru values in @sList and populating temporary table
 DECLARE @Index INT, @Delimiter CHAR(1)
 DECLARE @Result VARCHAR(255)


 SET @Delimiter = ','

 WHILE @sList <> ''
 BEGIN
  SET @Index = CHARINDEX(@Delimiter, @sList)
  IF @Index <> 0
  BEGIN
   SET @Result = LEFT(@sList, @Index - 1)
   SET @sList = SUBSTRING(@sList, @Index + 1, LEN(@sList))
  END
  ELSE
  BEGIN
   SET @Result = @sList
   SET @sList = ''
  END

  INSERT @retTable SELECT @Result
 END

 RETURN

END


 

Post #390859
Posted Sunday, August 19, 2007 11:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi Jose,

We tried to split the string into records, and insert it into a temp table yes, and it had some perfomance boost but the cost of splitting the string and inserting into a temp table cause performance to be less than using dynamic sql...

Post #391947
Posted Sunday, August 19, 2007 11:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi SDM,

It could well be that my results were affected by our table size and parameter length as it was a fairly large table and many parameter options (strings) selected. I'm sure a smaller table and less parameters would have different results. We decided to stick with the dynamics SQL as to advantages out weigh the disadvantages.

In the query plan computations stood out most (as expected) and the join was very in-efficient, which is what I expected as well.

Post #391950
Posted Sunday, August 19, 2007 11:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi Jon,

Interesting idea. Have you tested this against large tables? This would result in a table scan which might have a big impact on performance...

Post #391951
Posted Sunday, August 19, 2007 11:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi Jim,

Your approach is the same as what we did, and found it to be highly inefficient, have you done some perfonace testing on big tables and wide range of parameter selections?

Post #391952
Posted Sunday, August 19, 2007 11:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi dph,

Have you done performance testing on this with large tables? I try to avoid "LIKE" statements as much as possible as they have a tendancy to cause table scans. I would be interested to see your performance testing results.

Post #391954
Posted Sunday, August 19, 2007 11:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi sebastian,

I don't normally use indexes on temp tables as building the index is often more costly than not using it. In this case we did test it with, and without indexes and the overall performance was better without the index.

My rule of thumb for indexes is to only create then if they will be used more than once, then the cost of building the index will be less than scanning the table. With temp tables this is mostly true.

Post #391955
Posted Sunday, August 19, 2007 11:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 21, 2012 8:34 AM
Points: 15, Visits: 13

Hi Philippe,

Thanks for the post. This was the one option we considered but did not test as we were running on a SQL2000 database and I had never worked with CLR on SQL2000. I would imagine that this would be a good solution as C\C++\C# is normally better as string manipulation than SQL.

Post #391956
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse