ColdFusion / SQL Server performance issue

[eculpepper]

We are experiencing an issue with ColdFusion MX7, and SQL Server 2000. This
issue is regarding the performance problem that can arise when connecting a
Java application to a SQL Server 2000 database. We found a fix in the
Knowledgebase, however it did not correct the problem that we are experiencing.
The site that we are running has a table with about 27000 records in it which
is accessed every time that a person browses a page on the site (the query is a
unique find). Subsequently the entire site is slow with about 3 to 4 second
response time between page draws. We commented out the reference to the table,
and the site response time is instantaneous.
However this query is required for the site. What can we do to increase the
access time of the site and solve this issue?

[paross1]

What are you selecting from this reference table? Is your query and database
optimized? For instance, are the fields that are referenced in the WHERE clause
or the JOIN indexed, and are you not using any conversion functions on your
joined fields, etc.?

Phil

[eculpepper]

The query is a simple one.



SELECT iKeyID from Table where Table.iKeyID = 1



WHERE iKeyID is the primary unique index. I do not believe that the issue is
with the query. It is with the java sql driver that uses Unicode encode which
makes SQL Server do a table scan. We have told CF to not use Unicode encoding
but I do not believe that it is using that setting.

[Scott_thornton]

Create and execute the query as a stored procedure?

create procedure myname
(
@var integer
)
as
SELECT iKeyID from Table where Table.iKeyID = @var

or use a cfqueryparam tag in the where clause?

SELECT iKeyID from Table where Table.iKeyID = <cfqueryparam
cfsqltype="cf_sql_integer" value="#var#">