Sql-server – Index Strategies on Text or NVARCHAR(MAX) Fields

indexindex-tuningperformancequery-performancesql serversql-server-2005

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 !

Best Answer

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?

Related Solutions

Sql-server – Advice on diagnosing a “sometimes” slow query

I really don't think using the OPTION (RECOMPILE) is an effective way to eliminate the possibility of parameter sniffing.

Parameter sniffing happens when SQL is confused about a particular query and thinks its new because it sees new parameters. It's slow because it's taking extra time to generate a new execution plan.

All that option does is force SQL to produce a new plan every time which is pretty much the same thing. Instead, you might want to consider adding default parameters using this hint:

OPTION(OPTIMIZE FOR(@LocationIds='xx',@StatusType='xx'))

When choosing parameters for the default make sure to use a statistically representative set.
That will force the same plan to be used every time and eliminate the possibility of parameter sniffing. Once you do that, and determine it didn't help, then its probably safe to dismiss parameter sniffing as a possibility.

Mysql – Optimize MySQL query with MAX, GROUP BY, and WHERE

There are only three serious candidates:

(`created_at`,`sql`,`elapsed_seconds`) -- 1
(`created_at`,`elapsed_seconds`,`sql`) -- 2
(`sql`,`created_at`,`elapsed_seconds`) -- 3

Both are "covering". That is, the query can be handled entirely in the index. EXPLAIN indicates such by saying Using index.

Analysis:

(`created_at`,`sql`,`elapsed_seconds`) -- 1
(`created_at`,`elapsed_seconds`,`sql`) -- 2

filter first. But then the rest of the index is not in any useful order. So it sorts to do the GROUP BY and eventually finds the max. It cannot simply reach for the 'last' entry to get MAX. I don't think either of these is better than the other of the two.

(`sql`,`created_at`,`elapsed_seconds`) -- 3

might avoid the sort, since the sql values come one at a time. Also, the Optimizer might be able to jump to the starting point in the index for the desired created_at (for each sql). Again, it cannot simply reach for the 'last' entry to get MAX.

I vote for #3. However, this is an area where there have been optimization improvements. That is, an older version of MySQL may not do, for example, the leapfrogging.

Edit

Best Answer

Related Solutions

Sql-server – Advice on diagnosing a “sometimes” slow query

Mysql – Optimize MySQL query with MAX, GROUP BY, and WHERE

Related Question