Max OR's and AND's in a WHERE clause

Max OR's and AND's in a WHERE clause

Post by QUE » Fri, 28 Apr 2006 06:02:02


Does anyone know the limit?

I was able to test more than 8000 ORs without any problem. However, at
around 15,000, I got stack overflow error.

AND seems to blow up much sooner.

I am looking for an official word on this - what's the limit?
Thanks in advance

-a
 
 
 

Max OR's and AND's in a WHERE clause

Post by Alexander » Fri, 28 Apr 2006 06:31:01

What do you need it for? Maybe there are ways to accomplish your task
without humongous logical predicates?

 
 
 

Max OR's and AND's in a WHERE clause

Post by Aaron Bert » Fri, 28 Apr 2006 06:47:25

> I was able to test more than 8000 ORs without any problem.

This is a ridiculously large and unreasonable number of conditions. It will
probably take half an hour just to parse the statement, never mind execute
it. I suggest stuffing your values in a table and joining (for AND) or
using EXISTS (for OR).

For some information on how to stuff the values in a table, see
http://www.yqcomputer.com/ (which also has links to other articles along
similar lines).

A
 
 
 

Max OR's and AND's in a WHERE clause

Post by QUE » Fri, 28 Apr 2006 06:54:02

I need the answer for academic purposes. Reply if you know the answer to my
question.
 
 
 

Max OR's and AND's in a WHERE clause

Post by Aaron Bert » Fri, 28 Apr 2006 10:19:43

> I need the answer for academic purposes.

Whoray fer coledge! Are they also expecting you to learn to use a hammer to
screw in a lightbulb?


The problem is probably that you're exceeding the 64k limit on a SQL command
(but it's still going to be stupidly slow even if you don't exceed it). Did
you try measuring the length of the command instead of executing it? My bet
is it starts failing when it reaches about 64k. However, your initial post
wasn't all that clear anyway. Where do you get a stack overflow error?
Directly from SQL Server? VB? Excel?
 
 
 

Max OR's and AND's in a WHERE clause

Post by Mike Hodgs » Fri, 28 Apr 2006 10:38:00

his is a multi-part message in MIME format.
I don't believe there is a hard-coded limit in the parsing engine as
such. BOL does specify a theoretical limit on the length of a string
containing SQL statements (ie. a batch size) of 65536*network packet
size, which with the default network packet size of 4096 bytes would be
256MB. Other than that I think the resources in the server (memory
mostly I would think) would have the most influence over when a session
throws an error whilst parsing a SQL statement.

However, even for academic purposes I think it's a bit of a fruitless
question because, as others have said, it's a ridiculous number of
search conditions and nobody in their right mind would ever do that in
the real world (unless they were trying to get fired I suppose).

--
*mike hodgson*
http://sqlnerd.blogspot.com



AA wrote:


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I don't believe there is a hard-coded limit in the parsing engine
as such. BOL does specify a theoretical limit on the length of a
string containing SQL statements (ie. a batch size) of 65536*network
packet size, which with the default network packet size of 4096 bytes
would be 256MB. Other than that I think the resources in the server
(memory mostly I would think) would have the most influence over when a
session throws an error whilst parsing a SQL statement.<br>
<br>
However, even for academic purposes I think it's a bit of a fruitless
question because, as others have said, it's a ridiculous number of
search conditions and nobody in their right mind would ever do that in
the real world (unless they were trying to get fired I suppose).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
AA wrote:
<blockquote cite=" XXXX@XXXXX.COM "
type="cite">
<pre wrap="">I need the answer for academic purposes. Reply if you know the answer to my
question.


"Aaron Bertrand [SQL Server MVP]" wrote:

</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">I was able to test more than 8000 ORs without any problem.
</pre>
</blockquote>
<pre wrap="">This is a ridiculously large and unreasonable number of conditions. It will
probably take half an hour just to parse the statement, never mind execute
it. I suggest stuffing your values in a table and joining (for AND) or
using EXISTS (for OR).

For some information on how to stuff the values in a table, see
<a class="moz-txt-link-freetext" href="http://www.aspfaq.com/2248">http://www.aspfaq.com/2248</a> (which also has links to other articles along
similar lines).

A




</pre>