Index scans while using PreparedStatements

From: Prashanth (anonymous_at_discussions.microsoft.com.NO_SPAM)
Date: 08/02/04 

Date: Mon, 2 Aug 2004 15:15:08 -0700

I have a query that does a 3-table join. The tables involved
are
1. ct_list_item(li_key int, li_code nvarchar(329))
li_key is the primary key
2. ct_list_item_lang(li_key int, lang_code varchar(5),
value nvarchar(64))
li_key and lang_code form a 2-part primary key.
3. ct_list_item_map(list_key int, li_key int, list_level int)
list_key and li_key form a 2-part primary key.
All of these tables have clustered indexes on their primary
keys.
Here's the query:
SELECT map.list_key, map.li_key, lil.value, li.li_code
FROM ct_list_item li
JOIN ct_list_item_map map on map.li_key = li.li_key
JOIN ct_list_item_lang lil on li.li_key = lil.li_key and
lil.lang_code='en'
WHERE map.list_key= 1011
 I am finding that when I run the query using a JDBC
PreparedStatement with bind variables (on lang_code and
list_key), the query performs an index scan over the
clustered index on the ct_list_item_lang's primary key.
However if I run the query using a JDBC Statement without
bind variables, it does an clustered index seek. I am
puzzled as to why there is a difference.

Relevant Pages

  • Inefficient query looping over cursor
    ... ID int (Primary key, clustered index) ... AMOUNT_PAID smallint ...
    (microsoft.public.sqlserver.programming)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... Without having primary key in the table, will it slow down the query that I ... the clustered index on PACKET_TIME, and instead have a clustered index on ...
    (microsoft.public.sqlserver.programming)
  • Re: Speed question
    ... 99% of the time the query above is the one who will be run. ... I do not need this primary key for anything else, I will not query for it. ... have the clustered index (either on PACKET_TIME or on PACKET_CONTRACT, ... > memory the first time and the second time the pages are still in memory. ...
    (microsoft.public.sqlserver.programming)
  • Re: Deleting Duplicate Records based on "Text" column
    ... CONSTRAINT PRIMARY KEY NONCLUSTERED ... This query in QA produces 411 rows that are duplicates, ...
    (microsoft.public.sqlserver.programming)
  • Re: Clustered Index question
    ... PACKET_CONTRACT,PACKET_TIME resulted in the fastest query. ... "Tom Moreau" wrote: ... > Without having primary key in the table, will it slow down the query that I ... > the clustered index on PACKET_TIME, and instead have a clustered index on ...
    (microsoft.public.sqlserver.programming)
Loading