SB2055 SB2055 - 7 months ago 72
SQL Question

Should I Index my TenantId column?

I'm using Entity Framework to implement multi-tenant (same db, same schema):

public class Report : ITenantEntity
public Report() { }
public int ReportId { get; set; }
public int TenantId { get; set; }
public DateTime DateSent { get; set; }

I don't have a
POCO, just static IDs.

Should I index on TenantId somehow? All queries now involve filtering on TenantId, so I want to make sure I'm not killing performance by not having proper indexes.

olk olk

Of course as well as you filter your data on tenants, existence of appropriate index is critical for performance. Further depending on database loading and database maintenance procedure could be useful to consider additional optimization measures: make some index clustered, use partitioned tables, use federated database servers etc.

At some moment you might make tenants isolated at the database level and can use parametric views and stored procedures to isolate physical and presentation levels of database. Anyway, optimization of production database lays far away from EF auto-generated database scheme.