EF SQL query Performance on comma-separated string Ids-CodePudding

To store and query on associated IDs, which method works best in terms of query speed and performance? Especially in a large number of records in primary table, such as 100,000 records.

1- Use the comma-separated string field as Ids :

query.Where(x => (','   x.Ids   ',').Contains(','   value   ','));

2- Use a separate table and join operation :

query.Where(x => x.SecondaryTable.Any(s => s.Id == value));

CodePudding user response：

The first option performs a string comparison on a dynamically-generated string, so there is no possibility of an index improving query performance. It will be exceptionally slow.

The second option is performing a numeric comparison on an indexed primary key (assuming that Id is some numeric type and that it is the primary key). This is a much faster comparison for your processor to evaluate, and it is a simple comparison without generating dynamic data for said comparison.

CodePudding user response：

If you store Ids as comma separated string - you always have TABLE/INDEX scan. If your table is small it can be enough.

With SecondaryTable table which stores Ids associated with main table there a lot of other plans:

You can leave as is and trust or not DB Engine optimiser

query = query.Where(x => x.SecondaryTable.Any(s => s.Id == value));

If pair (MainId, Id) is unique. The following query should definitely hit index

var query = 
   from m in query
   from s in m.SecondaryTable.Where(s => s.Id == value)
   select s;

If pair (MainId, Id) is NOT unique.

var secondary = db.SecondaryTable.Where(s => s.Id == value);
var mainIds = secondary.Select(s => new { s.MainId }).Distinct();

query = 
   from m in query
   from s in mainIds.Where(s => s.MainId == m.Id)
   select m;

Anyway, better to test and check execution plan.