Prev: default directory change for full text search
Next: time out
From: Juwe17 on 10 Jun 2010 11:37
I created 2 database views. Each of them uses 256 database tables.
From every database table only one column is used in the select list of the
view. Every database table has one column which is used for the join.
Each database table has 5 rows. The database tables have not any foreign
keys and indexes.
Both views works fine. When I use the database views in a query like this:
SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id
I should get 5 rows with 512 columns in the resultset.

But I get the following error from the SQL-Server (after about 5 minutes):
Msg 8621, Level 17, State 2, Line 1.
The query processor ran out of stack space during query optimization. Please
simplify the query.

I use MS SQL-Server 2008 (10.0.2531). I get the same error in MS SQL-Server
2005 SP3 (9.0.4053).
The SQL-Server has 16 GByte RAM.

My question: Is it a wrong message? Is there a limitation of the SQL-Server?
Is it possible to increase the size of stack space? And how?
The answer is important for my design.

Thank you for help.




From: Juwe17 on 10 Jun 2010 11:59
Sorry, the error code is:
Meldung 8621, Ebene 17, Status 1, Zeile 1
Msg 8621, Level 17, State 1, Line 1.

"Juwe17" wrote:

> I created 2 database views. Each of them uses 256 database tables.
> From every database table only one column is used in the select list of the
> view. Every database table has one column which is used for the join.
> Each database table has 5 rows. The database tables have not any foreign
> keys and indexes.
> Both views works fine. When I use the database views in a query like this:
> SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id
> I should get 5 rows with 512 columns in the resultset.
>
> But I get the following error from the SQL-Server (after about 5 minutes):
> Msg 8621, Level 17, State 2, Line 1.
> The query processor ran out of stack space during query optimization. Please
> simplify the query.
>
> I use MS SQL-Server 2008 (10.0.2531). I get the same error in MS SQL-Server
> 2005 SP3 (9.0.4053).
> The SQL-Server has 16 GByte RAM.
>
> My question: Is it a wrong message? Is there a limitation of the SQL-Server?
> Is it possible to increase the size of stack space? And how?
> The answer is important for my design.
>
> Thank you for help.
>
>
>
>
From: Erland Sommarskog on 10 Jun 2010 16:40
Juwe17 (Juwe17(a)discussions.microsoft.com) writes:
> I created 2 database views. Each of them uses 256 database tables. From
> every database table only one column is used in the select list of the
> view. Every database table has one column which is used for the join.
> Each database table has 5 rows. The database tables have not any foreign
> keys and indexes.
> Both views works fine. When I use the database views in a query like this:
> SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id
> I should get 5 rows with 512 columns in the resultset.
>
> But I get the following error from the SQL-Server (after about 5 minutes):
> Msg 8621, Level 17, State 2, Line 1.
> The query processor ran out of stack space during query optimization.
> Please simplify the query.
>
> I use MS SQL-Server 2008 (10.0.2531). I get the same error in MS
> SQL-Server 2005 SP3 (9.0.4053).
> The SQL-Server has 16 GByte RAM.

Is this 32-bit or 64-bit SQL Server? If this is 32-bit SQL Server, you
are constraint to the lower 2GB, and maybe also to the mysterious
area known as memtoleave. It may help to add the startup option -g 512
to SQL Server. Even better is to go 64-bit.

> My question: Is it a wrong message? Is there a limitation of the
> SQL-Server? Is it possible to increase the size of stack space? And
> how?
> The answer is important for my design.

Hm, while the theoretical max of number of tables in a single query
in SQL Server is 1024, if memory serves, I'm not sure that this is a
very good way to go. What are you trying to achieve? A partitioned
view?


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: obiron via SQLMonster.com on 11 Jun 2010 08:33
not really answering the question but I cannot imagine an entity diagram that
would require parent-child relationships across 256 tables.

Rather than get hung up on why this view doesn't work, I would be asking for
the data structure to be reviewed.

I suspect that a large number of your 256 tables are holding the same
information but are partitioned by something that is common to all the tables
(e.g. Week) and that the number of tables could be substantially reduced by
adding an additional key column.


Juwe17 wrote:
>I created 2 database views. Each of them uses 256 database tables.
>From every database table only one column is used in the select list of the
>view. Every database table has one column which is used for the join.
>Each database table has 5 rows. The database tables have not any foreign
>keys and indexes.
>Both views works fine. When I use the database views in a query like this:
>SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id
>I should get 5 rows with 512 columns in the resultset.
>
>But I get the following error from the SQL-Server (after about 5 minutes):
>Msg 8621, Level 17, State 2, Line 1.
>The query processor ran out of stack space during query optimization. Please
>simplify the query.
>
>I use MS SQL-Server 2008 (10.0.2531). I get the same error in MS SQL-Server
>2005 SP3 (9.0.4053).
>The SQL-Server has 16 GByte RAM.
>
>My question: Is it a wrong message? Is there a limitation of the SQL-Server?
>Is it possible to increase the size of stack space? And how?
>The answer is important for my design.
>
>Thank you for help.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1

 | 
Pages: 1
Prev: default directory change for full text search
Next: time out