"Array parameter" TSQL

Question

Hi.

I have a table with call data records one for each call with call data and one of the fields is the CallerId which we use when quering the DB.

We use the following TSQL to simulate an array parameter, is this the way to go or are we way off?

ALTER PROCEDURE [dbo].[spStudio_Get_Smdr]
    @beginTime INT,
    @endTime INT,
    @subscribers VARCHAR(MAX) = NULL,
    @exchanges VARCHAR(MAX) = '1:',
    @beginDateValue int, 
    @endDateValue int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @exch TABLE(Item Varchar(50)) 
    INSERT INTO @exch
    SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item


    DECLARE @subs TABLE(Item Varchar(19))
    INSERT INTO @subs
    SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item

    SELECT
      ,[Level]
      ,[Timestamp]
      ,[EndYear]
      ,[EndDate]
      ,[EndTime]
      ,[CallingNumber]
      ,[DialledNumber]
      ..more fields between
      ,[DateValue]
      ,[TimeValue]
  FROM [SmdrFormat] AS S
    WHERE
     (S.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
    AND
     (S.[TimeValue] BETWEEN @beginTime AND @endTime)
    AND
     EXISTS(SELECT [Item] FROM @exch WHERE [Item] = S.[Level])
    AND
        (@subscribers IS NULL OR (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = S.[CallingNumber]
              OR [Item] = S.[DialledNumber])))

END

I am using a table variable to store the temp table that I split from and | delimited string that we pass in as a parameter. The SplitDelimitedVarChar SQL Function slits a VarChar and returns a Table variable. The time and datevalues are stored as ints.

All fields used in the WHERE clause is indexed.

This works fine when the delimited string parameter is short but when it is becoming big (upp to several hundred strings delimited by |) it takes quite a long time to execute the query.

Since I apperently is no SQL guru I feel that there is probably someone who can tell me if I am really bad att SQL or just got some part wrong? Any suggestion is appreciated

Thanks in advance Johan

Answer 1

Take a look at Arrays and Lists in SQL Server by Erland Sommarskog

Answer 2

In 2008, use table valued parameters

Answer 3

here my try at refactoring it:

ALTER PROCEDURE [dbo].[spStudio_Get_Smdr]
    @beginTime INT,
    @endTime INT,
    @subscribers VARCHAR(MAX) = NULL,
    @exchanges VARCHAR(MAX) = '1:',
    @beginDateValue int, 
    @endDateValue int
AS
SET NOCOUNT ON;


IF @subscribers IS NULL
BEGIN
    SELECT
        [Level]
            ,[Timestamp]
            ,[EndYear]
            ,[EndDate]
            ,[EndTime]
            ,[CallingNumber]
            ,[DialledNumber]
            --..more fields between
            ,[DateValue]
            ,[TimeValue]
        FROM [SmdrFormat] AS S
        WHERE (S.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
            AND (S.[TimeValue] BETWEEN @beginTime AND @endTime)
            AND EXISTS (SELECT [Item] FROM [SplitDelimitedVarChar] (@exchanges, '|') WHERE S.[Level]=[Item])

END
ELSE
BEGIN

    SELECT
        [Level]
            ,[Timestamp]
            ,[EndYear]
            ,[EndDate]
            ,[EndTime]
            ,[CallingNumber]
            ,[DialledNumber]
            --..more fields between
            ,[DateValue]
            ,[TimeValue]
        FROM [SmdrFormat] AS S
        WHERE (S.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
            AND (S.[TimeValue] BETWEEN @beginTime AND @endTime)
            AND EXISTS (SELECT [Item] FROM [SplitDelimitedVarChar] (@exchanges, '|') WHERE S.[Level]=[Item])
            AND EXISTS (SELECT [Item] FROM [SplitDelimitedVarChar] (@subscribers, '|') WHERE S.[Level]=[Item] OR S.[DialledNumber]=u2.[Item])
END

RETURN 0
GO

ALSO, Make sure your split function is the fastest it can be, see my code here