为什么我得到“服务器在编译期间遇到堆栈溢出”错误是SQL Server 2000 Sp4

I am trying to have around 6290 'AND' conditions in this query. I get the same for around 11945 'OR' conditions.

我试图在这个查询中有大约6290'AND'条件。我得到相同的11945'OR'条件。

Exception details: The server encountered a stack overflow during compile time. at System.Data. lClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException excepti , Boolean breakConnection)

异常详细信息:服务器在编译期间遇到堆栈溢出。在System.Data。 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException excepti,Boolean breakConnection)中的lClient.SqlConnection.OnError(SqlException异常,Boolean breakConnection)

2 个解决方案

#1

Interesting bug! The obvious question to ask you is, why? The stack overflow is because recursion is the typical way to parse an SQL statement like that, which builds up a syntax tree. Depending on what is pushed onto the stack with each recursive call, it's not surprising. Did it hurt the server? ;)

有趣的bug!问你的显而易见的问题是,为什么?堆栈溢出是因为递归是解析类似SQL语句的典型方法,它会构建语法树。根据每次递归调用推送到堆栈的内容,这并不奇怪。它伤害了服务器吗? ;)

#2

Try and optimise your AND/OR conditions.

尝试并优化您的AND / OR条件。

SELECT * FROM foo
WHERE ([fooKey] = 1 AND Year = 1995)
OR ([fooKey] = 1 AND Year = 1996)
OR ([fooKey] = 1 AND Year = 1997)
OR ([fooKey] = 1 AND Year = 1998)
OR ([fooKey] = 1 AND Year = 1999)
OR ([fooKey] = 1 AND Year = 2000)
OR ([fooKey] = 1 AND Year = 2001)
OR ([fooKey] = 1 AND Year = 2002)
... ad infinitum

becomes

SELECT * FROM fooWHERE ([fooKey] = 1 AND Year between 1995 and 2002)
union
SELECT * FROM fooWHERE ([fooKey] = 10017 AND Year = 1995)
union
SELECT * FROM fooWHERE ([fooKey] = 10018 AND Year = 1997)
... slightly less

Or go to 64 bit and try and add enough memory for this not to happen...

或者转到64位并尝试添加足够的内存,以免发生这种情况......

#1

#2