2

I'm looking at query plan in SQL Server Mgmt Studio and I see something like this:

(@P0 nvarchar(4000), @P1 nvarchar(4000)) Update...

What does the type mean beside the parameters? For this example lets assume that the column for those parameters are actually varchar(64). Is this how the parameters are being bound at runtime?

EDIT

This is a Java EE application using JDBC 3.0 or 4.0 providers. Basically we have a properties file full of SQL statements like this:

mySqlStatementFoo = UPDATE Schema.Table.Column set column = 1 WHERE objectID = ?

It looks like in the past if there was a binding mismatch we would cast the param like so:

Where objectID = CAST(? AS VARCHAR(36))

I suppose they chose to do it this way since we support multiple databases. I.E. DB2 and Oracle. I haven't seen the actual Java, but I suspect they are just selecting the statement from the file and sending it down the wire. I'm doing load testing and seeing that we are doing table scans and suspect that the optimizer isn't using the correct index because the parameters are being bound incorrectly.

  • 1
    Yes, SQL Server made a guess at the data type based on the format of the submitted statement. Not sure where this UPDATE statement originated, but you should consider using stored procedures or strongly typed parameterized statements. – Aaron Bertrand Dec 30 '14 at 18:26
  • How can I strongly type the statement? I see this parameter binding problem came up before and the developers added a cast to the param in the where clause. – ZeroDivide Dec 30 '14 at 18:42
  • 1
    Well, for one, you could use a stored procedure. Otherwise you're going to have to share more info. What developers? What language are they writing their code in? What provider are they using to connect to SQL Server? Show some code and we may be able to help/ – Aaron Bertrand Dec 30 '14 at 18:44
  • I've added some context above. – ZeroDivide Dec 30 '14 at 18:56
  • 2
5

Yes, you are likely seeing implicit conversions due to the guesses SQL Server has to make. It is creating a plan that will work not just for the parameter value you're passing now, but other potential values, too. With strings it (or perhaps JDBC? not sure) chooses an arbitrary default of 4000 and Unicode. I believe there are ways in JDBC to not send Unicode (Unicode vs. non-Unicode implicit conversions are among the most tedious), but you're still going to deal with the length issue (which may or may not be an issue in your specific case; again, not sure).

To avoid the implicit conversions and guesses, and ultimately the scans and high CPU conditions, you need to make SQL Server understand the data type that is being used in the parameter through explicit / strong typing.

Here are my suggestions, in order of preference:

  1. Switch to stored procedures.
  2. Switch to stored procedures.
  3. Declare your variables explicitly, e.g.:

    DECLARE @p1 VARCHAR(36) = ?; UPDATE ... WHERE col = @p1;
    
  4. Keep using CAST/CONVERT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.