Sql-server – Will one nvarchar column affect query performance

performancesql servervarchar

I have one column that I recently converted to nvarchar(100) to store Asian characters. The other columns in this table, and the other tables in the same database, use varchar, int, bit and datetime. Since that change, some of my queries now take ten to twenty seconds to return less than fifty records. The tables store a thousand records or less.

I've read nvarchar has an associated IO performance cost with it, but this bad for such a small record set? Do I have to adopt an all or nothing approach with nvarchar (convert all varchar to nvarchar) or have I missed something obvious?

Best Answer

Without knowing more details, it's impossible to tell, but you might be suffering from implicit conversion. There are a number of sites with information on the topic that you can use to help troubleshoot your particular query.

The short answer is that no, you should not need to convert all columns to nvarchar. If you are trying to join an nvarchar column to a varchar column, however, you could see a major performance impediment.

Related Solutions

Mysql – How much will using a varchar(15) PK affect the tables performance

I don't know that the PK is your issue. To me, doing a request for all 400k rows, 15 seconds doesn't sound too bad. PKs and indexes really come into the equation when you're trying to filter the set (using WHERE conditionals).

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate.

When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly distributed according to date.)

See my answer here for a similar issue

The ideal index for this query would be one on SomeIndexedValue, CreatedDate. Assuming that you can't add that or at least make your existing index on SomeIndexedValue cover CreatedDate as an included column then you could try rewriting the query as follows

SELECT MIN(DATEADD(DAY, 0, CreatedDate)) AS CreatedDate
FROM MyTable
WHERE SomeIndexedValue = 1

to prevent it from using that particular plan.

Best Answer

Related Solutions

Mysql – How much will using a varchar(15) PK affect the tables performance

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

Related Question