Table filtered for multiple column: eav support table can be the best solution for search performance improvement?

1 vote
1 answer
168 views
sql-server database-design index-tuning eav
                          I have **a table with 10~15 columns** (some varchar, some int, some double, and some date). **Table can contain tens of millions of records.**

**User can filter from all columns using all filter operator** (equal, start with, contains, ends with, major, minor..) and setting multiple filters (ex. Filter for column1 and column2 and column5 and column 8)

**I would like to implement a solution who optimize the search response having a good compromise for insert performance.**

**Solution1**:
Create an index for each column.
I think is not the best solution because it will compromise the insert performance and not allow to cover all combination of filters.

**Solution2**:
Using an EAV support table who contains the column data allowed to filter.

    CREATE TABLE FlatTable
    (
       TableId UNIQUEIDENTIFIER NOT NULL,
       Column1 VARCHAR(64),
       Column2 INTEGER,
       Column3 DATETIME,
       Column4 FLOAT,
       ...
       Column10 VARCHAR(512),
       CONSTRAINT PK_FlatTable PRIMARY KEY (TableId)
    )
    
    CREATE TABLE EavTable
    (
       EavTableId UNIQUEIDENTIFIER NOT NULL,
       TableId UNIQUEIDENTIFIER NOT NULL,
       PropertyName VARCHAR (512) NOT NULL,
       StringValue VARCHAR (512), 
       StringValueReverse AS REVERSE(StringValue), 
       StringValueFullText VARCHAR(MAX),
       NumericValue MONEY, 
       DateValue DATETIME,
       CONSTRAINT PK_EavTable PRIMARY KEY (EavTableId),
       CONSTRAINT FK_EavTable FOREIGN KEY (TableId) REFERENCES FlatTable (TableId) ON DELETE CASCADE
    )
    CREATE UNIQUE INDEX UX_EavTable ON EavTable (TableId, StringValue, PropertyName)
    CREATE UNIQUE INDEX UX2_EavTable ON EavTable (TableId, StringValueReverse, PropertyName)
    CREATE UNIQUE INDEX UX3_EavTable ON EavTable (TableId, NumericValue, PropertyName)
    CREATE UNIQUE INDEX UX4_EavTable ON EavTable (TableId, DateValue, PropertyName)
    CREATE FULLTEXT INDEX ON EavTable(StringValueFullText)
       KEY INDEX PK_EavTable
       WITH STOPLIST = OFF;


**Remarks**:  
*StringValueReverse* column is used to filter string values with "ends with operator" allow to resolve the query with a like 'reverseValue%' filter and perform and index seek on index UX2_EavTable.

*StringValueFullText* column contains string value splitted on multiple words allow to use fulltext index on some circumstances when filter operator is "contains".

*NumericValue* column contains the numeric value of the property if it is a number allow to perform and index seek on index UX3_EavTable

*DateValue* column contains the datetime value of the property if it is a date allownto perform and index seek on index  UX4_EavTable

**With this schema i can filter on multiple columns using multiple EXISTS conditions with INDEX SEEK on all filters** (except some case filtering with contains filter)

For example:

    SELECT *
      FROM FlatTable
     WHERE EXISTS (SELECT *
                     FROM EavTable
                    WHERE TableId = FlatTable.TableId
                           AND PropertyName = 'Property1'
                          AND StringValue LIKE 'value%'
                  )
                  AND EXISTS (SELECT *
                                             FROM EavTable
                        WHERE TableId = FlatTable.TableId
                              AND PropertyName = 'Property2'
                              AND StringValueReverse LIKE 'eulav%'
                  )
                  AND EXISTS (SELECT *
                                             FROM EavTable
                        WHERE TableId = FlatTable.TableId
                              AND PropertyName = 'Property3'
                              AND CONTAINS(StringValueFullText, '"value*'")
                  )
                 AND EXISTS (SELECT *
                         FROM EavTable
                        WHERE TableId = FlatTable.TableId
                              AND PropertyName = 'Property4'
                              AND NumericValue > 100
                  )
    AND EXISTS (SELECT *
                         FROM EavTable
                        WHERE TableId = FlatTable.TableId
                              AND PropertyName = 'Property5'
                              AND DateValue > '20240101'
                  )


How you evaluate this solution?  

There is a better one?  

Thanks
                        
Asked by Claudio (11 rep)
Dec 25, 2023, 07:27 PM
Last activity: Jul 26, 2025, 10:03 AM
Table filtered for multiple column: eav support table can be the best solution for search performance improvement?

Related Questions
