Prev: SQL Server don't use all memory
Next: SQL Server 2008 R2/Remote BLOB Storage/SharePoint 2010
From: Juwe17 on 14 Jun 2010 06:02
I use 32-bit SQL Server. But I will try 64-bit SQL Server. Thank you for the
hint.

I thought: The count of tables per SELECT statement is only limited by
available resources.
(I found this under http://msdn.microsoft.com/en-us/library/ms143432.aspx)

What I am trying to achieve?
The user of an application should be able to define dynamically his business
object. He can choose attributes of his business object. I don't know how
many attributes. He defines/chooses the data type of such an attribute. May
be he needs attributes of data type varchar(8000). This is near die limit of
bytes per row in an data table of MS SQL-Server. So it is the best to say:
One data table for one attribute.

For the application it's no problem to handle the business object. The user
can insert/update data for his business object.
Reporting is the problem. The user should find his business object in the
database. The idea is to give him parts of his business object in form of
data views. Every data view has 256 attributes/data tables (the maximum of
tables in a view).
So I have a 1-to-1-to-1....-relationship across 256 data tables in one data
view. And if user has defined more than 256 attributes I have a
1-to-1-relationship across the two data views (in worst case both views have
256 tables).


"Erland Sommarskog" wrote:

> 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: Erland Sommarskog on 15 Jun 2010 17:02
Juwe17 (Juwe17(a)discussions.microsoft.com) writes:
> What I am trying to achieve?
> The user of an application should be able to define dynamically his
> business object. He can choose attributes of his business object. I
> don't know how many attributes. He defines/chooses the data type of such
> an attribute. May be he needs attributes of data type varchar(8000).
> This is near die limit of bytes per row in an data table of MS
> SQL-Server. So it is the best to say:
> One data table for one attribute.

No, it is not.

First of all, the entire idea of a users defining their objects is
questionable. Yes, this requirement does appear in many systems, and
no matter how you do it, all solutions has it cons. But in most
cases yo� should try to avoid this. Maybe all that is needed is a
good analysis of the business requirements and a good database design.

But, OK, let's say that you need dynamic attributes and that you have
decided that you will implement this by changing the schema. Having one
table per attribute is a design that just will not scale. And there is
no need for it. Watch this:

CREATE TABLE bigstuff(
a varchar(8000) NOT NULL,
b varchar(8000) NOT NULL,
c varchar(8000) NOT NULL,
d varchar(8000) NOT NULL,
e varchar(8000) NOT NULL)
go
INSERT bigstuff(a, b, c, d, e)
VALUES (replicate('a', 8000),
replicate('d', 8000),
replicate('c', 8000),
replicate('d', 8000),
replicate('e', 8000))
go
drop table bigstuff

This runs without an error on SQL 2005 and SQL 2008. On SQL 2000 it
will fail, but even there it would be poor design. Just because a user
may add a varchar(8000) does not mean that he will and if he does, it
does not mean that he will always add 8000 bytes to it.

On SQL 2000 you would maybe need a scheme so that you can split
data over several tables, but on SQL 2005/2008, there is little reason.


--
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: Juwe17 on 16 Jun 2010 12:26
I have tried 64-bit SQL Server with 8GB RAM. The results are acceptable good.
A query that joins the two data views (each having 256 tables and each data
table having 9500 rows) takes about 3 and a half minute. This is enough for
me.
It is only for an reporting, which takes place very seldom. This much I know
about the use cases.
The views are the interface. The user picks the data with SPSS or other
tools and works there.

A good analysis of the business requirements is only possible, if I know the
user and his special business requirements. And I must know his business
requirements tomorrow.
I have simplified the problem for communicate in this forum. There are other
reasons for the decision "one data table - one attribute".

Thank you for the hint about the data type varchar in SQL 2005/2008.
I think it's similar to datatype "TEXT", which only needs 16Byte per row to
handle it?



"Erland Sommarskog" wrote:

> Juwe17 (Juwe17(a)discussions.microsoft.com) writes:
> > What I am trying to achieve?
> > The user of an application should be able to define dynamically his
> > business object. He can choose attributes of his business object. I
> > don't know how many attributes. He defines/chooses the data type of such
> > an attribute. May be he needs attributes of data type varchar(8000).
> > This is near die limit of bytes per row in an data table of MS
> > SQL-Server. So it is the best to say:
> > One data table for one attribute.
>
> No, it is not.
>
> First of all, the entire idea of a users defining their objects is
> questionable. Yes, this requirement does appear in many systems, and
> no matter how you do it, all solutions has it cons. But in most
> cases yoú should try to avoid this. Maybe all that is needed is a
> good analysis of the business requirements and a good database design.
>
> But, OK, let's say that you need dynamic attributes and that you have
> decided that you will implement this by changing the schema. Having one
> table per attribute is a design that just will not scale. And there is
> no need for it. Watch this:
>
> CREATE TABLE bigstuff(
> a varchar(8000) NOT NULL,
> b varchar(8000) NOT NULL,
> c varchar(8000) NOT NULL,
> d varchar(8000) NOT NULL,
> e varchar(8000) NOT NULL)
> go
> INSERT bigstuff(a, b, c, d, e)
> VALUES (replicate('a', 8000),
> replicate('d', 8000),
> replicate('c', 8000),
> replicate('d', 8000),
> replicate('e', 8000))
> go
> drop table bigstuff
>
> This runs without an error on SQL 2005 and SQL 2008. On SQL 2000 it
> will fail, but even there it would be poor design. Just because a user
> may add a varchar(8000) does not mean that he will and if he does, it
> does not mean that he will always add 8000 bytes to it.
>
> On SQL 2000 you would maybe need a scheme so that you can split
> data over several tables, but on SQL 2005/2008, there is little reason.
>
>
> --
> 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: Erland Sommarskog on 16 Jun 2010 17:32
Juwe17 (Juwe17(a)discussions.microsoft.com) writes:
> I have simplified the problem for communicate in this forum. There are
> other reasons for the decision "one data table - one attribute".

I'm tempted to say that whatever the reason, it is a bad decision.
It's never going to work out.

> Thank you for the hint about the data type varchar in SQL 2005/2008. I
> think it's similar to datatype "TEXT", which only needs 16Byte per row
> to handle it?

No. A varchar(8000) with all 8000 bytes used, takes up 8002 bytes
within the row. The change in SQL 2005/2008 is that a row can span
multiple pages.

As for the data type "text", please be aware of that this data type
has been deprecated, and there is all reason not to use it. SQL 2005
introduced a new data type: varchar(MAX) (and nvarchar(MAX) and
varbinary(MAX)), which just like text can store up to 2GB of data.
But you can work with these types just like the other data types, and
you don't have all those restrictions that come with text & co, nor
is there any need to use text pointers and all that.




--
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: Juwe17 on 24 Jun 2010 13:15
OK, but I can also use a table with up to 444 varchar (max) columns, each
filled with 500 characters. If I use the following tables option:
execute sp_tableoption 'my_text_table', 'large value types out of row', 'ON'

>"Erland Sommarskog" wrote:
>The change in SQL 2005/2008 is that a row can span
> multiple pages.

But it is now, perhaps back to the Middle Ages, if I need to know that
multiple pages are used. I am an application developer. Should I know which
Bit is set here and there?


 |  Next  |  Last
Pages: 1 2
Prev: SQL Server don't use all memory
Next: SQL Server 2008 R2/Remote BLOB Storage/SharePoint 2010