Question 85786 in ASP.NET Question posted in
The official documentation can be found here.

Indexes needed for user advanced filter? – Asp.net

ASP.NET

MS SQL table: tblPets

ID (123456789)
AnimalType (Dog, Cat, Goldfish...)
AnimalName (Rudolf, Ben, Harold...)
CountryCode (US, AU..)
StateCode (CA, NY...)
CityCode (AK, LA...)
IsMammal (True, False)
IsFish (True, False)
HasFur (True, False)
Color (Black, Brown, Orange...)
WeightKG (34, 57, 18...)

We are thinking of creating an "Advanced Filter" webpage for users to search/filter for all records that match their user-inputted criteria.

We are considering having a filter popup dialog with 10 text input fields allowing the user to filter on one or more criteria.

E.g.:

CountryCode: US
StateCode: CA
IsFish: True
Color: Orange

Assuming we want the table tblPets as indexed as possible for fastest results, what would the indexes be? E.g., would there need to be 100 indexes (10 x 10)?

Assuming column one is named c1, column two c2 etc:

Index1:
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10

Index2:
c2,c3,c4,c5,c6,c7,c8,c9,c10

Index3:
c3,c4,c5,c6,c7,c8,c9,c10

Index4:
c4,c5,c6,c7,c8,c9,c10

Index5:
c5,c6,c7,c8,c9,c10

Index6:
c6,c7,c8,c9,c10

Index7:
c7,c8,c9,c10

Index8:
c8,c9,c10

Index9:
c9,c10

Index10:
c10

Index1B:
c1,c3,c4,c5,c6,c7,c8,c9,c10

Index2B:
c1,c4,c5,c6,c7,c8,c9,c10

Index3B:
c1,c5,c6,c7,c8,c9,c10

Index4B:
c1,c6,c7,c8,c9,c10

Index5B:
c1,c7,c8,c9,c10

Index6B:
c1,c8,c9,c10

Index7B:
c1,c9,c10

Index8B:
c1,c10

Index1C:
c2,c4,c5,c6,c7,c8,c9,c10

Etc

Tags:

2

Answers


  1. 0

    Yes, you should put indexes on each column that will be used in the where clause. Pay attention that the indexes means slower write/update performance. The indexes will also increase your table size.

    Login or Signup to reply.
  2. 0

    In this case the best way is to create a CLUSTERED COLUMNSTORE index on the table.

    If the table has already a CLUTERED index (usually PRIMARY KEY) dropt the PK constraint and create a new PK with the same columns in a NONCLUSTERED inhdex :

    ALTER TABLE ??? DROP CONSTRAINT PK_???;

ALTER TABLE ??? ADD PRIMARY KEY (???) NONCLUSTERED;

    Then create the CLUSTERED COLUMNSTORE index :

    CREATE CLUSTERED COLUMNSTORE INDEX ON ???;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search