Array Parameter TSQL

Question

I have a table with call recording data, one for each call with call data, and one of the fields is CallerId, which we use when querying the database.

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

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 use a table variable to store the temp table that I shared, and | the string we pass as a parameter. The SQL function SplitDelimitedVarChar cuts a VarChar and returns a table variable. The time and date values ​​are stored as ints.

All fields used in the WHERE clause are indexed.

This works great when the delimited string parameter is short, but when it gets large (upp up to several hundred lines separated by the | character), it takes quite a while to complete the query.

Since I am apparently not a SQL guru, I feel like there is someone who can tell me if I really feel bad about SQL or something is wrong? Any suggestion is appreciated.

Thanks in advance Johan

Answer 1

See Arrays and Lists in SQL Server by Erland Sommarskog

Answer 2

In 2008, use the parameters estimated by the table

Answer 3

here is my refactoring attempt:

 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, see my code here

Answer 4

Consider this article for a few detailed examples of how to transfer large amounts of data to a stored procedure.