代码之家 › 专栏 › 技术社区 › Chau Chee Yang

如何根据选定的值集有效地从数据库表中选择行

implementation performance sql

Chau Chee Yang · 技术社区 · 16 年前

我有一个100万行的事务表。这个表有一个字段名“Code”来保存客户ID。大约有10000个不同的客户代码。

我在operator first中使用,它只适用于少数客户:

SELECT * FROM TRANS_TABLE WHERE CODE IN ('...', '...', '...')

另一种方法是创建一个只有一个代码字段的临时表,并使用INSERT语句将选定的客户代码注入临时表。我可以用

SELECT A.* FROM TRANS_TABLE A INNER JOIN TEMP B ON (A.CODE=B.CODE)

你知道处理这种情况的更好办法吗?

4 回复 | 直到 16 年前

Mr. Shiny and New å®å® 16 年前

您可以生成SQL,例如

SELECT * FROM TRANS_TABLE WHERE CODE IN (?,?,?,?,?,?,?,?,?,?,?)

在循环中重复使用它,直到你加载了所有你需要的ID。优点是,如果您只需要几个id,那么DB就不需要解析所有in子句。如果许多ID是一个罕见的情况,那么性能的影响可能并不重要。如果您不担心SQL解析缓存,那么可以将in子句的大小限制为DB的实际限制,这样有时就不需要循环,有时就需要循环。

Aaronaught 16 年前

如果使用SQLServer2008,最快的方法通常是使用 Table-Valued Parameter (电视节目):

CREATE TYPE CodeTable AS TABLE
(
    Code int NOT NULL PRIMARY KEY
)

DECLARE @Codes AS CodeTable
INSERT @Codes (Code) VALUES (1)
INSERT @Codes (Code) VALUES (2)
INSERT @Codes (Code) VALUES (3)
-- Snip codes

SELECT t.*
FROM @Codes c
INNER JOIN Trans_Table t
    ON t.Code = c.Code

使用ADO.NET,您可以填充TVP directly from your code ,所以不需要生成所有这些 INSERT 声明-只需传递一个 DataTable

CREATE PROCEDURE GetTransactions
    @Codes CodeTable READONLY
AS

SELECT t.*
FROM @Codes c
INNER JOIN Trans_Table t
    ON t.Code = c.Code

... 把车开进去就行了 @Codes 值作为参数。

Wikser 16 年前

因为你必须通过身份证,以某种方式,应该是最快的方式。

在in子句中包含大量值(数千个)可能会消耗资源并返回错误8623或8632。若要解决此问题,请将in列表中的项存储在表中。

如果您真的超过了IN的限制并创建了一个临时表,那么只要您一次插入值(当然不是数千个查询),表的创建就不会是一个大问题。选择开销最小的方法,如下面提到的方法之一:

http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/

当然,如果您的id中有一些静态模式,您可以通过它来选择(比如在SPs或udf中)。如果你从你的数据库中取出成千上万的ID,而不是来回传递它们,你可以存储它们或者使用子查询。。。

badabadabada 16 年前

也许您可以将客户代码传递给以逗号分隔的存储过程,并使用此处提到的split sql函数: http://www.devx.com/tips/Tip/20009 .

CREATE PROCEDURE prc_dosomething (
    @CustomerCodes varchar(MAX)
)
AS

DECLARE @customercodetable table(code varchar(10)) -- or whatever length you require.
SET @customercodetable = UTILfn_Split(@CustomerCodes) -- see the article above for the split function.

-- do some magic stuff here :).