Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following query (simplified for question) I'm trying to speed up for a read only DB ...

SELECT 
 [sysid]
,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)   
,[From]=[from_addr]   
,[To]=[to_addr]  --I'm a very long Text or NVARCHAR(MAX) Field
,[Subject]=[subject]  
,CASE WHEN [attach] = 1 THEN 'Yes' ELSE 'No' END AS 'Att'   
,[Code]=[ccode]   
,[Staff]=[staff]  
,[MatNo]=[mat_no]  
FROM dbo.[email] 
DYNAMIC WHERE CLAUSE ON ANY OF ABOVE

I've tried adding some indexes including covering indexes I can't include the to_addr the way it is (as text or NVARCHAR(MAX) col), and the query optimizer ends up using the clustered index because the to_addr field is not included. What are some ways to handle a situation like this ? Unfortunately I' limited to 2005 on this.

Edit

Tried adding Full_Text For to_addr still does a table scan. However if I comment out that line out it will use the index. : ( Damn you Text Data !

share|improve this question
2  
Fulltext index? –  Jon Seigel May 1 '12 at 0:15
    
@JonSeigel Damn has it come to that ? lol –  bumble_bee_tuna May 1 '12 at 0:27
2  
TEXT? You shouldn't be using TEXT anymore. –  Aaron Bertrand May 1 '12 at 1:06
    
@AaronBertrand I didn't do it ! Its ISV I changed to NVARCHAR(MAX) is that the correct move ? Data Length is well over 4000 characters. –  bumble_bee_tuna May 1 '12 at 1:08
2  
Yes, TEXT should be VARCHAR(MAX) and NTEXT should be NVARCHAR(MAX). –  Aaron Bertrand May 1 '12 at 1:10
add comment

1 Answer

up vote 5 down vote accepted

Why do you think anything but a scan should be used to pull back all the data? A full-text index won't really help - that helps you search those columns, but if you're just returning all the data (for any variety of WHERE clauses) then there's no shortcut to reading all of the data. Can I ask why a to_addr, which is presumably limited to ~320 characters by the SMTP standards (depending on which standard you believe), contains data > 4000 characters?

A lot of people think that a scan is bad. If you need to return a large amount of data, then often a clustered index scan will be used. Your where clause may lead to seeks being used to locate the rows to return, but a seek isn't going to work where the data in that column is that large. Are you just seeing a scan in the execution plan and assuming that must be the problem?

share|improve this answer
    
Wow, good answer. Yes what led me down this path was seeing that when the data was read of the nonclustered index the IO was much less but after dusting my brain off quick this is obviously because it isn't retrieving such a wide row. Good call. Also is there any perfomance benifit to changng TEXT field to varchar(max) ? –  bumble_bee_tuna May 1 '12 at 1:23
3  
Queries won't magically become faster (if you're pulling 50,000 characters per row, it doesn't really matter what the source data type is), but you gain in a lot of other areas, for example you can interact with VARCHAR(MAX) like a first-class citizen instead of all the limitations with TEXT, and of course your schema will be supported going forward (TEXT/NTEXT/IMAGE will eventually be removed from SQL Server). –  Aaron Bertrand May 1 '12 at 1:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.