Trades-SQL Optimization Tips
* Use views and stored procedures instead of heavy queries.
This can reduce network traffic because your client sends to server only stored procedure or view name (perhaps some parameters) instead of the big heavy text queries. It can be used to facilitate permission management also, because you want a user to access the table columns may be reduced to not see it.
* Try to use constraints instead of triggers, whenever possible.
Restrictions are much more efficient than triggers and can increase efficiency. Therefore, you should use constraints instead of triggers, whenever possible.
* Use the table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, table variables that should be used whenever possible. Table, the variables are available in SQL Server 2000.
* Try to use the EU countries, the statement instead of UNION, whenever possible.
Union All statement is much faster than the EU, the Union because the petition does not look all Duplicate rows and the Duplicate rows looking statement, either does not exist.
* Try to avoid using a different clause, whenever possible.
Since there are different results in a deterioration in the performance of the clause, this clause is used only when necessary.
* Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in performance compared to select the statements. Try to use correlated sub-query or derived tables, if the conduct line-by-the-line activities.
* Try to avoid the clause does not apply, if possible.
After the section is used to restrict the result set returned by GROUP BY clause. If you use a GROUP BY clause does not apply to shares the GROUP BY clause groups rows grouped rows and aggregates their values, and then point to prevent undesired aggregated groups. In many cases, you can write your select statement is that only where and GROUP BY clauses without notice. It can improve your query.
* If the number of back row table can be used in another way instead of SELECT COUNT (*) statement.
Because SELECT COUNT (*) certificate to every table full table scan line number back, a very large table in time. There is another way to determine the number of rows in the table. You can use sysindexes system table, in this case. Not ROWS column in the table sysindexes. This column contains the row number for each table in a database. So, you can use the following select statement instead of SELECT COUNT (*): SELECT rows from sysindexes WHERE id = OBJECT_ID (TABLENAME ") and the Indies <2 so you can improve the speed of these issues at different times.
* Include the SET NOCOUNT ON statement on its stored procedures to stop the message the number of rows affected by T-SQL statement.
This can reduce network traffic, because your customers will not notice, specifying the number of rows affected by T-SQL to obtain.
* Try to limit the number of questions about the outcome of using the WHERE clause.
This can result in good performance benefits because SQL Server returns the client only particular rows, not all the rows in the table (s). This can reduce network traffic and to increase the overall performance of the query.
* Use the comments SELECT TOP keyword or the SET rowcount statement, if only the first n rows back.
It can improve your questions, since a smaller number of results returned. It may also reduce the traffic, the server and clients.
* Try to limit the number of questions about the result, returning only the particular columns of the table, not all of the columns of the table.
This can result in good performance benefits because SQL Server returns to the customer but, above all columns, not all of the columns of the table. This can reduce network traffic and to increase the overall performance of the query.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|