Improving SQL Performance

Delphi Database, Delphi Components from ComponentAce

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

Apr 20, 2014