CODEWITHSUNDEEP
javasql-server
Tim Sparg
Tim Sparg

Reputation: 3304

How bad is the usage of nvarchar

Our software is deployed at a client site where they are having performance issues, they've hired a SQL consultant to look at the databases and see where their bottlenecks are.

One of the things the consultant spotted was that a lot of our statements where converting to nvarchar. After some investigation I discovered that it was the PreparedStatement that was doing it.

To give you an example:

PreparedStatement query = connection.prepareStatement("SELECT * FROM sys.tables WHERE"
        + " name LIKE ?");
query.setString(1, "%%");

Becomes 

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'SELECT * FROM sys.tables WHERE name LIKE @P0        ',N'%%'
select @p1

the adhoc executeQuery method on the other hand just sends the SQL through

ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM sys.tables WHERE  name LIKE '%%'");

becomes

SELECT * FROM sys.tables WHERE  name LIKE '%%'

What I'm trying to work out is how bad this actually is. Prepared Statements are used all over our application because their use is supposed to be more efficient (if executed multiple times) than the adhoc queries.

Additionally I can't see that the Microsoft guys would put something into their driver that they knew would cause performance issues.

Is this something that we should really be looking into, or is the consultant looking for issues where there aren't any?

Upvotes: 1

Views: 535

Answers (2)

Daniel Baktiar
Daniel Baktiar

Reputation: 1712

Prepared statements come with more optimization options than that of ad-hoc query, and usually will be better from security point of view (no SQL injection by design).

The nvarchar usage usually comes from non-functional requirements. If you want your application to support any Unicode characters, which is very common nowadays, you need it. If that's in the requirement, don't change it.

I think LIKE query is the culprit, you should actually look at full text indexing capability in your backend database. The query looks super simple, it's the LIKE '%' query that causes scanning of character sequences in each field.

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

The pepared statement forces the query to become a parameterized query (which is a good thing), whereas the ad-hoc query may or may not be auto-parameterized by the SQL engine. In short, it's not a major performance hit.

However, there's always a lot of debate about whether or not you should use parameterized stored procedures from a design perspective (not necessarilly a performance one). I think most DBA's (and probably database consultants) prefer stored procedures because they're easier to debug using traditional database management tools (as well as providing security benefits, and including parameterization by default).

Upvotes: 1

Related Questions