Solved

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

Posted on 2008-10-29
1
551 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Sybase - How to edit cron schedule for sybase backups 3 930
SyBase SQL DataTime Format 6 300
sql anywhere query 5 148
Converting some Sql server syntax to Sybase Syntax. 8 81
Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
We asked our MSP customer base what their favorite tools were and how they help them serve clients. We focused our questions on favorite tools in the following categories: >PSA tools >RMM tools >Alert management tools >Communication tools and Mo…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

751 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