“数组参数"TSQL [英] "Array parameter" TSQL

问题描述

我有一个包含呼叫数据记录的表，每个呼叫都带有呼叫数据，其中一个字段是我们在查询数据库时使用的 CallerId.

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.

我们用下面的TSQL来模拟一个数组参数，这是要走的路还是走的路?

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

我正在使用表变量来存储我从中拆分的临时表和 |我们作为参数传入的分隔字符串.SplitDelimitedVarChar SQL 函数分割一个 VarChar 并返回一个表变量.时间和日期值存储为整数.

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.

WHERE 子句中使用的所有字段都已编入索引.

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.

因为我显然不是 SQL 大师，所以我觉得可能有人可以告诉我，我是不是在 SQL 方面真的很差，或者只是有什么地方错了?任何建议表示赞赏

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

提前致谢约翰

推荐答案

看看 SQL Server 中的数组和列表 作者:Erland Sommarskog

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

这篇关于“数组参数"TSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！