Performance issue with NVARCHAR fields.

[kees07]

We have 4 tables with field types like NVARCHAR and SMALLINT and we have 4 tables in the same database with VARCHAR and SMALLINT. The all reside i the same MSSQL2008R2 database with a database collationtype LATIN_GENERAL_BIN.

If we run a simple query on the VARCHAR tables it comes with results in a few seconds. But if we run the same query on the tables with the NVARCHAR tables it takes more then 7 minutes. CPU is skyhigh during the query on NVARCHAR tables.

This is a huge performance issue and till now we don't have any idea what causes this performance flaw.

Does anyone have an idea?

Kees

[BitBlt]

Could be any number of things. Not much detail in your post. In no particular order:
How big are the nvarchar and varchar columns?
Nullable?
Sparsely filled?
How many rows in each table?
Indexed?
Clustered?
Fragmentation?
What does your server paging look like?
Server memory utilization?
Disk utilization?
Cache Hit Ratio?
Full Text Search?
I/O Contention?
Other processes on the server?

I could go on and on, but you get the gist. Simplest thing to do is to do a showplan on your queries...first on the varchar version, then on the nvarchar version and see if there are obvious "most expensive parts". Then just start going down the list.

[kees07]

Hi and thanks for the first reply.
Most of your "questions" can be ruled out since:

the indexes, nullableness, data and rows on all tables are the same
they run on the same database so server or database difference or bussiness could not cause difference I supose.
All the data in all thpe data is the same en inserted just a second before the query runs. No fragmentation should be there I suppose.
However your first question about How big are the nvarchar and varchar columns? might be interessting. The differ a bit (off course) But could that be the cause of a performance difference of 7 minutes?

Kees

[BitBlt]

Still not enough detail. The questions I listed were just standard DBA fare...the first things you check (even if only to rule them out). There will be differences in the size of the columns...nvarchar holds double-byte character representations so with the same data the column will be twice as wide. It also has an impact on performance of indexes.
You may also want to update stats before you run your query. Do you have a DBA there to look at your configuration? Without someone "on the ground" there it is nearly impossible to diagnose what your problem may be.

[kees07]

Don't get me wrong. I appreciate your suggestions, but as you wrote the list of questions was "first thing to do stuff". So I did before posting my first question.
The tables are created and inserted with data, then I rebuild the indexes en then I did the select query. No other database or proces runs on that server. Its a dedicated query test to run all other matters out. At least thats what I think.
Then again about the column size. yes they are exactly twice the size in the unicode tables. Indexes are the samethough on both the unicode and the onounicode tables.

Still no idea whats going wrong.

Detlev

[crishlay]

send your table and sql query here...there are not possible this take too much time