Monday, March 16, 2015

Troubleshooting SQL index performance on varchar columns @ LosTechies

I was looking today into optimizing queries against a 450M row table that has an index ( not the primary key of course ) which is a string. I was checking around on the internet for some best practices and came across this interesting article:

So as a heads-up: be sure that your string type indexes ( if at all possible ) are type varchar - and that your ORM writes a query that does not require SQL Server to do a data type conversion on the field.

Apparently EntityFunctions.AsNonUnicode() enforces this:

Or - just use the ColumnType attribute on the appropriate column, which keeps things nice and clean:

No comments:

Post a Comment