SQL Query not using Index

Hal Levy · August 18th, 2004, 04:27 PM

I have a basic Query:

Code:

select * from EDCARS_Exp_Trans_Detail
where TD_Exp_Status = 'S'

This query takes a long time to run, so I thought to put an Index on the Column. However, even though the Index now exists, SQL Server doesn't use the index.

TD_Exp_Status is a Varchar(1) nulls allowed (I have no idea why it's a Varchar instead of a char at 1..... I inherited this Database). There are 1,372,200 rows in the table- and growing quickly.

Any ideas on how I can get the query time improved?

Thanks,

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Deepesh_Jain · August 19th, 2004, 12:10 AM

If we create an index on a particular column of a table, and when we run any query on that table the sql server started using that index automatically. Here you mentioned that the sql server does not use the Index. How you know this???????

Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

mehdi62b · August 19th, 2004, 07:47 AM

in addition to Indexes,use ORDER BY in ur query.

--------------------------------------------
Mehdi.:)

Hal Levy · August 19th, 2004, 08:20 AM

Deepesh - I know it doesn't use the Index because the Execution Plan says it's doing a Table Scan.

Mehdi - The Full query DOES have an order by. It also does a Table Scan. I am putting here the full query. I narrowed it down for testing.

Code:

SELECT DISTINCT Employee.E_Home_Dept_Prog, E_Payroll_Co, E_Term_Date AS Term_Status, Count(EDCARS_Exp_Trans_Detail.TD_Emp_ID) AS records
FROM EDCARS_Exp_Trans_Detail
WITH (INDEX(td_exp_status))
INNER JOIN EMPLOYEE ON EDCARS_Exp_Trans_Detail.TD_Emp_ID = Employee.E_Emp_ID
WHERE EDCARS_Exp_Trans_Detail.TD_Exp_Paid_Status <> 'P' AND EDCARS_Exp_Trans_Detail.TD_Exp_Status = 'S'
GROUP BY Employee.E_Home_Dept_Prog, E_Payroll_Co, Employee.E_Term_Date
ORDER BY Employee.E_Home_Dept_Prog, E_Payroll_Co

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

bmains · August 19th, 2004, 11:19 AM

Hey,

Two separate fields listed here: WHERE EDCARS_Exp_Trans_Detail.TD_Exp_Paid_Status <> 'P' AND EDCARS_Exp_Trans_Detail.TD_Exp_Status = 'S'

Did you try creating an index for both fields?

Brian

Hal Levy · August 19th, 2004, 11:28 AM

Brian- yes, I tried that. I also tried the Index Tunig Wizard- that didn't suggest any indexes.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

mehdi62b · August 19th, 2004, 11:37 AM

Hal,I had a similar discussion in another forum, have a look at there
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38394
HtH.

--------------------------------------------
Mehdi.:)

Jeff Mason · August 19th, 2004, 12:49 PM

Quote:

quote:Originally posted by Hal Levy
Brian- yes, I tried that. I also tried the Index Tunig Wizard- that didn't suggest any indexes.

The query processor is smarter than you. :D

It will only use an index if it believes that doing so will result in a lower cost query than the other alternatives it considers.

In your case, what I think is going on is that the JOIN is having a stronger influence on the query cost than the WHERE predicate on the indexed column. You said that the column was defined as a varchar(1). Resisting for a moment the temptation to ask what a variable length data column with a maximum length of 1 character could possible mean ;), I would observe that the domain of values for this column is limited. After all, there are only so many distinct values (257? - and I doubt you are using anywhere near this many values in your app) this column could take, so an index on this column would not have much specificity, i.e. there would be many duplicate entries in it. An index with too many duplicates isn't going to be of much use, since the query processor will figure that using the index doesn't buy much - it has to go to the clustered index (data rows) to get the values of the columns specified in the SELECT clause as well as the other column used in the WHERE clause. The system does keep statistics about the distribution of data values for indexes, so this information is readily available. Couple that with the JOIN it needs to do, and I would guess it figured using the index wasn't worth the effort.

Note that the syntax you attempted to use on the FROM clause (WITH (INDEX(td_exp_status)) is called a table hint, not a command. Like I said, the query processor knows better than you, so it is free to choose to ignore any hints you give it, which it apparently did.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

mehdi62b · August 19th, 2004, 02:47 PM

Jeff,I have a question?
if we change the clustered index of primary column(to nonclustered index)so we
could make this column (TD_Exp_Status) as clustered,
whether query processor considers our column to be sorted
correctly regardless of other alternatives?

--------------------------------------------
Mehdi.:)

Hal Levy · August 19th, 2004, 03:54 PM

Jeff- I pasted a "test" version with a hint that I was trying. THe production version doesn't have the hint.

When I hinted at using it- it used it.. with worse results than the Tablescan.

Like I said, I didn't design the database- a VARCHAR(1) isn't my idea of a good design.In fact, I think there are all of 2 possible settings for that field.. If we weren't thinking about replacing the whole system- I'd redesign parts of it...

So I guess the answer is there's no way, with the current table design, to make this run any better.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee