To help improve the performance of a large list or library, you can
index up to 20 columns. In general, an index on a column enables you
to quickly find the rows you want based on the values in that column,
even when working with millions of items. When you combine indexes
with filtered views, you can quickly retrieve the items you want.
It is important to consider the following when you create and use
indexed columns. Each additional column index consumes extra resources
in the database and adds some overhead to every operation to maintain
the index. Therefore, you should add indexes only to columns that will
be used actively for filtering in views on the list or library. It's a
good idea to look at all the views and see which columns are used most
commonly across different views for filtering to help choose the right
columns to be indexed. Note that any column that you define to have a
unique value requires an index.
i know you have indexs some columns but what exactly have you indexed? is it only the one your querying? as noted above the more you index the more toll it takes on the sql server to be indexed within sql server!!
oQuery.QueryThrottleMode = SPQueryThrottleOption.Override;
the reason why iv set that is because its noted here! anything over
5000 items are throttled:
By default, the list view threshold prevents operations that will
involve more than 5,000 items, such as queries that will return more
than 5,000 items or adding a column to a list that contains more than
5,000 items. Although this is a configurable default, we strongly
recommend that you keep it. If poorly performing queries are used on
lists with more than 5,000 items, overall throughput can significantly
decrease when you increase this limit.
Some operations, such as non-indexed queries or adding a column to a
list, take time and resources that are proportional to the number of
items in the list. On a small list this does not matter because there
are so few items that the operation is fast. As the list size
increases, these operations take longer and use more resources. Rather
than let these operations run unbounded, the list view threshold
blocks them. You can think of the list view threshold as a guard rail
along a highway letting you know that you should change the query and
how data is accessed, or you should perform the operation when farm
usage is low.
The list view threshold is the maximum number of list or library items
that a database operation, such as a query, can involve at one time.
By default, this is set to 5,000 items. This limit has a major effect on large lists because, by the definition of this threshold, a
large list is a list that has more items than this limit. Operations
that exceed this limit are throttled. Operations, such as creating an
index on a list that is over this limit, are prevented because the
operation affects more than 5,000 items. This limit prevents the
queries that have a selectivity (items that can be efficiently
filtered by using filter criteria) of more than 5,000 items. This
limit also prevents queries that filter on columns that are not
indexed. This is because a query that filters (and in some cases
sorts) on a column that is not indexed must perform the filter on all
items in the list to retrieve the correct dataset, and it will operate
on more items than the list view threshold. The default value for this
limit is based on farm and list performance and on how SQL Server
manages locks. We recommend that this limit not be changed.
there is a good article on indexing ill post at the bottom! as for you the issue lies with the ammount of data within the list (columns and rows). Say you index 1 column for 170k items within a given list.... in sql server thats 170k rows aswell as 170 items stored. Thats a lot ;). As for your query its correct, the fact that you tried to optimize it aswell by using ViewFields is also a bonus returning id, name and size but its still a major issue with the ammount of items being quieried from the sql database.
when you query its still querying the whole 170k records regardless if you index or not.
so you have two options! 1 optimize your query even further by adding value type to each fieldref:
or directly call the table in sql as that is the fastest option available using a stored procedure ;), should be able to do it under a couple of seconds....reason being your skipping the middle man and going directly to the source! have a stored procedure taking in a lookup ID and returning the three values :)
you should follow this desgin when using massive lists:
as a side note when microsoft state building list within 1million thats becasue its within folder or groups rather than put in one list. for each list its threshold is 5k but you could have 5k list of folders with 5k within each one ;). Having them within folders or groups makes search queries faster and lets not forgetting loading the page.