September 2, 2014 at 1:25 pm
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
September 17, 2014 at 7:33 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply