Avoid full table scan

Question

I have an SQL select query to be tuned. In the query there is a View in from clause which has been formed through 4 tables. When this query is executed Full table scan takes place on all these 4 tables which causes CPU spikes. The four tables have valid indexes built on them.

The query looks similar to this:

SELECT DISTINCT ID, TITLE,...... 
FROM FINDSCHEDULEDTESTCASE 
WHERE STEP_PASS_INDEX = 1  AND LOWER(COMPAREANAME) ='abc'   ORDER BY ID;

The dots indicate that there are many more columns. Here FINDSCHEDULEDTESTCASE is a view on four tables.

Can someone guide me how to avoid full table scan on those four tables.

Can you please post your table structure of the underlying tables, as well as the query for the view, and what indexes you've placed on the tables, as well as the query plan for your query? There can be any number of reasons full table scans are happening. Also, what DBMS are you using? — N West, Jul 04 '12 at 16:07
And have you got a case sensitive collation? If not, there is no point calling `LOWER` on `COMPAREANAME` before comparing it to `abc`. — Bridge, Jul 04 '12 at 16:21

score 2 · Answer 1 · answered Jul 04 '12 at 20:32

2

In any case using your condition

AND LOWER(COMPAREANAME) ='abc'

you'll have the full scan of COMPAREANAME values because for each value function LOWER must be calculated.

answered Jul 04 '12 at 20:32

Ivan

51
3

score 1 · Answer 2 · edited Nov 13 '15 at 22:38

It depends on so many things!

SELECT DISTINCTG ID, TITLE, ......

Depending on how many columns you SELECT, it is possible that SQL Server decides to do a table scan instead of using your indexes.

Also, depending on your "WHERE" conditions, SQL Server can also decides to do a table scan instead of using your indexes.

Which version of SQL Server are you using?

There can be ways to improve the indexes on the tables, if, for an example, the conditions in the "WHERE" represents less than 50% of the rows, and if you are using SQL 2008. (With filtered indexes http://msdn.microsoft.com/en-us/library/ms188783.aspx )

Or you can create indexes on views (http://msdn.microsoft.com/en-us/library/ms191432.aspx )

There really is not enough details in your question to be able to really help you.

Avoid full table scan

2 Answers2

Linked