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

Ward
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"  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"  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"  wrote in message 
news:OaGQ5bZSGHA.1728@TK2MSFTNGP11.phx.gbl...
> 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"  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?
>>
>
>

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"  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"  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

Another option would be to keep the basic database design as is (store the 
text in a table column), but compress / decompress the text at the 
application layer. However, in this case, you would probably need to store 
the data as image data type. This would be based on the common database 
document management design patterns.

http://www.dbazine.com/sql/sql-articles/charran5
http://www.databasejournal.com/features/mssql/article.php/1443521
or just google for: document and image and "sql server"

Also, you will need a method to perform the text compression / decompression 
at the application side.
http://msdn.microsoft.com/msdnmag/issues/03/06/ZipCompression/default.aspx
http://datacompression.info/Zlib.shtml


"Ward Bekker"  wrote in message 
news:1142609704.439115.241660@i39g2000cwa.googlegroups.com...
> 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
>

Have you thought about getting a textbase (document system) to handle a
text problem/  SQL is not aleways the answer.

Celko : Do you have an example of such a product?

JT : I already store the data compressed, unfortunatly the space needed
in sql server per record is still to large.

Here is a very old list of products, but it is a start for you:
ZyIndex has been the "Gold Standard" for years, but there are some new
products that are as good.

 AskSam Systems
 Box #1428
 Perry, FL 32347

 Bridger Systems
 Box #1207
 Bozeman, MT 59771

 BRS Software
 800 Westpark Drive
 McLean, VA 22102

 CompuLink Management Center, Inc.
 370 South Crenshaw Blvd, #E-106
 Torrance, CA 90503

 Data Retrieval Corporation
 11801 West Silver Spring Drive
 Milwaukee, WI 53225

 Dataflight Software
 10573 West Pico Blvd #68
 Los Angeles, CA 90064

 Document Systems Inc.
 12 South Main Street
 Norwalk, CT 06854

 DT Search Software
 2101 Crystal Plaza Arcade #231
 Arlington, VA 22202

 EPI Inc.
 Thunderstone Software
 11115 Edgewater Drive
 Cleveland, OH 44102

 Excalibur Technologies Inc.
 1959 Palomar Oaks Way #100
 Carlsbad, CA 92009-1307

 Executive Technologies
 2120 Sixteenth Avenue South
 Birmingham, AL 35205

 Folio Corp.
 5072 North 300 West
 Provo, UT 84604-5652

 Fulcrum Technologies Inc.
 785 Carling Avenue
 Ottawa, Ontario K1S 5H4
 CANADA  Gunning Wordnology
 344 Durie Street
 Toronto, Ontario M6S 3G7
 CANADA

 Horizons Technology
 Power!Search
 3990 Ruffin Road
 San Diego, CA 92126

 IBM SearchManager/2
 client/server ap for OS/2 and Windows

 Infodata Systems Inc.
 5205 Leesburg Pike
 Falls Church, VA 22041

 Larson-Davis Information Systems
 InfoQue
 1681 West 820 North
 Provo, UT 84601

 MacKinney Systems Inc.
 2674-A South Glenstone #112
 Springfield, MO 65804

 Microlytics, Inc.
 2 Tobey Village Office Park
 Pittsford, NY 14534

 MultiWriter Software
 763 Stelton Street
 Teaneck, NJ 07666

 National Information Standards Organization
 Box #1056
 Bethesda, MD 20817
 BSR Z39.58 "Common Command Language for On-Line Interactive
Information
Retrieval"

 NDX Corporation
 10400 Eaton Place #403
 Fairfax, VA 22030

 PC DOCS, Inc.
 124 Marriott Drive #203
 Tallahassee, FL 32301

 PC-TRANSLATOR
 Linguistic Products
 Box 8263
 The Woodlands, TX 77387

 Personal Library Software
 Personal Librarian  Rockville, MD 20850
 Promark Software Inc.
 120-17411 Union Hill Road
 Redmond, WA 98053

 Promark Software
 120 -17411 Union Hill Rpad
 Redmond, WA 98053

 ProSoft
 399 East Tenth Street
 Eugene, OR 97401

 QL Systems Ltd
 One Gore Street
 Kingston, O, Ontario K7L 2L1
 CANADA

 Quicksoft Inc
 PC Browse
 219 First Avenue North #224
 Seattle, WA 98109

 RAM! Sector Publications
 Box 738, Station C
 Toronto, Ontario M6J 3S1
 CANADA

 Recognita Corp. of America
 1250 Oakmead Pkwy #210
 Sunnyvale, CA 94088-3599

 Research Software Design
 2718 SW Kelly Street #181
 Portland, OR 97201

 SoftSolutions Technology Corp.
 Novell, Inc.
 122 East 1700 South
 Provo, UT 84606

 Sumak Corporation
 39 Dawson Drive
 Sudbury, MA 01776

 TextWare Corp.
 Box #3267
 333 Main Street
 Park City, UT 84060

 Thunderstone Software
 EPI Inc.
 11115 Edgewater Drive
 Cleveland, OH 44102  Verity Inc.
 1550 Plymouth Street
 Mountain View, CA 94043

 Virginia Systems Inc.
 5509 South Bay Court
 Midlothian, VA 23112

 ZyLab Corporation
 1130 Lake Cook Road #102
 Buffalo Grove, IL 60089
 708-459-8000

=====================

 National Information Standards Organization
 Box #1056
 Bethesda, MD 20817

 Publications in textbases:

 Electric Word
 Emmalaan 21- EW 18
 1075 AT Amsterdam
 The Netherlands
 	<<out of business>>

 Records & Retrieval Report
 Box #5007
 Westport, CT 06881

 Searcher Magazine
 143 Old Marion Pike
 Medford, NJ 08055

 Text Management Journal
 50 Stanford Street #800
 Boston, MA 02114

 Text Technology
 Wright State University
 7600 State Route #703
 Celina, OH 45822

 Verbatim
 Box #78008
 Indianapolis, IN 46278

 Wordsmith's Review
 22250 Capulin Court
 Woodland Hills, CA 91364

=============================
Some UK text products for the UK edition of DBMS:

 Blackwell Scientific
 Osney Mead, Oxford,
 England OX2 0EL.
 US phone: 800-695-3364

 ZyImage
 Marketlabs (Europe) Limited
 497 Battersea Park Road
 LONDON SW11 4LW
 071 223 8247

 Excalibur EFS
 Excalibur Technologies
 Royal Albert House
 Sheet Street
 WINDSOR
 SL4 1BE
 0753 831978

 Visual Recall
 XSoft (division of Xerox)
 3400 Hillview Avenue
 Palo Alto

 Assassin PC (Assassin-6)
 Associated Knowledge Systems
 Amen House
 Bedale
 North Yorks
 DL8 1XA
 0677 425101

 First!
 Individual Inc.
 84 Sherman Street
 CAMBRIDGE
 Mass
 02140
 UK: Cedar Court, 9 Fairmile, Henley RG9 2JR
 0800 766 4224/ 617 354 2230;  491 5796000

 Basis
 Information Dimensions

 Headfast
 Head Software International
 Oxted Mill  Oxted
 Surrey
 RH8 9PB
 0883 717057

 Intelligence Analyst Workbench
 ICL
 Observatory House
 Windsor Road
 SLOUGH
 SL1 2EY
 0753 516000

 ProCite (for Windows)
 Personal Bibliographic Software
 Woodside
 Hinksey Hill
 OXFORD
 OX1 5AU
 0865 326612

 Druid
 BDC Techical Services
 Slack Lane
 Derby
 DE22 3FL
 0332 47123

 Star
 Alpha Micro
 Enterprise House
 Roxborough Way
 MAIDENHEAD
 SL6 3UD
 0628 822120

 YGrep Search Engine
 Yves Roumazeilles (101233.1032@compuserve.com).  It is not
specifically
oriented toward large text databases, but it allows regular expression
searches and searches with approximation levels (let's search "toto"
"tata" 
"tutu" as "toto"  2_errors_allowed). 

--CELKO--