| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
diego
Starting Member
1 Posts |
 Posted - 11/05/2001 : 02:35:00
|
Ok, I've stumbled across this problem too and in "most" of all I aggree with You but as You read MOST of all but not all. I was designing a site for reading/publishing books online and knew that I surely had to deal with REALY large texts. First I started the same way as You..but see the "evolution" of my thoughts Yourself:
1. Storage of data-definitions (author,submitdate, title, genre, topic etc.) in a table. [status ok and kept because there IS no alternative way of sense]
2. Storage of the content in TEXT fields for easier fulltext-searches to be performed. [Status dropped this idea for a while, see below]
3. Because of the inefficient way of retreiving all the data for the content on every BOOK-page I played around with the much faster filesystemobject and also the STREAM object and got a very good performance boost in heavy-load situations [Still not satisfied because its harder to implement indexing as a linked service on a remote(!) sql-server while the indexed pages are located on the WEB-server as resource files. Additionally not satisfied with the obvious situation when You want to access the data from different machines without manually changing site-code (thats what makes a database so useful isnt it?  ]
4. Full-Text Indexing of remote data went me nuts so I decided to play with TEXT-fields again. Additionally that killed my fears concerning data-access from remote sites.
5. IIS5 and MSSQL2000 allow for simple usage of Recordset addnew or inserts and selects. But(!) You will definately not be sure what happens with REALY large text (I found situations when IIS will raise an error (and these methods tend to be VERY slow in heavy load). I decided to go back to retreive/insert data by ado-methods getchunk and appendchunk.
6. I am still not satisfied because each time a user reads a bookpage he is causing a roundtrip to the sql-server. Yes, MDAC2.6 + has several intelligent caching options and it works just fine but still not the solution...
I decided to "cache" the bookpages on the webserver(s) and to write a little functionset that (1.writes to that chache when a page is setup/altered/deleted, 2. on READ checks if a certain document exists and if not creates it from the database content and then reads it for output. Only "negative" sideeffect is that You have to handle redundant data (text is stored as "master" in the database while all webservers have "slave" textcontent cached. Intimes of 100GB+ harddiscs this really should NOT be a problem of space, but indeed it CAN become a performance problem again if the caching is not organized VERY good (for more info get informed about the NTFS filesystem and its inner works)
After all my system looks like:
Post/edit message/book:
-write ALL data to database (all descriptive by inserts or better yet using the command object of ADO and procedures, textcontent by appending chunks to Your database.) Use Fulltext-indexing whereever You like in Your database (fast and reliable)
-Creating the Cachefile of the CONTENT to save Yourself from transfering the content text on each request (SQL-Server =WAN/LAN=> IIS-Server)
Read message
-read descriptive data from database (if a search is performed the CONTENT in database is used too)
- then read the LOCAL chache to get the Content of the book into the page
I am still considering if I will extend caching in the way that I create the whole HTML-document and dont perform any script for lets name it "content.asp?book=213" instead: "/content/213.html". Not a problem to make it but I still think about several negative effects of it. But as You all might know asp still performs slower than showing a static html-file. Remember even a messageboard is typically write*1/read*n and all extensive tasks should be made in *1 situations rather than *n situations.
Thats my input, no for my questions :
In Sql-server, to create a linked server named FTIndexWeb that accesses a full-text index named Web, You execute:
"sp_addlinkedserver FTIndexWeb, 'Index Server', 'MSIDXS', 'Web'"
Unfortunately I did not find an OLEDB property for indexing provider that maps the property location (as used in addserver) to the indexing server oledb property "machine" so I could use a remote machine where the textfiles reside and the sql-server uses a distributed query (openquery) to access this via a linked (indexing) server. I only managed to use it with any catalog stored in indexing service on the local server. Maybe someone knows how to define a remote machine in sp_addserver (the same when using the grafical admin in enterprise manager) with indexing service OLEDB? I need it for another scenario where I use sql-server to check for indexed documents on an enterprise fileserver and merge it with database informations. For now I have to use scripts running local on the fileserver and sending info periodically to the sql-server. This does not satisfy me and I'd prefer to use a pull-method instead of push.
|
 |
|
|
RobWafle
Starting Member
38 Posts |
Posted - 03/22/2002 : 15:45:01
|
diego,
if you're still out there.. please contact me at robwafle@hotmail.com ..
The idea of the linked server below allows you to query the microsoft indexing engine from SQL server, so you don't have to put your documents in the database.. just the filesystem.
For me, this was exactly what I was looking for, as I have 311GB+ (aprox 150GB/year) of documents that didn't need to be copied into my SQL server.
Rob
|
|
|
|
NewtonTroy
Starting Member
Canada
2 Posts |
Posted - 04/02/2002 : 17:16:45
|
Has anyone been able to set up indexing services on a remote machine and connect to it sing a linked server?
I have no problem setting up Indexing Services as a linked server locally, but I can't seem to connect to a remote machine's Indexing Service through setting up a linked server.
Any ideas or suggestions? Anyone have this working?
Troy
TroyN@Ingenium-Tech.com |
|
|
|
NewtonTroy
Starting Member
Canada
2 Posts |
|
|
John
Starting Member
United Kingdom
9 Posts |
Posted - 09/23/2002 : 10:10:59
|
Please could you give more explaination of your solution as I cannot seem to get this sorted.
Cheers
|
|
|
|
pinklehammer
Starting Member
2 Posts |
Posted - 01/27/2003 : 09:05:48
|
quote:
Real Big TEXT Fields <P>Could you please explain how the following is done in more detail?
Real Big TEXT Fields
In my youthful innocence I created a 55,000 character text field using an ASP page (I used TEXTAREA). I passed this using a POST to another ASP page and called a stored procedure passing my 55,000 character field as a parameter. It got passed and INSERTed just fine. I returned it back to the page using a SELECT and it displayed just fine. Took a while to scroll down the page but all the text was there. I tried the same thing on the hosted site using a 15,000 character field and it worked fine also.
Joost Meij
jmeij@msn.com
|
|
|
|
pinklehammer
Starting Member
2 Posts |
Posted - 01/27/2003 : 09:07:38
|
Define the table in SQL server, open Access and create a project with an existing data source, open the table in the Access project and you can successfully paste HUGE amounts of text which will be stored/retrieved quite happily within SQL.
quote:
Real Big TEXT Fields <P>Could you please explain how the following is done in more detail?
Real Big TEXT Fields
In my youthful innocence I created a 55,000 character text field using an ASP page (I used TEXTAREA). I passed this using a POST to another ASP page and called a stored procedure passing my 55,000 character field as a parameter. It got passed and INSERTed just fine. I returned it back to the page using a SELECT and it displayed just fine. Took a while to scroll down the page but all the text was there. I tried the same thing on the hosted site using a 15,000 character field and it worked fine also.
Joost Meij
jmeij@msn.com
|
|
|
|
gjsiii
Starting Member
2 Posts |
Posted - 02/27/2003 : 17:55:11
|
I'm having problems setting up a database as described in the article. I too would like a varchar field to hold 4000 characters however I am unable to get it to hold any more than 1023 -- even when the length of the field is set to 8000. Is the 8000 the size of the field in bytes?
I'm also unable to get any more than 256 characters into a field of datatype text and cannot change the length from the default length of 16. This is all happening on SQL Server 2000 and I am using Enterprise Manager to modify the tables. Any suggestions? Thanks.
|
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 02/27/2003 : 18:47:07
|
You can't change the length of text. The 16 bytes is the size of the pointer it uses to store the actual data. You simply declare a column of type "text" and don't indicate a size.
Do not use Enterprise Manager for data entry, it is the wrong tool for the job. It has all kinds of little quirks like the one you're seeing. The easiest way to enter data into SQL Server is to link the tables into an Access database. You'll be able to do all of the data entry there without any limitations.
|
|
|
|
gjsiii
Starting Member
2 Posts |
Posted - 03/01/2003 : 13:06:38
|
robvolk,
That seems to work just fine. Thanks for the help.
|
|
|
|
marcus
Starting Member
1 Posts |
Posted - 06/19/2003 : 02:02:31
|
A question slightly off track if I may..
I have a database that has data (3 of 300 rows > 8000 characters) stored in a text field. I would like to UPPER this data but this cannot be done on a text field. Is there another way to do this please...my long search through google got me to here 
cheers
Marcus |
|
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 06/19/2003 : 07:27:52
|
Look in Books Online under the UPDATETEXT command. That's probably the only way you'll be able to do it. You'd use READTEXT to read part of the text column into a variable, UPPER() the variable, and use UPDATETEXT to change the column.
|
|
|
|
Michael Shan
Starting Member
2 Posts |
Posted - 06/08/2004 : 22:38:52
|
Hi,
Anyone can give me a quick answer. I make one field as varchar(8000) datatype. But when I try to insert long string data into this field, it only allow around 1000 char to be inserted. Do you know why and how to solve this problem?
Thanks
quote:
Originally posted by AskSQLTeam
Brad writes What is the best way to store text in SQL Server (7.0) so that it can easily and quickly be served up to an ASP page? For example, say I have 500 articles, ranging in size from 500 characters to 25,000 characters. Not only do I want to store the text of the article, but also the title of the article, its date, the author, the subject, and so on. I also want to be able to search the text of the articles. Obviously a VARCHAR field won't hold really long articles, and TEXT fields are slow and hard to work with. I also want to avoid using the SQL Server Search service. What is the ideal solution?<BR><BR>Brad M. McGehee<BR><a href="http://www.sql-server-performance.com">sql-server-performance.com</a><P>Article <a href="/item.asp?ItemID=202">Link</a>.
|
|
|
|
nedda
Starting Member
Bulgaria
1 Posts |
Posted - 03/29/2008 : 06:21:05
|
quote:
Originally posted by robvolk
You can't change the length of text. The 16 bytes is the size of the pointer it uses to store the actual data. You simply declare a column of type "text" and don't indicate a size.
Do not use Enterprise Manager for data entry, it is the wrong tool for the job. It has all kinds of little quirks like the one you're seeing. The easiest way to enter data into SQL Server is to link the tables into an Access database. You'll be able to do all of the data entry there without any limitations.
This does it - inserted text programatically and it worked just fine. Thanks for the time saver! |
|
|
| |
Topic |
|
Article: Best way to store text in SQL Server for ASP pages?
