[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-10-29
1
Medium Priority
?
567 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The following article sheds light on easy-to-use steps to recover non-responding hard drives without data loss. Count on these approaches to fix undetectable, not responding, or non-working hard drives.
After a recent Outlook migration from a 2007 to 2010 environment, some issues with Distribution List owners were realized. In this article, I explain how that was rectified.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

591 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