代码之家 › 专栏 › 技术社区 › Floyd user1988754

SQL查询的大参数列表

sql-server-2000 sql-server-2008 sql-server c#

Floyd user1988754 · 技术社区 · 14 年前

我有一大串用于SQL查询的int参数:

update mytable set col='xyz'
where id in (/* thousands of ints */)

我也可以在SQL Server 2008上运行这个查询。

最好的办法是什么。

编辑:

身份证列表来自 C类# 程序。不是从另一张桌子上。

7 回复 | 直到 14 年前

D'Arcy Rittich 14 年前

可以将整数插入临时表,然后按如下方式查询:

update mytable m set col='xyz' 
where exists (select * from #MyTempTable where id = m.id)

Brock Adams 14 年前

使用SQL 2000的另一种方法是使用XML。

使程序/应用程序的int格式如下:

'<root><TMP J="111"/><TMP J="222"/><TMP J="333"/></root>'

.
然后创建以下存储过程:

CREATE PROCEDURE UpDateIntsFromXML (
    @sXML TEXT
)
AS
    DECLARE @iDoc INT
    EXEC    sp_xml_preparedocument @iDoc OUTPUT, @sXML

    UPDATE  YourTable
    SET     YourColumn = 'fixed value'
    FROM    OPENXML (@iDoc, '/root/TMP', 1) WITH (J INT) AS X
    WHERE   X.J = YourTable.IntColumn

    EXEC    sp_xml_removedocument @iDoc
RETURN

然后应用程序可以调用该SP,传递一个潜在的巨大文本/XML块。

请注意 root TMP ,和 J 都是区分大小写的。

Floyd user1988754 14 年前

SQL Server 2008: Table Valued Parameters

100000个ID需要14-20秒,1000个ID需要大约140毫秒。

sql = @"
  update MyTable
    set Col1 = 1
    where ID in (select * from @ids)
  ";
sqlCmd = new SqlCommand {Connection = _sqlConn, CommandText = sql};

//Create a DataTable with one Column("id") and all ids as DataRows
DataTable listOfLeadIDs = new DataTable();
listOfIDs.Columns.Add("id", typeof(int));
Ids.ToList<string>().ForEach(x => listOfIDs.Rows.Add(new object[] { int.Parse(x) }));

//Bind this DataTable to the Command-object
// Node: "IntTable" is an User-Defined-Table-Typ (new feature with SQL-2008)
sqlCmd.Parameters.Add(
  new System.Data.SqlClient.SqlParameter("@ids", listOfIDs) { 
    TypeName = "IntTable" 
  });

//Execute the Query
sqlCmd.ExecuteNonQuery();

CREATE TYPE [dbo].[IntTable] AS TABLE(
    [id] [int] NULL
)
GO

Brad 14 年前

不惜一切代价, 避免 IN my backup

相反,使用 EXISTS

UPDATE myTable
SET col = 'newValue'
FROM myTable 
WHERE EXISTS (
      SELECT * 
      FROM @myTempTable temp
      WHERE myTable.ID = temp.ID)

Andy 14 年前

将数据分成较小的组,并执行多个更新查询。

没有理由使用临时表,因为您从数据库外部检索数据,所以无法避免它向数据库的传输。

Pasi Savolainen 14 年前

BETWEEN 对。

但在这种情况下,只需将这些int组成一个字符串,并将其作为单个 varchar(max)

-2

David Parks 14 年前

我想您可能想创建一个基于内存的临时表,其中包含一个索引。假设要查询的表很大,则不希望对5000个匹配项的每一行进行表扫描比较。您希望在两个索引的帮助下进行联接。

CREATE TEMPORARY TABLE IF NOT EXISTS inputlist
(i INT PRIMARY KEY) ENGINE = MEMORY;

INSERT INTO inputlist (i) VALUES (1),(2),(3),(1000),(2000),(5000);

SELECT * FROM your_table JOIN inputlist ON your_table.intvalues = inputlist.i;

DROP TEMPORARY TABLE inputlist;

基于MySQL的SQL,请参见:
http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/insert.html
http://dev.mysql.com/doc/refman/5.1/en/create-table.html