please advise: very long select query 
Author Message
 please advise: very long select query

Hi,

I am trying to execute this SQL statement on our
Sybase 11.5.1.1 on Solaris 2.6 from a SybPerl-Script:

  select CAANR, REVISION, BLOCK, RESPMHO, NAME1,
  MAIL1, NAME2, MAIL2 from CONTACTS where (
       (CAANR = 'CAAZ 1071193' and REVISION = 'R3A06')
    or (CAANR = 'CAA  1172517' and REVISION = 'R1A01')

  /* 1300 similar lines skipped.... */

    or (CAANR = 'CAAZ 1074267' and REVISION = 'R1A02')
  )

And get the following error message:

  Msg 3626, Level 17, State 1: Line 1:
  The transaction was aborted because it used too
  much stack space. Either use sp_configure to increase
  the stack size, or break the query into smaller pieces.
  spid: 18, suid: 95, hostname: XXX, application name: isql

If I use just 1020 lines for testing, everything works.
What would you do? Split this SQL statement or better
call sp_configure? If call sp_configure, then what is its
argument then? Will it affect the database noticable?

Thank you
Alex



Tue, 21 Jan 2003 03:00:00 GMT
 please advise: very long select query

Hi,

I am trying to execute this SQL statement on our
Sybase 11.5.1.1 on Solaris 2.6 from a SybPerl-Script:

  select CAANR, REVISION, BLOCK, RESPMHO, NAME1,
  MAIL1, NAME2, MAIL2 from CONTACTS where (
       (CAANR = 'CAAZ 1071193' and REVISION = 'R3A06')
    or (CAANR = 'CAA  1172517' and REVISION = 'R1A01')

  /* 1300 similar lines skipped.... */

    or (CAANR = 'CAAZ 1074267' and REVISION = 'R1A02')
  )

And get the following error message:

  Msg 3626, Level 17, State 1: Line 1:
  The transaction was aborted because it used too
  much stack space. Either use sp_configure to increase
  the stack size, or break the query into smaller pieces.
  spid: 18, suid: 95, hostname: XXX, application name: isql

If I use just 10-20 lines for testing, everything works.
What would you do? Split this SQL statement or better
call sp_configure? If call sp_configure, then what is its
argument then? Will it affect the database noticable?

Thank you
Alex



Tue, 21 Jan 2003 03:00:00 GMT
 please advise: very long select query

Quote:
> If I use just 1020 lines for testing, everything works.
> What would you do? Split this SQL statement or better
> call sp_configure? If call sp_configure, then what is its
> argument then? Will it affect the database noticable?

Personally ... I'd put the 1300 sets of values into a table and then
write my query to join against said table.  Then your query will only be
a couple lines long.  And since the data is now in a table it'll be
easier to add, delete, or modify your data without having to re-write
your query.

(duh, Mark?)

--
Mark A. Parsons





Wed, 22 Jan 2003 03:00:00 GMT
 please advise: very long select query
ASE allocates a memory area as stack space for each user conncetion
configured. The use for this is exactly for dealing with the kind of thing
you are doing. It provides working area for the query processor to generate
query tree data structure. Apparently the size of your query statment out
grown it.
If you use sp_configure to up it, it will be up for all user connections and
if you have many many use connections configured for the server, the amoung
of memory used by stack can add up.
I think you might want to try to split the query before using sp_configure
..

Luke



Quote:
> Hi,

> I am trying to execute this SQL statement on our
> Sybase 11.5.1.1 on Solaris 2.6 from a SybPerl-Script:

>   select CAANR, REVISION, BLOCK, RESPMHO, NAME1,
>   MAIL1, NAME2, MAIL2 from CONTACTS where (
>        (CAANR = 'CAAZ 1071193' and REVISION = 'R3A06')
>     or (CAANR = 'CAA  1172517' and REVISION = 'R1A01')

>   /* 1300 similar lines skipped.... */

>     or (CAANR = 'CAAZ 1074267' and REVISION = 'R1A02')
>   )

> And get the following error message:

>   Msg 3626, Level 17, State 1: Line 1:
>   The transaction was aborted because it used too
>   much stack space. Either use sp_configure to increase
>   the stack size, or break the query into smaller pieces.
>   spid: 18, suid: 95, hostname: XXX, application name: isql

> If I use just 10-20 lines for testing, everything works.
> What would you do? Split this SQL statement or better
> call sp_configure? If call sp_configure, then what is its
> argument then? Will it affect the database noticable?

> Thank you
> Alex



Thu, 23 Jan 2003 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. how to query a long long select statements?

2. Please help, Interbase transaction detail ...how to query....any clue...any advise

3. Please Help: Long select statement for ADO

4. Please Help: Long select statement for ADO

5. Select query taking extremely long time

6. nested select query take too long

7. ODBC performance issues with SELECT queries in PSQL2000, (SP2a if it helps) LONG MESSAGE

8. Problem with long SQL query (Please help!!!)

9. Query taking too long - Please Help

10. Query taking too long - please help

11. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

12. ADVISE NEEDED: How to migrate :ADVISE NEEDED


 
Powered by phpBB® Forum Software