The server encountered a stack overflow during compile time.

12 May 2005 1:28 PM

steven.harris

Hi, Can anyone help?

Im trying to run a query and im getting an error.
"the server encountered a stack overflow during compile time."

The query is fairly large (300k) and in the form of;

SELECT * FROM Drug
WHERE ([DrugKey] = 1 AND Year = 1995)
OR ([DrugKey] = 1 AND Year = 1996)
OR ([DrugKey] = 1 AND Year = 1997)
OR ([DrugKey] = 1 AND Year = 1998)
OR ([DrugKey] = 1 AND Year = 1999)
OR ([DrugKey] = 1 AND Year = 2000)
OR ([DrugKey] = 1 AND Year = 2001)
OR ([DrugKey] = 1 AND Year = 2002)
OR ([DrugKey] = 10017 AND Year = 1995)
OR ([DrugKey] = 10018 AND Year = 1997)
....
....
....

These combinations of "WHERE DrugKey and Year" can go on and on for
several 1000 lines.

I know this is not the most efficent method but SQL seems to gulp when
you do this. Can anyone explain why? More importantly, explain how to
stop it happening, without changing the query?

TIA
Steve.

12 May 2005 1:39 PM

Alejandro Mesa

Create a select statement for each OR condition and union them using UNION
operator.

Example:

SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1995)
union
SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1996)
union
SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1997)
union
SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1998)
....

AMB

Show quote

"steven.har***@unified.co.uk" wrote:

> Hi, Can anyone help?
>
> Im trying to run a query and im getting an error.
> "the server encountered a stack overflow during compile time."
>
>
> The query is fairly large (300k) and in the form of;
>
>
> SELECT * FROM Drug
> WHERE ([DrugKey] = 1 AND Year = 1995)
> OR ([DrugKey] = 1 AND Year = 1996)
> OR ([DrugKey] = 1 AND Year = 1997)
> OR ([DrugKey] = 1 AND Year = 1998)
> OR ([DrugKey] = 1 AND Year = 1999)
> OR ([DrugKey] = 1 AND Year = 2000)
> OR ([DrugKey] = 1 AND Year = 2001)
> OR ([DrugKey] = 1 AND Year = 2002)
> OR ([DrugKey] = 10017 AND Year = 1995)
> OR ([DrugKey] = 10018 AND Year = 1997)
> ....
> ....
> ....
>
> These combinations of "WHERE DrugKey and Year" can go on and on for
> several 1000 lines.
>
> I know this is not the most efficent method but SQL seems to gulp when
> you do this. Can anyone explain why? More importantly, explain how to
> stop it happening, without changing the query?
>
> TIA
> Steve.
>
>

12 May 2005 1:46 PM

Alejandro Mesa

Also, group common conditions like:

>>SELECT * FROM Drug
>>WHERE ([DrugKey] = 1 AND Year = 1995)
>>OR ([DrugKey] = 1 AND Year = 1996)
>>OR ([DrugKey] = 1 AND Year = 1997)
>>OR ([DrugKey] = 1 AND Year = 1998)
>>OR ([DrugKey] = 1 AND Year = 1999)
>>OR ([DrugKey] = 1 AND Year = 2000)
>>OR ([DrugKey] = 1 AND Year = 2001)
>>OR ([DrugKey] = 1 AND Year = 2002)

SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year between 1995 and 2002)
union
SELECT * FROM Drug WHERE ([DrugKey] = 10017 AND Year = 1995)
union
SELECT * FROM Drug WHERE ([DrugKey] = 10018 AND Year = 1997)
....

AMB

Show quote

"Alejandro Mesa" wrote:

> Create a select statement for each OR condition and union them using UNION
> operator.
>
> Example:
>
> SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1995)
> union
> SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1996)
> union
> SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1997)
> union
> SELECT * FROM Drug WHERE ([DrugKey] = 1 AND Year = 1998)
> ...
>
>
> AMB
>
>
> "steven.har***@unified.co.uk" wrote:
>
> > Hi, Can anyone help?
> >
> > Im trying to run a query and im getting an error.
> > "the server encountered a stack overflow during compile time."
> >
> >
> > The query is fairly large (300k) and in the form of;
> >
> >
> > SELECT * FROM Drug
> > WHERE ([DrugKey] = 1 AND Year = 1995)
> > OR ([DrugKey] = 1 AND Year = 1996)
> > OR ([DrugKey] = 1 AND Year = 1997)
> > OR ([DrugKey] = 1 AND Year = 1998)
> > OR ([DrugKey] = 1 AND Year = 1999)
> > OR ([DrugKey] = 1 AND Year = 2000)
> > OR ([DrugKey] = 1 AND Year = 2001)
> > OR ([DrugKey] = 1 AND Year = 2002)
> > OR ([DrugKey] = 10017 AND Year = 1995)
> > OR ([DrugKey] = 10018 AND Year = 1997)
> > ....
> > ....
> > ....
> >
> > These combinations of "WHERE DrugKey and Year" can go on and on for
> > several 1000 lines.
> >
> > I know this is not the most efficent method but SQL seems to gulp when
> > you do this. Can anyone explain why? More importantly, explain how to
> > stop it happening, without changing the query?
> >
> > TIA
> > Steve.
> >
> >

12 May 2005 2:21 PM

samh

I tried the formatyou suggested by changing to the UNION operator.
This caused an error along the same lines

BASE EXCEPTION: System.Data.SqlClient.SqlException: Internal Query
Processor Error: The query processor ran out of stack space during
query optimization.

Again, thanks for the suggestion but is there anything else anyone can
recommend?

TIA.
Steve.

12 May 2005 1:47 PM

samh

Hi,
Thanks for the reply.

Do you know if there is a limit on the number of UNION queries?

I'm not sure this approach would help me as it appears that its more
about the size of the query than the format it's in?

Steve.

12 May 2005 1:53 PM

samh

Just for completness.

This query is auto-generated by a program I've written. The problem is
that this query can be combined with serveral other queries of
differing format. Hence, the SQL generated is not always the prettiest
and can often be large. In the majority of cases this works well but
I've a client doing a crazy query that pulls back over 80,000
conditions in the where clause. Now its the size of the query that
seems to be the problem and what SQL is objecting too.

I'm mainly interested in finding out why SQL can't cope with this?

Steve.