python - PYMSSQL/SQL Server 2014 : is there a limit to the length of a list of PKs to use as a subquery?

Question

我已经实现了一个 python 脚本，以便使用以下方案将数百万个文档(由 .NET Web 应用程序生成，所有内容都放入一个目录中)划分到子文件夹中:年/月/批处理，因为所有这些文档来自的任务最初是分成批处理的。我的 python 脚本对 SQL Server 2014 执行查询，其中包含每个文档所需的所有数据，特别是创建它的月份和年份。然后它使用 shutil 模块移动 pdf。因此，我首先执行第一个查询来获取给定月份和年份的批处理列表:

queryBatches = '''SELECT DISTINCT IDBATCH
                FROM [DBNAME].[dbo].[WORKS]
                WHERE YEAR(DATETIMEWORK)={} AND MONTH(DATETIMEWORK)={}'''.format(year, month)

然后我执行:

for batch in batches:
  query = '''SELECT IDWORK, IDBATCH, NAMEDOCUMENT
             FROM [DBNAME].[dbo].[WORKS]
             WHERE NAMEDOCUMENTI IS NOT NULL and
                   NAMEDOCUMENT not like '/%/%/%/%.pdf' and 
                   YEAR(DATETIMEWORK)={} and 
                   MONTH(DATETIMEWORK)={} and 
                   IDBATCH={}'''.format(year,month,batch[0])

根据 PYMSSQL 使用文档，其记录被收集到游标中。所以我继续:

IDWorksUpdate = []
row = cursor.fetchone()
while row:

  if moveDocument(...):
    IDWorksUpdate.append(row[0])
  row = cursor.fetchone()

最后，当周期结束时，在 IDWorksUpdate 中，我拥有 WORKS 的所有 PK，其文档已成功正确移动到子文件夹中。因此，我关闭光标和连接，然后实例化新的。最后我执行:

subquery = '('+', '.join(str(x) for x in IDWorksUpdate)+')'
query = '''UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT = \'/{}/{}/{}/\'+NAMEDOCUMENT WHERE IDWORK IN {}'''.format(year,month,idbatch,subquery)

newConn = pymssql.connect(server='localhost', database='DBNAME')
newCursor = newConn.cursor()

try:
    newCursor.execute(query)
    newConn.commit()
except:
    newConn.rollback()
    log.write('Error on updating documents names in database of works {}/{} of batch {}'.format(year,month,idbatch))
finally:
    newCursor.close()
    del newCursor
    newConn.close() 

今天早上，我发现只有几个批处理的更新查询在数据库中执行失败，即使文档已正确移动到子目录中也是如此。该批处理有超过 55000 个文档需要移动，因此 IDWorksUpdate 是否会溢出并导致最终更新查询的创建失败？我认为 55000 并不是一个很大的整数列表。问题是，在 PYMSSQL 中，我们不能同时对同一数据库有多个连接/游标，因此我无法在移动相应文件时更新记录。所以我想创建一个文档被正确移动的作品的 PK 列表，最后用新的连接/光标更新它们。可能发生了什么？我做错了吗？

更新

我刚刚编写了一个简单的脚本来重现将要执行的查询以更新记录，这是我从 SQL Server 收到的错误:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

这是查询:

UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT = '/2016/12/1484/'+NAMEDOCUMENT WHERE IDWORK IN (list of 55157 PKs)

事实是该表非常大(大约有 1400 万条记录)。但我需要 PK 列表，因为只有文档已正确处理和移动的任务才能更新。我不能简单地运行:

UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT = '/2016/12/1484/'+NAMEDOCUMENT WHERE YEAR(DATETIMEWORK)=2016 and 
MONTH(DATETIMEWORK)=12 and IDBATCH=1484

这是因为当我们的服务器受到加密锁攻击时，我必须仅处理和移动仍然存在的文档，等待其他文档被释放。我应该将这些字符串拆分为子列表吗？怎么办？

更新2

似乎以下可能是一个解决方案:我将 PK 列表分成 10000 个 block (一个完全实验的数字)，然后执行与 block 一样多的查询，每个查询都有一个 block 作为子查询。

def updateDB(listID, y, m, b, log):

newConn = pymssql.connect(server='localhost', database='DBNAME')
newCursor = newConn.cursor()

if len(listID) <= 10000:

    subquery = '('+', '.join(str(x) for x in listID)+')'
    query = '''UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT= \'/{}/{}/{}/\'+NAMEDOCUMENT WHERE IDWORKIN {}'''.format(y,m,b,subquery)

    try:
        newCursor.execute(query)
        newConn.commit()
    except:
        newConn.rollback()
        log.write('...')
        log.write('\n\n')
    finally:
        newCursor.close()
        del newCursor
        newConn.close()   
else:
    chunksPK = [listID[i:i + 10000] for i in xrange(0, len(listID), 10000)]

    for sublistPK in chunksPK:

        subquery = '('+', '.join(str(x) for x in sublistPK)+')'
        query = '''UPDATE [DBNAME].[dbo].[WORKS] SET NAMEDOCUMENT= \'/{}/{}/{}/\'+NAMEDOCUMENT WHERE IDWORK IN {}'''.format(y,m,b,subquery)

        try:
            newCursor.execute(query)
            newConn.commit()
        except:
            newConn.rollback()
            log.write('Could not execute partial {}'.format(query))
            log.write('\n\n')

    newCursor.close()
    del newCursor
    newConn.close()

这可能是一个好的/安全的解决方案吗？

Answer 1

正如MSDN文档中所述

IN (Transact-SQL)

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

(您引用的错误消息是错误 8623。)

将 IN 列表值放入临时表中，然后使用

... WHERE IDWORK IN (SELECT keyValue FROM #inListTable)

让我觉得比你描述的“分块”方法更直接。