As a developer you may or may not need to go into the database and write queries or design tables and indexes, or help determine configuration of your SQL Server systems. But if you do, these tips should help to make that a more pain free process.
a) Since the code generated from the ORM can frequently be ad hoc, ensure that the SQL Server instance has ‘Optimize for Ad Hoc’ enabled. This will store a plan stub in memory the first time a query is passed, rather than storing a full plan. This can help with memory management.
b) Ensure your variables and parameters are the same data types as the columns. An implicit or explicit conversion can lead to table scans and slow performance.
c) You get exactly one clustered index on a table. Ensure you have it in the right place. First choice is the most frequently accessed column, which may or may not be the primary key. Second choice is a column that structures the storage in a way that helps performance. This is a must for partitioning data.
d) Performance is enhanced when indexes are placed on columns used in WHERE, JOIN, ORDER BY, GROUP, and TOP. Always test to ensure that the index does help performance.
Hope above tips were helpful :)
a) Since the code generated from the ORM can frequently be ad hoc, ensure that the SQL Server instance has ‘Optimize for Ad Hoc’ enabled. This will store a plan stub in memory the first time a query is passed, rather than storing a full plan. This can help with memory management.
b) Ensure your variables and parameters are the same data types as the columns. An implicit or explicit conversion can lead to table scans and slow performance.
c) You get exactly one clustered index on a table. Ensure you have it in the right place. First choice is the most frequently accessed column, which may or may not be the primary key. Second choice is a column that structures the storage in a way that helps performance. This is a must for partitioning data.
d) Performance is enhanced when indexes are placed on columns used in WHERE, JOIN, ORDER BY, GROUP, and TOP. Always test to ensure that the index does help performance.
Hope above tips were helpful :)
There should be some best practices we need to follow while dealing with ORM(EF, Hibernate or any other). Also we need to follow the design guidelines to design database as well specially for performance optimization.
ReplyDeleteCould you please highlight any best practices on those stuff?
Pradyumna
Definitely, I'll cover that in my upcoming posts
Delete