2

I have an ASP.Net MVC application & I use PetaPoco and SQL Server.

My usecase is I want to allow a search on a table with many fields, but hide fields that are "slow" (ie) unindexed. I'm going to modify the PetaPoco T4 template to decorate this information on the columns.

I found this answer that gives you a list of tables vs indexes. My concern is it shows a lot of columns for a particular table. Is the query given in the answer reliable for my usecase ? (ie) can the columns shown be included in the where clause & it wont be slow ? I have some tables that have 40M rows. I dont want to include slow columns in the where condition.

Or is there a better way to solve this problem ?

Community
  • 1
  • 1
MD Luffy
  • 536
  • 6
  • 18

2 Answers2

1

There are no slow columns in the sense of your question. You have to distinguish between two uses of a column.

  1. Searching. When the column appears in the WHERE, or JOIN clause, it slows down your query, if there is no index for it.

  2. Returning in recordset. If the column appears in the SELECT clause, its content must be returned with each row, whether you need it, or not. So for queries returning many rows, each additional column to be returned means a performance penalty.

Conclusion: As you can see, the performance impact of SELECTED columns does NOT DEPEND on index, but on the number of the returned rows.

Advice: Create indexes for columns used to search and do not return unnecessary columns. Let your queries be as specific as possible in terms of both, selected columns and returned rows.

Thinkeye
  • 888
  • 12
  • 22
1

I think it will not be that simple. You can check indexed columns using the suggested approach (or similar), but the fact that a column is present in an index does not mean your query will necessarily utilize it efficiently. For example if an index is created on columns A, B and C (in that order) and you only have a 'WHERE' clause on B or C (but not on A) you will probably end up with index scan rather than index seek and your query is likely to be slower than expected.

So your check should take into account the sequence of the columns in the indices - instantly fast columns (in your situation) might probably be considered the first columns of the indices (where ic.index_column_id = 1 in the post you mentioned). Columns that are not first in the indices (i.e. ic.index_column_id > 1) will be fast as long as the first columns are also included in the filter. There are other things you might also need to take into account (e.g. cardinality), but this is important to make sure you drive index seeks rather than scans.

Dimitar Kyuchukov
  • 310
  • 1
  • 8