|
|
|
|
To increase the speed of SQL SELECT query, you can analyze the following issues:
| · | 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'
|
| 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
|
|