1

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

5
  • Why do you think maintaining the EAV table is going to be more performant than maintaining normal indexes? Dec 25, 2023 at 22:06
  • This is a good question: maintaining a normal table with a lot of indexes is better than mantaining a EAV table?
    – Claudio
    Dec 25, 2023 at 22:58
  • IMHO EAV offer some benefit over string search operator who a simple table column filter does not: i can apply operator "ends with" or "contains" resulting in an index seek with some hacks showed in the above sample (using dedicated columns: StringValueReverse / StringValueFullText)
    – Claudio
    Dec 25, 2023 at 23:06
  • I honestly can't see how it's going to be more performant to maintain 15 extra rows per main row. The hacks can be applied to the main table in the same way if need be, and you can use multi-column indexes to help common queries. Dec 26, 2023 at 1:23
  • Without EAV, how many indexes i need to create to cover all combinations of 15 column filters? Insert in table so many indexed is more fast than insert additional 15 rows on a EAV table? There are other solutions? Unfortunately my requirements is allow user to filter for all this columns.
    – Claudio
    Dec 27, 2023 at 17:41

1 Answer 1

0

Just like your EAV table, indexes in SQL Server are a copy of a subset of data in a table, organized in a way suitable for seeking.

Unless you intend to update your EAV table asychronously with respect to inserts (unlike with deletes, that you are cascading synchronously), you will be able to cover all combinations of filters, while still attaining a much better performance for both INSERTs and complex SELECTs than what the EAV table would give you, by combining indexes with computed columns.

Also, you should ensure that your FlatTable has a clustered index.

If TableId values are to be autogenerated

This is an example of combining conditions on multiple columns:

CREATE TABLE FlatTable
(
   TableId UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, -- clustered index
   -- ...
   Column3 DATETIME,
   -- ...
   Column10 VARCHAR(512),
   Column10Reversed as REVERSE(Column10) -- computed column, stored only on index
);
CREATE NONCLUSTERED INDEX IX_FlatTable_Column3 ON FlatTable (Column3);
CREATE NONCLUSTERED INDEX IX_FlatTable_Column10 ON FlatTable (Column10);
CREATE NONCLUSTERED INDEX IX_FlatTable_Column10Reversed ON FlatTable(Column10Reversed);

SELECT * 
FROM FlatTable
WHERE Column3 > '20240101' 
    AND Column10Reversed LIKE 'eulav%'
2
  • the thing that worries me on the solution with a flat table and many indexes is the big amount of indexes it require to cover all the combination of filters. Assume i have a table with 17 filterable columns. 7 columns of this table are varchar so i need to allow like operator on it, to optimize search in this columns i create 2 computed columns for each of it: one for reverse value (used for ends with operator), and one for fulltext search (used for contains operator). So the result is 17 + (7 * 2) = 31 filterable columns who need to be indexed.
    – Claudio
    Jan 9 at 12:51
  • How many index i need to add to the flat table to cover all search combinations? and how it affect the insert performance? With an EAV table i cover all filter combinations with only 5 index on the eav table. The drawback of EAV is that inserting a record on flat table require to add more 17 rows on EAV table. In summary i need an optimized way to manage filter on many columns with all filter combinations taking into account also the insert performance.
    – Claudio
    Jan 9 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.