Query plan choses right index but wrong cardinality when an integer variable is used rather than literal

Query plan choses right index but wrong cardinality when an integer variable is used rather than literal

I have two identical selects one uses a literal integer the other a variable in its place,when I run the two the same index is used but with the second the cardinality of the index is reduced and performance greatly degraded (over 20times slower).

Without having to write this as dynamic SQL how to I get Sybase 12.5.3 to choose the correct keys?

With both queries passing having the business_date as a variable value
---
declare @business_date datetime, @r int
select @business_date ='30may2008'
select @r=2384
 SELECT count(1)  from A a  where  a.r = 2384
               and a.business_date = @business_date
 SELECT count(1)  from A a  where  a.r= @r
                and a.business_date = @business_date

1st Query plan where Literal r INT is used:, it correctly uses the first  two of 4 clustered index keys
------
  STEP 1
        The type of query is SELECT.
        FROM TABLE
            A
            a
        Nested iteration.
        Using Clustered Index.
        Index : A_pk
        Forward scan.
        Positioning by key.
        Keys are:
            business_date  ASC
            r  ASC
        Executed in parallel with a 5-way hash scan.
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
------
2nd Query plan where variable r INT is used, where variable set to integer value: it incorrectly uses the first only the first of 4 clustered index keys
----
    STEP 1
        The type of query is SELECT.
        FROM TABLE
            A
            a
        Nested iteration.
        Using Clustered Index.
        Index : A_pk
        Forward scan.
        Positioning by key.
        Keys are:
            business_date  ASC
        Executed in parallel with a 5-way hash scan.
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
-----


KiwiTimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KiwiTimAuthor Commented:
Found the solution: r is smallint not int as my variable was declared:
 And I found the following in Sybooks
----
Datatypes for parameters and variables used as SARGs
When declaring datatypes for variables or stored procedure parameters to be
used as search arguments, match the datatype of the column in the variable or
parameter declaration to ensure the use of an index.
---
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.