1) Query Analysis
- If you use LINQ to SQL in EF Core, you can log SQL query from LINQ to query command and try to run it on SQL Tool Query to check how performance based on execution plan.
- Use monitoring tool when you need to check how system is working and find what is your bottle neck.
- Consider to log query to file or database log.
- Some tools can help you to check benchmark, for example in .NET https://benchmarkdotnet.org/index.htm
2) Effective Query
- No Select * , Just get what we want.
- Use paging when you need get a large number of result.
var blogs25 = context.Posts .Where(p => p.Title.StartsWith(“A”)) .Take(25) .ToList(); - Consider to use Lazy Load, but sometime Eager loading is still a best option. For example in case you need get data join from multiple tables.
- Create Index for columns : Only for foreign key, with simple data type for searching.
- You may combine “ID INT” for primary key và GUID for global ID, because if you use GUID for primary key, it can make size of table is bigger and lower performance.
- Consider to build non-cluster index on foreign key without strategy plan because size of tables will bigger (it need to create B-Tree Pointer).
- Don’t use To List everywhere, because it will take data into memory for buffer. If you only need iterator and query, just use IQueryable or IEnumrable is enough.
- Sometime, you can use RawQuery directly instead of Repository layer.
- Asynchronous Programming is effective, especially when you need to call I/O, Network.
3) Update data
- Instead of updating database for each record one by one, we can use Batching/Bulk update to reduce roundtrip to database.
- Use IUnit Of Work pattern to update database in transaction.
4) Modeling data
- Consider to use Star / Snow Flex Schema to model data table.
- Consider to denormalize database to reduce join multiple tables.
- Use IsDeleted column to mark delete state instead of deleting it. Deleting data from table can make inconsistency data state if some tables has foreign key to other tables.
- Consider to use NOSQL to save un-structure data like log, history, audit …
- Consider use append-only table to save event source data.
- Consider Read-Model pattern when your system huge of read request.
