Prepared statement doing table scan

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Prepared statement doing table scan

Author

Topic

Rashmidharshan
Starting Member

2 Posts

Posted - 2009-10-09 : 01:57:09

Hi All,

I have a prepared statement which i execute through jdbc, it is a simple
select statement with a few parameters in the IN clause. The execution of this
prepared statement is taking 3 seconds but if i execute the same query
directly through jdbc itself it returns results immediately in fraction of a
second.

The query is like
Select info from MYTABLE
where gid IN (?,?,?,?,....)

The table i'm querying for has nonclustered unique index on gid and
clustered index on 2 date fields

I saw the execution plan for the direct execution and prepared execution.
Direct execution does an index seek while prepared execution is doing a
table scan.

I'm using SQLServer 2005.

Is there a way to prevent prepared queries from doing table scan when
indexes are present?

Since these prepared statements are generated by our application using WITH (INDEX = index_on_gid) is not an option for me

Rashmi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-09 : 04:26:08

I have no clue about that java stuff.
But I have heard something like this in our company.
The solution was to configure on the java-side (jdbc or ... don't know) because of the following:
The searchvalue was given to the SQL Server in a wrong datatype so the index could not be used.

Maybe that helps.

No, you're never too old to Yak'n'Roll if you're too young to die.

Rashmidharshan
Starting Member

2 Posts

Posted - 2009-10-10 : 00:38:49

Yep you are right there is jdbc parameter to be toggled.

The table scans were occuring because the jdbc was passing the parameters as nVarchar but the gid was of type varchar. Hence the optimiser decides to do a table scan

See
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?cat=&lang=&cr=&guid=&sloc=en-us&dg=microsoft.public.sqlserver.server&tid=75ed2c06-dbfd-45be-84f6-2551f011978a&mid=94511961-1663-4925-bc24-9711574943b7&amid=94511961-1663-4925-bc24-9711574943b7&answer=1&p=1&stgxml=http://www.microsoft.com/communities/newsgroups/en-us/settings.xml

quote:
Originally posted by webfred

I have no clue about that java stuff.
But I have heard something like this in our company.
The solution was to configure on the java-side (jdbc or ... don't know) because of the following:
The searchvalue was given to the SQL Server in a wrong datatype so the index could not be used.

Maybe that helps.

No, you're never too old to Yak'n'Roll if you're too young to die.

Rashmi

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-10 : 06:01:32

Glad that I could help!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources