SQL Query Performance
I already used a number of times queries in my custom applications/reports that use the CRM tables instead of the Filtered Views. I know this is not the supported way to go, but sometimes it is just more performant to use the tables directly.
Although using the CRM tables instead of the views can give already a performance boost (escpecially because you bypass the security), sometimes the performance could still be better.
These are some simple tuning tips I collected during my 'SQL Query Journey' with thanks to the SQL Server community:
Don't return unnecessary columns. Returning too many columns (select * from) can have a negative impact on performance. So only return the columns you really need.
Use the LIKE operator with caution. Like with the value enclosed in wildcards ("%...%") almost always causes a table scan.
Also negative operations (<> or NOT LIKE) are not very efficient. If you are only checking for existence, use the IF EXISTS or the IF NOT EXISTS construct instead.
Pay attention when using indexes. Indexing is good and necessary, but having too many indexes will impact your insert and update statemants.
You will have to find the correct balance between the indexing needs of the reads and writes.
Do Not begin your custom stored procedure names with "sp_".
SQL Server always looks in the master database first for a stored procedure that begins with the sp_ prefix. This occurs even if you qualify the stored procedure with the database name. So use your own custom naming convention.
Optimize the Application Before Scaling Up or Scaling Out.
Of course there are tons of other tuning tips, but these can already get you on your way.
Although using the CRM tables instead of the views can give already a performance boost (escpecially because you bypass the security), sometimes the performance could still be better.
These are some simple tuning tips I collected during my 'SQL Query Journey' with thanks to the SQL Server community:
Don't return unnecessary columns. Returning too many columns (select * from) can have a negative impact on performance. So only return the columns you really need.
Use the LIKE operator with caution. Like with the value enclosed in wildcards ("%...%") almost always causes a table scan.
Also negative operations (<> or NOT LIKE) are not very efficient. If you are only checking for existence, use the IF EXISTS or the IF NOT EXISTS construct instead.
Pay attention when using indexes. Indexing is good and necessary, but having too many indexes will impact your insert and update statemants.
You will have to find the correct balance between the indexing needs of the reads and writes.
Do Not begin your custom stored procedure names with "sp_".
SQL Server always looks in the master database first for a stored procedure that begins with the sp_ prefix. This occurs even if you qualify the stored procedure with the database name. So use your own custom naming convention.
Optimize the Application Before Scaling Up or Scaling Out.
Of course there are tons of other tuning tips, but these can already get you on your way.
Comments
Post a Comment