Problems when I used Big Texts In MSSql Database!

helo..

I have Big texts And I Want To searching In These texts By MSSql Database,

i saved this texts in about 4800 rows in one table with one ntext field.

when I finished I surprised with some problems:

1- when I want to search in this table by simple Select Command like this: "Select topicID from topicstbl where topictxt like '%vb forums%'
I faced this ERR MSG "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." And It does not continue searching to the last row so it returns only some of the results.

2- it is too slow and it took three minuites to performe one incomplete search operation because it was "TIMEOUT".

3- the size of original texts is 300MB but when i saved these texts to MSSql Database its size becomes too large its more than 1GB.

what can I do with these problems taking into concideration that I do not want to use FULL TEXT SEARCH?

Are you running SQL Server 2005? If so, you can try using an NVARCHAR(MAX) datatype instead of an NTEXT datatype. Then, you can create a non-clustered index that has topicID and then topictxt as an INCLUDED column. This would be a covering index for your query, and it will give you much better performance. See the sample below:

CREATE NONCLUSTERED INDEX [IX_topicstbl_Cover1] ON [dbo].[topicstbl]

(

[topicID] ASC

)

INCLUDE ( [topictxt]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

|||

In terms of the increase in size then if the original text was held in an ASCII style format (1 byte per character) then in moving to ntext you have gone to 2 bytes per character and so a base doubling of the actual text size regardless of any other overhead for the tables etc.

If you are dealing with this amount of text, and convert it to the indicated nvarchar(max) fields then you could consider using Full Text Searching (FTS). This takes administration etc. but would provide much faster adhoc searching of these large text fields. This assumes you are using a version of SQL Server which support FTS - This is I believe Enterprise, Standard, and Workgroup as of SQL Server 2005.

I would recommend using one of the (max) type fields as the types ntext, text, and image are being deprecated and will be removed from a future version of SQL Server.

|||Queries using a LIKE condition that begins with a wildcard (the % character) cannot use an index on that column. Think of it this way: if you wanted to look in the phone book and find everyone whose last name ends in "son," how would you do it? You'd have to look through each name sequentially, which is what SQL Server has to do.

Honestly, the simplest way to get the query performing with reasonable speed is to implement full text indexing.|||

Thanks..

Yes I am using SQL Server 2005 with VB.NET 2005.

Now, if I want to use NVARCHAR(MAX) datatype for topictxt column

1- how can I save big text to it byte by byte and read it byte and byte?

I used this code when I use NTEXT datatype, but this code did not work properly with NVARCHAR(MAX):

Public Function SaveTextToDB(ByVal BigText As String, ByVal TblName As String, ByVal FldName As String, ByVal ColumnIDName As String, ByVal RowID As String) As Boolean

Try

Dim addEmp As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT @.Pointer = TEXTPTR(" & FldName & ") FROM " & TblName & " WHERE " & ColumnIDName & " = '" & RowID & "'", MyConnection)

Dim trParm As SqlClient.SqlParameter = addEmp.Parameters.Add("@.Pointer", SqlDbType.Binary, 16)

trParm.Direction = ParameterDirection.Output

addEmp.ExecuteNonQuery()

'''''''''''''''''''''''''''''''''''''

Dim bufferLen As Integer = 1048576

Dim appendToPhoto As SqlClient.SqlCommand = New SqlClient.SqlCommand("UPDATETEXT " & TblName & "." & FldName & " @.Pointer @.Offset 0 @.Bytes", MyConnection)

Dim ptrParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@.Pointer", SqlDbType.Binary, 16)

ptrParm.Value = trParm.Value

Dim photoParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@.Bytes", SqlDbType.NText, bufferLen)

Dim offsetParm As SqlClient.SqlParameter = appendToPhoto.Parameters.Add("@.Offset", SqlDbType.Int)

offsetParm.Value = 0

Dim offset_ctr As Long = 1

Dim buffer As String = ""

Do While BigText.Length - offset_ctr > bufferLen

buffer = Mid(BigText, offset_ctr, bufferLen)

photoParm.Value = buffer

appendToPhoto.ExecuteNonQuery()

offset_ctr += bufferLen

offsetParm.Value = offset_ctr - 1

My.Application.DoEvents()

Loop

buffer = Mid(BigText, offset_ctr, BigText.Length - offset_ctr + 1)

photoParm.Value = buffer

appendToPhoto.ExecuteNonQuery()

buffer = ""

Return True

Catch ex As Exception

MyErrStr = ex.Message

Return False

End Try

End Function

Public Function ReadTextFromDB(ByVal MyCommandText As String, ByVal TextColumnNumber As Integer) As String

Try

Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(MyCommandText, MyConnection)

Dim bufferSize As Integer = 1048576

Dim outByte(bufferSize - 1) As Char

Dim retval As Long

Dim startIndex As Long = 0

Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()

reader.Read()

startIndex = 0

retval = reader.GetChars(TextColumnNumber, startIndex, outByte, 0, bufferSize)

Do While retval = bufferSize

startIndex += bufferSize

retval = reader.GetChars(TextColumnNumber, startIndex, outByte, 0, bufferSize)

My.Application.DoEvents()

Loop

reader.Close()

Return Convert.ToString(outByte)

Catch ex As Exception

MyErrStr = ex.Message

Return ""

End Try

End Function

what is the right code to do this exact operation by using NVARCHAR(MAX)?

2- how can I include Full Text Search service in my program setup silently in the same way as including SQL Express to the setup project?

|||Oh, if it's Express Edition, then I don't believe you get full text search.

Why even use ntext/nvarchar if you're trying to shoehorn 8-bit data into the field? Unless you're storing unicode text in the column, then don't even bother with these data types. If you're storing raw binary data in these columns (I think that's what's going on, based on the "photo" stuff in the code), then look into using image, or better yet, varbinary(max). If it's plain old ASCII text, then use the 8-bit text or varchar(max) types.|||

davidbrit2 wrote:

Oh, if it's Express Edition, then I don't believe you get full text search

Just a note to say that I have just discovered that you can indeed get Full Text Search (and SQL Server Management Studio Express and Reporting Services) with SQL Server Express. You need to download the "SQL Server 2005 Express Edition with Advanced Services" rather than the standard version. This is 234Mbytes as opposed to 37M (+ 43M for Management Studio Express on its own) but you can then use FTS apparently. It is another option on the download page.

Thought it worth getting this information in here.

|||

thank's all..

I will try FTS..

|||Yeah, I discovered that some time after my reply and never thought to bring it back up here. :-) Thanks for mentioning it.