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