Solved

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

Posted on 2008-10-29
1
542 Views
Last Modified: 2012-05-05
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.
-----


0
Comment
Question by:KiwiTim
1 Comment
 

Accepted Solution

by:
KiwiTim earned 0 total points
ID: 22830224
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A customer recently asked me about anti-malware and the different deployment options available for his business. Daily news about cyberattacks, zero-day vulnerabilities, and companies that suffered a security breach made him wonder if the endpoint a…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now