0

we have a database table which has around 200,000 records. which includes 3 ntext columns, which hold string data with length vary from 4000-70000. but a mere selection on the table takes more than 1 minute to return data. and even using where condition, and indexes to select 12000 records for a condition it takes 40 sec.

so we decided to change those data types to nvarchar(max), but still did not notice a major difference, as it would store data out of the row since its too long. is there any better way i can improve this performance of my table ?

Improve this question
1
  • Gusy would a table partition would improve the speed too ? Commented Jan 25, 2010 at 12:32

6 Answers 6

Reset to default
7

What make you think your problem is your field data type? There are several other points to consider before anything else:

  • Do your tables have indexes? Are you using them?
  • Do you have enough bandwidth available?
  • Are your NIC cards using most updated drivers?
  • Have you analyzed your query execution plan?
  • Are your SQL server under stress (CPU/memory/disk)? And your webserver/desktop?
  • Are your data correctly normalized?
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

-yes i do have indexes -we were able to recreate this issue in our local qa environment. so bandwidth is not a problem. -well execution plan shows clustered index scan 100% .its just a selection query with a WHERE. funniest is it recommends to create an index which is already there. but it says to include the text columns also, but sql is not allowing to create indexes on those ntext columns
can you talk more about that table structure, existing indexes and where clause? do you use any function on that where?
IMO the red flag here is his clustered index scan coupled with the size of the return set.
@Aneef: You can't create indexes on the text columns, but you can still INCLUDE them. It's definitely the index scan that's your perf issue - a non-covering index is often of little value.
2

You should make the columns nvarchar instead of ntext, and you could include them in the index as nonkey columns. But... that's a lot of data you're fetching. If you need to perform the query so often that the 1 minute execution time is a problem then maybe you should rethink your approach.

Improve this answer

Comments

1

Could you not move the large text fields to a separate table and link them to the main table with a 1-1 relationship. That might help speed things up

Improve this answer

Comments

1

I agree with Kevin. Any scan (Clustered Index or otherwise) is bad, and including the data is not really a practical option.

Move the text into a separate table with it's own primary key, and use these three as foreign keys in your original table.

I do something very similar to this for storing textual data for medical claims, and it works a treat.

(As a side note) one other advantage to this is that odds are, you do not necessarilly have to display all of this text on the screen for the entire returned result set at one time - so you only end up fetching the specific text data you need down the line.

This lets you use the same table structure as both a summary view (like displaying a list of questions on stackoverflow) and as a detailed view (where you show all of the textual data for a single header record).

Improve this answer

Comments

1

If you don't want to move the ntext column to another table, make sure you're not retrieving those columns until the very last pass. So instead of this:

SELECT * FROM tbl WHERE (/* your code here*/)

Try something like this:

SELECT * FROM tbl WHERE id IN (SELECT id FROM tbl WHERE /* your code here */)
Improve this answer

Comments

0

for your second query, it could transfer over 4.6 gigabytes, so I can see it taking that long potentially...

for the single record query, you could try splitting it up into fixed-length columns:

ie. part1 nchar(2000), part2 nchar(4000), part3 nchar(8000), part4 nchar(16000) ...

if all the cols are non-varying, its easier to compute row boundaries when the cols are all fixed length.

if you "Show Execution Plan" in Query Analyze, anything useful show up...?

Improve this answer

1 Comment

well execution plan shows clustered index scan 100% .its just a selection query with a WHERE. funniest is it recommends to create an index which is already there. but it says to include the text columns also, but sql is not allowing to create indexes on those ntext columns

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.