about covering indexes
-
Hi everyone,
I have a problem in query execution.
I know covering indexes can contain max. 16 columns.
In my case, I have a query, which takes data from view (View consists of 50 columns, joins and so on). When I execute that query, it shows Index scan in one table(this table consists of more than 2 millions records).
I tried to remove index scan ( which takes alot of logical records ), creating indexe but it again leads bookmark lookups. I know i need to create covering index to remove such bookmarks. But i am unable to cover all columns(because this query consists of 25 columns from that table).
How can remove such index scans, bookmark lookups ?
Let me know if you want any information.
ASAP
Thanks
AKP
-
A 25 column covering index just might do it
... but I'm thinking the view is written incorrectly. Do you have calculations on the columns in the WHERE or ON clauses? If so, that could certainly cause the problems you speak of...--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
How many of the 50 columns are you querying for and how many rows are you getting. That combined with your run time will tell us more.
Run this statement with your query and post the output message:
set statistics IO on
-
Index should cover columns used in WHERE clause, not in SELECT list.
_____________
Code for TallyGenerator -
When I create a index including all columns of where clause, it shows bookmark Lookups with index seek. But this also becomes costly for query execution. What can I do ?
thanks
AKP
-
You rarely need an index for more than few columns. For index to be efficient, it must assure at least partial selectivity, best on most selective column.
For example, you have 1 mil. rows (not necessarily a single table), the most selective column in where selects them 2000, full where selects them 200. In such case it's most efficient to have index on "the most selective column", as sequential scan of rowset based on this index of more efficient than having a huge 25 column index.
Besides, with 25 column index you'd have to always include first index column in where and hope in would be used.
-
Ther is apparently some confusion about a covering index. Indeed, the select list should be covered by such an index. Usually the first term is the PK or specifically designed to be accessed in the where clause, and the covered columns follow.
From the BOL:
"Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone."
The limit of 16 columns in an index makes the use of a covering index for more than 16 columns not an option in SQL 2000.
In SQL 2005, you can include non-indexed columns that will be stored in the leaf level of the index's B-Tree, which is actually an improvement over using regular indexes to achieve the desired effect.
It may be possible to create two covering indexes and join the table twice, picking the appropriate set of columns from each instance of the table, but I don't have any experience doing anything like that.
jg
-
Well put Jeff.
I think to help this chap we need the follow:
* the query
* current run time
* desired run time
* the amount of data being returned.
* the output of from set statistics I/O ON
I'm guessing the chap is new to tuning and general advice on indexing isn't helping him.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply