Optimal storage of a lot of text records

Hi,
I have the availability of a database of 100mb. I need to store large
amounts of forum messages, the more the better.Currently every table
row has 4 small nvarchar fields, and a ntext for the body of the forum
message.
This way every row is about 4 kbyte in size. That means I am able to
store "only" 25,600 forum messages.
Is there a more optimal solution so I can put more messages in this
database?
WardWard
Do you say that a database cannot grow more than 100MB?
Is it by defenition?
As an alterrnative you store the messages at filesystem and read them when
you need
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142579469.310833.81370@.p10g2000cwp.googlegroups.com...
> Hi,
> I have the availability of a database of 100mb. I need to store large
> amounts of forum messages, the more the better.Currently every table
> row has 4 small nvarchar fields, and a ntext for the body of the forum
> message.
> This way every row is about 4 kbyte in size. That means I am able to
> store "only" 25,600 forum messages.
> Is there a more optimal solution so I can put more messages in this
> database?
> Ward
>|||Yes, it cannot grow larger. I do have around 500 mb storage space. The
downside of storing it on the filesystem is that I won't be able to use
full-text search. Or doesn't have that to be a problem?|||Ward
> downside of storing it on the filesystem is that I won't be able to use
> full-text search. Or doesn't have that to be a problem?
>
If does not relate . In fact you need a storage SAN probably to deal with
your issue as well as to allow growing your database
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142581209.270659.116510@.u72g2000cwu.googlegroups.com...
> Yes, it cannot grow larger. I do have around 500 mb storage space. The
> downside of storing it on the filesystem is that I won't be able to use
> full-text search. Or doesn't have that to be a problem?
>|||Sorry, should be IT does not relate
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OaGQ5bZSGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Ward
> If does not relate . In fact you need a storage SAN probably to deal with
> your issue as well as to allow growing your database
>
> "Ward Bekker" <w.bekker@.gmail.com> wrote in message
> news:1142581209.270659.116510@.u72g2000cwu.googlegroups.com...
>|||What is your expected database size? Perhaps you simply need more space.
ML
http://milambda.blogspot.com/|||So, you say it's possible to have full-text search on real files from
Sql Server? If so, could you eleborate?|||> So, you say it's possible to have full-text search on real files from
> Sql Server? If so, could you eleborate?
>
If the file is located in SQL Server's table
Well, you will be better of to ask the question in full-text news group, I
have played with full-text very little
Also , there is pretty good article in the BOL how to create/work with
full-text in SQL Server
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142583077.165690.319440@.p10g2000cwp.googlegroups.com...
> So, you say it's possible to have full-text search on real files from
> Sql Server? If so, could you eleborate?
>|||The nvarchar and ntext datatypes support the unicode character set, but
store 2 bytes per character instead of 1 byte per character for varchar and
text. However, switching to varchar and text would only double the capacity
to 50,000 messages; which would not be any real order of magnitude.
You can store the text of the messages in seperate text files with the
naming convention based on the primary key of the message. For example,
message id #120455 would have a related file called 00120455.txt. How you
relate the text files with the messages is an application programming issue.
From what you describe, it sounds like a 3rd party hosted database. Do you
have the option of just paying for the additional storage?
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142579469.310833.81370@.p10g2000cwp.googlegroups.com...
> Hi,
> I have the availability of a database of 100mb. I need to store large
> amounts of forum messages, the more the better.Currently every table
> row has 4 small nvarchar fields, and a ntext for the body of the forum
> message.
> This way every row is about 4 kbyte in size. That means I am able to
> store "only" 25,600 forum messages.
> Is there a more optimal solution so I can put more messages in this
> database?
> Ward
>|||JT,
Yes, it's a hosted database. I can pay for additional storage, but I
want to first make sure that I have a optimal SQL Server config so i
get more bang per buck ;-)
Tnx,
Ward

OPENXML Not enough storage errors

I am trying to work the a large (65MB) hierarchical data structure stored in XML that describes phylogenetic relationships in biology. I would like to return a set of species belonging to a particular phylogenetic class (e.g. "Mammalia") which corresponds to returning all the terminal children of this node in the XML tree.

The query

select sp
from openxml(@.docHandle, N'//node[rank/text()="species"]/names/name[@.class="scientific name"]') with (
sp varchar(50) './text()'
)

runs and returns all 287000 species in the document, but if I try to restrict to children of the node "Mammalia" using the query

select sp
from openxml(@.docHandle, N'//node[names/name/text()="Mammalia"]//node[rank/text()="species"]/names/name[@.class="scientific name"]') with (
sp varchar(50) './text()'
)

I get an error:

XML parsing error: Not enough storage is available to complete this operation.

Seems like the latter query should use less memory, not more, because it is restricting the return set to a small fraction of the total.

Any suggestions?

Do you really need this complex path expression with two //node? That is going to cost you.

What about using the following expression:

//node[rank/text()="species"]/names/name[@.class="scientific name" and text()="Mammalia"]

Also, if you are using SQL Server 2005, have you looked at using the nodes() method and XQuery?

Best regards

Michael

|||

Unfortunately, reordering the XPath will not give the desired result set. Will take a look at XQuery and nodes().

Thanks,

David