sqlperformance—sql表达式中非常庞大的in子句

tvokkenx 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(298)

假设我有一个包含一百万条记录的产品表（是的，我有一百万个产品！）。
此产品表有一个引用字符串字段。
有些人想导出除某些产品以外的所有产品的csv文件。
我们知道要排除的产品的参考号。它们存储在csv文件中。
以下是我构建的查询：

SELECT ... FROM products WHERE reference NOT IN ('ref1','ref2','.....')

所有的工作都很好，只有不到100个参考排除。
如果我有超过10000个参考文献要排除，我该怎么办？sql查询非常大。
我试过mysql，postgre和sqlserver。同样的问题。
谢谢

sql sqlperformance

来源：https://stackoverflow.com/questions/62215252/very-huge-in-clause-in-sql-expression

3条答案

按热度按时间

rsaldnfx1#

可以在sql server上使用表值参数。
查询如下所示：

SELECT *
FROM products AS P
LEFT JOIN @exludedProducts AS EP ON EP.reference = P.reference
WHERE P.reference IS NULL

您需要声明表值类型，如（检查类型以匹配您的表）：

CREATE TYPE dbo.ProductReferenceTvp AS TABLE 
(
    reference VARCHAR(10) NOT NULL
)

您的.net代码可能如下所示：

public void GetProducts(IEnumerable<string> excludedProducts)
{
    StringBuilder sb = new StringBuilder();

    sb.AppendLine(" SELECT * ");
    sb.AppendLine(" FROM products AS P ");
    sb.AppendLine(" LEFT JOIN @exludedProducts AS EP ON EP.reference = P.reference ");
    sb.AppendLine(" WHERE P.reference IS NULL ");

    using (var cn = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(sb.ToString(), cn))
        {
            var pExcludedProducts = GetTvp("@exludedProducts", excludedProducts.Distinct());
            cmd.Parameters.Add(pExcludedProducts);

            DataSet ds = new DataSet();
            new SqlDataAdapter(cmd).Fill(ds);
            Print(ds);
        }
    }
}

private SqlParameter GetTvp(string name, IEnumerable<string> excludedProducts)
{
    var dt = new DataTable();
    dt.Columns.Add("reference", typeof(String));
    foreach (var product in excludedProducts)
    {
        dt.Rows.Add(product);
    }
    var p = new SqlParameter(name, dt);
    p.SqlDbType = SqlDbType.Structured;
    p.TypeName = "dbo.ProductReferenceTvp";
    return p;
}

赞(0）回复(0）举报 2021-07-26

gxwragnw2#

将csv文件加载到表中，使用 reference 作为主键。
然后使用 not exists :

SELECT ...
FROM products p
WHERE NOT EXISTS (SELECT 1
                  FROM csv_table c
                  WHERE c.reference = p.reference
                 );

当然，创建csv表的逻辑取决于数据库。但是，任何可以使用索引的数据库都应该能够对此进行优化。

赞(0）回复(0）举报 2021-07-26

zbsbpyhn3#

如果您有可能对表格进行更改；您可以有一个额外的列作为exclude（位字段），如果需要被排除，则更新为1，如果不需要，则保留为0。在运行查询之前，将排除数据存储在表中并更新主表，而不是维护csv文件。
然后在查询时，将该列用作筛选器。

赞(0）回复(0）举报 2021-07-26