SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implicit conversion of input parameter in predicate causing performance issues


Implicit conversion of input parameter in predicate causing performance issues

Author
Message
T Michael Herring
T Michael Herring
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 598
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
Maintainer
Maintainer
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 10
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search