Slow RecordSet Retrieval -- Likely Result of Poorly Written Query

[CJ_London]

you apply indexes to fields that are used in joins or criteria. You also apply them to fields you frequently search, filter or sort on. Note it is not worth applying indexes to fields such a yes/no because it is ineffective. This link may give you a better understanding of indexing https://www.access-programmers.co.uk...26&postcount=1

with regards your query, perhaps carrier_code as well. and maybe classification code. Now you have something that executes in a short period of time, experiment a bit, add an index rerun and time the query.



Note that now you have added indexes, if your table is block populated (i.e. big dump of data from somewhere else) this will take longer, if too long a tip is to remove the indexes, add your data in and reinstate the indexes - all easily done with code

[NSearch]

I have been experimenting with the indexes and the query is now returning in 2 seconds!!

[CJ_London]

A lot better than the 70% I suggested. Is that 2 secs to see the recordcount at the bottom?

Some years ago I had a client who built their own complex query for something and it took about an (acceptable for them) hour to run. As the dataset grew it got to the stage they would set the query running before they went home of an evening and it would finish soon after they got in the following morning. Then it starting taking to lunchtime - so around 20 hours! I was doing something else for them and they asked me to look at it. Tweaking here and there I got it down to 20 minutes. So it is always worth experimenting

[isladogs]

Here's another link you may find useful ....http://www.mendipdatasystems.co.uk/s...s-8/4594556613

[NSearch]

A lot better than the 70% I suggested. Is that 2 secs to see the recordcount at the bottom?

Some years ago I had a client who built their own complex query for something and it took about an (acceptable for them) hour to run. As the dataset grew it got to the stage they would set the query running before they went home of an evening and it would finish soon after they got in the following morning. Then it starting taking to lunchtime - so around 20 hours! I was doing something else for them and they asked me to look at it. Tweaking here and there I got it down to 20 minutes. So it is always worth experimenting

2 seconds for the entire record set to return! Really awesome!

In some larger data sets that I am going to get into, I am sure it would have taken hours!

[CJ_London]

2 seconds for the entire record set to return! Really awesome!

goes to show the power of indexing. By my calculation if it was taking 7 mins before that is around 200 times faster. If you haven't done so, take a look at Colin's link, it demonstrates different ways of achieving the same result with differing degrees of performance. Be aware that it is based on a specific dataset. With different types of datasets, different volumes, different datatypes you may get different comparative performances - although appalling bad will always be appallingly bad!