Don’t use the * in your queries. A SELECT * creates a overload on table, Input/Output and network bandwidth.
All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear on index.
Avoid VIEWs. Use them only when there are benefits of doing so.
Verify if a critical query gains performance by turning it in a stored procedure.
Avoid too much JOINs on your query: use only what is necessary!
Avoid cursors at all costs!
If a query is slow and your index is not being used by it (remember to check your execution plan), you can force it using WITH(INDEX=index_name), right after the table declaration on the FROM clause.
Use EXISTS or NOT EXISTS instead of IN or NOT IN. IN operators creates a overload on database.
Try to use BETWEEN instead of IN, too.
When using LIKE operator, try to leave the wildcards on the right side of the VARCHAR.
Always avoid to use functions on your queries. SUBSTRING is your enemy. Try to use LIKE instead.
Sometimes is better to make various queries with UNION ALL than a unique query with too much OR operations on WHERE clause. Test it.
When there is a HAVING clause, it is better to filter most results on the WHERE clause and use HAVING only for what it is necessary.
Use more variable tables and less temporary tables.
Use functions to reuse code. But don’t exaggerate on using them!
To delete all rows on a table, use TRUNCATE TABLE statement instead of DELETE.
If you have a IDENTITY primary key and do dozens of simultaneous insertions on in, make it a non-clusterized primary key index to avoid bottlenecks.
Now, some tips for the table structure. Sometimes it is necessary to make some alterations on the table design to extract more performance!
All tables should have a primary key. Except data warehouses and the like sometimes.
All tables should have a clusterized index, normally on the primary key.
Don’t be afraid to create non-clusterized indexes on most tables. Just be sure that you aren’t overindexing your tables! Too much indexes degrades insertions.
If you are creating an index, check the queries that are made against the table. Give preference to index columns that appear on most WHERE and JOIN clauses, and their order!
Avoid TRIGGERs. Use it only as a last resource. It is better to use CONSTRAINTs and STORED PROCEDUREs to mantain the integrity of your databases!
All columns involved in indexes should appear on WHERE and JOIN clauses on the same sequence they appear on index.
Avoid VIEWs. Use them only when there are benefits of doing so.
Verify if a critical query gains performance by turning it in a stored procedure.
Avoid too much JOINs on your query: use only what is necessary!
Avoid cursors at all costs!
If a query is slow and your index is not being used by it (remember to check your execution plan), you can force it using WITH(INDEX=index_name), right after the table declaration on the FROM clause.
Use EXISTS or NOT EXISTS instead of IN or NOT IN. IN operators creates a overload on database.
Try to use BETWEEN instead of IN, too.
When using LIKE operator, try to leave the wildcards on the right side of the VARCHAR.
Always avoid to use functions on your queries. SUBSTRING is your enemy. Try to use LIKE instead.
Sometimes is better to make various queries with UNION ALL than a unique query with too much OR operations on WHERE clause. Test it.
When there is a HAVING clause, it is better to filter most results on the WHERE clause and use HAVING only for what it is necessary.
Use more variable tables and less temporary tables.
Use functions to reuse code. But don’t exaggerate on using them!
To delete all rows on a table, use TRUNCATE TABLE statement instead of DELETE.
If you have a IDENTITY primary key and do dozens of simultaneous insertions on in, make it a non-clusterized primary key index to avoid bottlenecks.
Now, some tips for the table structure. Sometimes it is necessary to make some alterations on the table design to extract more performance!
All tables should have a primary key. Except data warehouses and the like sometimes.
All tables should have a clusterized index, normally on the primary key.
Don’t be afraid to create non-clusterized indexes on most tables. Just be sure that you aren’t overindexing your tables! Too much indexes degrades insertions.
If you are creating an index, check the queries that are made against the table. Give preference to index columns that appear on most WHERE and JOIN clauses, and their order!
Avoid TRIGGERs. Use it only as a last resource. It is better to use CONSTRAINTs and STORED PROCEDUREs to mantain the integrity of your databases!
0 comments:
Post a Comment