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 at support@componentace.com

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 the RequestLive=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 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 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.


See also
Increasing Inserts and Updates Speed and Improving Overall Performance