SQL Server Performance Forum – Threads Archive

Performance Issue with ntext

Hi All, I am doing an project in which we need to capture huge text (more than 8000 chars).
so I used ntext as the column data type.
while using this column in simple select queries it is taking long time to get the results displayed.
Could any one help me in optimizing the query to get the results faster. Thanks in advance. Regards,
Murali

---

Murali,
is there any possibility of using text type instead of ntext Other than that there is no way (As I know) to optimize the performance. You can’t have indexes on ntext field either In Addition, when using select try to select only the necessary fields.

---

Are you sure the data will be constantly more than 8000 chars for that column? Books online outlines the tip of using SP_TABLEOPTION in this regard to gain the performance from the columns that uses text/ntext. By default, ntext or text datatype data is not stored with the rest of your row’s data in the table because that data can exceed well over a gigabtye. Instead, ntext or text data has a pointer stored in the row to indentify the data page where the actual data is stored. You can execute this SP as: EXEC sp_tableoption ‘mytablename’, ‘text in row’, ‘6500’ This will make the adjustment the next time the data is updated.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

"taking long time to get the results displayed" is relative. What exact does this mean. 3 seconds, 3 minute or what? How many rows are we talking about? Also, what do your queries look like? Your indexes? Are the BLOB column frequently retrieved along with other column or not?
For a general understanding of BLOBs, see if this helps:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx
—
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Use text field if it is possible.
If it is possible and that column is not used frequently, try to keep that column(ntext) in separate table and make relationship between two tables.
Surendra Kalekar

---

Hi dineshasanka, I can use text but the data should be unicode. I am not aware of the impact of changing the ntext to text. Could you tell me will it cause any data loss. Also I am selecting necessary fields in the select statements. Regards,
Murali

---

Hi Satya,Frank, Thanks for the suggestion .
I will try the "text in row" option. Frank: My query is a simple select statement.
FYI
the query I am using to fetch the records is.
"select case_number_id,Generic_text,user_response from generic_answers order by case_number_ID" In the above query "user_response" column is of ntext data type.
Currently there are 19000 records in the DB. The above query takes 2min time to fetch all the records.
In the above mentioned table case_number_id is a foreign key. could you suggest me a solution. Regards,
Murali

---

yes there is no way that you can change it to text as you are using unicode

---

Why do you need to return all rows from the table? Can you redesign your process/GUI to select just top N rows?

---

Hi Murali,
I think one more thing you can change in the query is removing orde by clause.
Do this ordering on client side as removing this will help in performance.

---

Piggy-backing on mmarovic: Is there really no need for a WHERE clause? Retrieving 19,000 rows with BLOBS data over a network sounds like a lot of overhead to me. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Are you sure the data will be constantly more than 8000 chars for that column? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Hi All, Satya:No,the data will not be constantly more than 8000 chars for the "user_response" column.
It is the clients requirement that they dont want any limit to the data entered.
That’s why we used ntext. Frank: Yes, there is a need for a where clause. but there is one senario that doesnot require where clause.
My idea is to achieve performance for the whole data than the filtered data. If I am going wrong any where please correct me. Regards,
Murali

---

If the data is not more than 8000 chars then its better to use VARCHAR for performance consideration.
You can control/manage the entered data divide into 2 columns if required. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Do you mean you read all data and filter them on client? If so, that is not very good idea.

---

Hi All, Mmarovic:No,I will filter first from the client itself then pass the query to the DB.
The performance issue I am facing is in generating the reports.
Here the end user will select the date range and some products. In the products list there is an all option.
IF the user selects "all" option I need to pull all the available data from the DB.
My idea is to achive the performance to the "all" option i.e if I can achieve all option, the other options also works fine right? Please correct me if I am wrong in explaining the issue. Regards,
Murali

---

That report doesn’t produce any aggregation since it is returning text column. It means user can choose to review 19000 rows. After he has 19000 rows displayed he will never review more then 50, maybe 100. Better change your strategy. Either force user to filter rows based on additional criteria or try some paging technique.

---

In addition to that, it might be a good idea if you retrieve the text column only when requested by the user and not generally. I think nobody will read a report containing 19,000 columns without any aggregation. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Good idea Frank. In other words if you insist to display 19000 rows, at least display text data later on demand.

---

]]>