Speed

[Ekhart]

I am having terrible issues with database speed, particularly when running reports. I am imagining a large part of this is due to poor network since the back-end is on the network, but I do have some queries that contain many unions within them, about 20-30 pages in length total. Does anyone know if one could expect better performance by splitting these out and simply having queries to queries to queries vs. 1 large union query?

[CJ_London]

proper indexing has a major impact on performance - with very large datasets a properly indexed db will take maybe 5% of the time of a non indexed db

union queries are relatively slow (to answer your question) and indicative of a poorly designed database so you really need to look at your table design and relationships to eliminate them. If you are sorting or filtering the union query itself (as opposed to its individual elements) it will not be able to use indexing

using an initial wildcard in string criteria (e.g. somefield like *sometext) prevents the use of an index if it is otherwise available

bringing too much data across the network can slow performance

group by queries - where possible, use criteria in the WHERE Part, not the HAVING part, that way you are only bringing through the data required before grouping, rather bringing it all through and then 'eliminating' the values you don't want after you have brought it through

in your report, to reduce 'height' use criteria as much as possible in the recordsource and not in the filter

Only bring through the fields required to reduce 'width' - don't use SELECT *

query design -don't use domain functions

remove any sorting you have in your queries and sort in the report

use criteria on individual indexed fields, not on calculated fields (particularly table calculated fields)

don't use lookups in table design has a significant impact on performance since the underlying data in the rowsource also has to be brought through, increasing the amount of data you are bringing across the network.