SQL 性能优化

Improving SQL Performance
Previous Top  Next


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 
  
Note: If you have any questions concerning SQL queries optimization, please don't hesitate to contact ComponentAce Support Team [e-mail]

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 setRequestLive value to True and to False before opening a query. Some queries run faster with the RequestLive=True,otherswill perform much better with theRequestLive=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 clause

To 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 tables

Your query perofrmance could be increased also if you will move alldata from disk tables toin-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 ... INTOsome_table query runs faster than INSERT INTO some_table (SELECT ...),in another casesINSERT INTO is faster. Please note that theRequestLiveproperty could have an impact on a performance of these queries.


See alsoIncreasing Inserts and Updates Speed and Improving Overall Performance