Solved

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

Posted on 2008-10-29
1
547 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Giving access to ESXi shell console is always an issue for IT departments to other Teams, or Projects. We need to find a way so that teams can use ESXTOP for their POCs, or tests without giving them the access to ESXi host shell console with a root …
There's a lot of hype surrounding blockchain technology. Here's how it works and some of the novel ways it' s now being used - including for data protection.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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