Slow performance filtering on ntext column on large table
I have a table that logs user changes with this definition:
audit_trail
(
change_id int identity (1, 1) NOT NULL,
change_date datetime NOT NULL,
user_id int NOT NULL,
record_id int NOT NULL,
table_name nvarchar(50) NOT NULL,
field_name nvarchar(50) NOT NULL,
new_value ntext NULL
)
This query runs very slow (15+ minutes) on this table:
SELECT DISTINCT record_id
FROM audit_trail
WHERE table_name = 'jobs'
AND field_name = 'status'
A开发者_开发百科ND new_value LIKE '157'
My table has over 70 million records. This is not a usual query for this table. Normal queries on this table sort by date or search for changes in a date range so I have a clustered index on the change_date
column. The execution plan for this query shows it doing a clustered index scan. I thought I could improve the performance by adding an nonclustered index on (table_name, field_name)
but this index was not even used. Any recommendations on improving this query's performance?
try the following:
sp_tableoption N'audit_trail', 'text in row', '1024'
You might also consder full-text search.
If you can do that, changing nvarchar into varchar will improve the size, and probably the performance of your table. I also suspect that removing the unnecessary "DISTINCT" will allow the optimizer to use the other indexes.
精彩评论