Well, I guess there are a few questions you can ask about your query:
1. Are you certain you isolated the proper query from the app?
2. Is the app using dynamic sql to generate the where clause, so perhaps the query is slightly different?
3. Are the statistics for the table up to date?
4. Is the query returning more than 10% of the total rows in the table? (the optimize will generally do a table scan if more than 10-15% of the records are returned)

If you had access to the source (I understand that you do not) you could just add a table hint like this:
select X from Table_Y with (index(IX_TableY_ColumnZ))

Kevin Martin
Database Engineer
Multimedia Games, Inc.

-----Original Message-----
From: Lee, David [mailto:David.[Email Address Removed] Thursday, April 29, 2004 9:26 AM
To: LazyDBA.com Discussion
Subject: query not using new index


I have been tasked with improving the performance of a query which is part of a Delphi program to which I do not have the source. Using profile while the program is running I identified the query and copied it to the Query Analyser to work on it.

By creating an index on a certain field I managed to reduce the time in QA from 58secs to 120ms. However, when I created the new index on the server and ran the executable exactly as before but it does not use the new index.

How do I make the query use the new index?

thanks in advance

David Lee
DBA
Carlson Marketing


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.672 / Virus Database: 434 - Release Date: 4/28/2004





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.672 / Virus Database: 434 - Release Date: 4/28/2004

MS Sql Server LazyDBA home page