Improving SQL Performance

Twelve Tips For Optimizing Sql Server 2005 Query Performance

1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level

To increase the speed of SQL SELECT query, you can analyze the following issues: 

·

RequestLive property value

·

Available indexes for conditions from WHERE clause

·

Rewriting a query with OR conditions as a UNION

·

Available indexes for JOIN conditions

·

Available indexes for ORDER BY clause

·

Available indexes for GROUP BY clause

·

Select from in-memory tables

·

SELECT INTO vs INSERT SELECT RequestLive property value  The first important option which can be used for a speed optimization is the TABSQuery.RequestLive property. If selection query is simple and involves a single table only, try to set RequestLive value to True and to False before opening a query. Some queries run faster with theRequestLive=True, others will perform much better with the RequestLive=False. Available indexes for conditions from WHERE clause It is recommended to make sure that optimal indexes for conditions from WHERE clause are available. See the topic "Speeding up Searches and Filters" for more details on how to check search conditions and create appropriate indexes. For example if you would like to get a better performance for the query:  SELECT * FROM customer WHERE City='Kapaa Kauai' AND State='HI'   the best way to speed it up is to create the following case-sensitive index:  ABSTable1.AddIndex('idxCityState', 'City;State', []);   If you need to get a better performance for the query:  SELECT * FROM customer WHERE Upper(City)='KAPAA KAUAI'   the best way to speed it up is to create the following case-insensitive index:  ABSTable1.AddIndex('idxCity_nocase', 'City', [ixCaseInsensitive]);   Available indexes for JOIN conditions To improve a JOIN query, please check that each field from JOIN conditions has an index. For example if you would like to improve the performance of the query:  SELECT Event_Name,Venue FROM Events e JOIN Venues v ON (e.VenueNo = v.VenueNo)   you can create the following indexes:  VenuesTable.AddIndex('idxVenueNo', 'VenueNo', [ixPrimary]);   EventsTable.AddIndex('idxVenueNo', 'VenueNo', []);   Rewriting query with OR conditions as a UNION Absolute DB cannot use indexes to improve performance of a query with OR conditions. You can speedup your query  SELECT * FROM table WHERE (Field1 = 'Value1') OR (Field2 = 'Value2')   by creating indexes on each field in the above conditions and by using a UNION operator instead of using OR: SELECT ... WHERE Field1 = 'Value1'   UNION   SELECT ... WHERE Field2 = 'Value2'   Available indexes for ORDER BY clause If you want to speed up a "live" SELECT from a single table with ORDER BY clause, you can create a compound index for ORDER BY fields. For example if you would like to increase the speed of the query:  SELECT * FROM Employee ORDER BY LastName, FirstName   you can do it by creating the following compound index:  ABSTable1.AddIndex('idxLastNameFirstName', 'LastName;FirstName', []);   Available indexes for GROUP BY clauseTo get a better performance for SELECT from a single table with GROUP BY clause, you can create a compound index for GROUP BY fields. For example if you want to speed up the query:  SELECT * FROM Employee GROUP BY FirstName   you can create the following index:  ABSTable1.AddIndex('idxFirstName', 'FirstName', []);   Select from in-memory tablesYour query perofrmance could be increased also if you will move all data from disk tables to in-memory tables and you will perform a query using in-memory copies of the disk tables (Set TABSQuery.InMemory property to True before query execution).  SELECT INTO vs INSERT SELECT  In some cases SELECT ... INTO some_table query runs faster than INSERT INTO some_table (SELECT ...), in another cases INSERT INTO is faster. Please note that the RequestLive property could have an impact on a performance of these queries.