LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Hi Guys,

I have been troubleshooting some slowness on a JDBC query that uses SQL bindings and wanted to see if anyone else has come across this issue or suggestions.

I have a query, lets say to look at an order table, to get a list of order numbers, and i pass in a order number as a JDBC binding, the purpose is to exclude any that exist already from being added. It runs a sql like the following:

select MASTER_ORDER_ID
from [DB].[dbo].[BRE]
where MASTER_ORDER_ID = ?

On the SQL Server, it looks like its taking the JDBC binding input as a nvarchar rather than a varchar.

Lets say I have 5000 orders to check, its taking 12+ minutes to get the output. After running the SQL and running the SQL profile in SQL Server, I am seeing it doing the following execution plan:

Select (Cost 0%) -> Parallelism (Cost %) -> Filter (Cost 100%) -> Compute Scaler (Cost 0%) -> Index Scan (Cost 0%)

If i run manually on the SQL server It executes quickly. And the execution plan is:

Select (Cost 0%) -> Hash Match (Right Semi Join) (Cost 60%) -> Constant Scan (Cost 0%) -> Index Seek (Cost 40%)

Is there anyway to force the jdbc binding input i am setting as a varchar, rather than nvarchar. It seems like the converting is causing the slowness.

Wayne

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

This article here is similar. how do i ensure the input is in the correct type to pass on?

http://lostechies.com/jimmybogard/20...rchar-columns/

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

Hi Wayne,

Interesting information and article, thanks. I'm guessing that this is going to turn into a request for an enhancement. But first, have you tried modifying the query to this to see how it compares?

select MASTER_ORDER_ID
from [DB].[dbo].[BRE]
where MASTER_ORDER_ID = CAST ( ? AS VARCHAR(16) )
-- the 16 is just a guess at what the size of the MASTER_ORDER_ID column is, it should be a number that matches but I guess could be bigger

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

In the comments at the bottom of the article there was a link to another article, http://www.sqlskills.com/blogs/jonat...e-index-scans/ which describes a very thorough analysis of when SQL Server does an index scan versus an index seek which results from data type coercion.

So then another option to explore is to change the data type on the other side of the equation. So in other words, change MASTER_ORDER_ID to nvarchar so that it matches the parameter's type. Of course nvarchar may require twice the storage space since it allows a larger range of character values. Matching the data types will result in no type conversions and therefore and index seek instead of an index scan.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Change makes approximately 50 faster.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

Think i found an non elegant solution. But makes results return in 20 seconds. Vs 12 mins.

See http://www.johnsansom.com/implicit-c...puted-columns/

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

So, you've created an "Indexed Computed Column". Very interesting. This is a duplicate index on the same column in the table. It at least doubles the space requirements for indexes on that column or worse since this is an index on the Unicode version of the values. In my opinion this results makes a very compelling case for giving the user more control over the data types of the parameters being passed into the query. It also makes a very compelling case for Microsoft to improve the query optimization logic in SQL Server since doing sequential searches is so clearly the wrong strategy in this context.