Database Performance Tip # 9
- Do not use “select *…†or “insert into dbo.Table select…†(without explicit column list). Explicit column lists reduce disk I/O (no need to read system tables to identify columns), and are more maintainable when columns are added or re-positioned
- If a cursor is unavoidable, use a simple WHILE loop instead, to loop through the table, its fast.
- Avoid searching with not equals operators (<> and NOT) as they result in table and index scans.
- Always access tables in the same order in all your stored procedures/triggers consistently. This helps in avoiding deadlocks. Other things to keep in mind to avoid deadlocks are keep your transactions as short as possible.
- Do not prefix your stored procedure names with ‘sp_’. The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_,, it first tries to locate the procedure in the master database, then looks for any qualifiers (database, owner) provided, then using dbo as the owner. So, you can really save time in locating the stored procedure by avoiding sp_ prefix. But there is an exception! While creating general purpose stored procedures that are called from all your databases, go ahead and prefix those stored procedure names with sp_ and create them in the master database
- Consider dropping the indexes on a table before loading a large batch of data. This makes the insert statements run faster. Once the inserts are completed, you can recreate the index again. if you are inserting thousands of rows in an online system, use a temporary table to load data. Ensure that this temporary table does not have any index. Since moving data from one table to another is much faster than loading from an external source, you can now drop indexes on your primary table, move data from temporary to final table, and finally recreate the indexes.
- Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.
- Do not use the column numbers in the ORDER BY clause as it impairs the readability of the SQL statement. Further, changing the order of columns in the SELECT list has no impact on the ORDER BY when the columns are referred by names instead of numbers.
This post was viewed (48) times.
Who Moved My Cheese? IP Spoofing in LoadRunner Controller