Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Implicit conversion of input parameter in predicate causing performance issues Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2014 1:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:16 AM
Points: 261, Visits: 440
I have an application that is submitting a couple parameterized queries that are causing performance issues due to an implicit conversion being performed in the predicate list. The column is a timestamp and the parameter is coming in as a bigint.

This is a vendor application, and though the vendor is aware of the issue and are working on further analysis, it will likely be some time before we have a solution in hand. What I am looking to the community for is whether or not anyone is aware of any methods available to circumvent the execution of the submitted T-SQL in favor of a statement with an explicit cast (this has been manually tested and works) or a method to modify the incoming parameter data type, all from within the database engine.

Below is an example (edited, but functionally complete) of one of the queries as passed in from the application:
exec sp_executesql N'select top 50 "tbl0".ObjectId as "objectid", "tbl0".ObjectAccessId as "objectaccessid" 
from Table0 as "tbl0"
where (("tbl0".VersionNumber >= @VersNum0))
order by "tbl0".ObjectId asc, "tbl0".ObjectAccessId asc', N'@VersNum0 bigint',@VersNum0=123456789

And this is an example of a more optimal version (explicit cast):
exec sp_executesql N'select top 50 "tbl0".ObjectId as "objectid", "tbl0".ObjectAccessId as "objectaccessid" 
from Table0 as "tbl0"
where (("tbl0".VersionNumber >= CAST(@VersNum0 AS TIMESTAMP)))
order by "tbl0".ObjectId asc, "tbl0".ObjectAccessId asc', N'@VersNum0 bigint',@VersNum0=123456789

And an example with the correct parameter data type:
exec sp_executesql N'select top 50 "tbl0".ObjectId as "objectid", "tbl0".ObjectAccessId as "objectaccessid" 
from Table0 as "tbl0"
where (("tbl0".VersionNumber >= @VersNum0))
order by "tbl0".ObjectId asc, "tbl0".ObjectAccessId asc', N'@VersNum0 timestamp',@VersNum0=123456789

If anyone has any suggestions, I am certainly willing to take a look. I've looked at the viability of plan guides with no success...

Thanks in advance.


T. Michael Herring
Database Administrator
Wells Fargo
Post #1609762
Posted Wednesday, September 17, 2014 7:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:35 PM
Points: 6, Visits: 9
Can you create a temporary table (as a staging table) with the datatypes you want? You could insert the incoming data into desirable covariant data columns. From there you could manipulate the data as you want.
Post #1614671
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse