I have a split database, backend is on a server. Tables are linked. I'm having an issue with one particular query/form. There are two tables, callserv and mastername. Each table holds about 50,000 records. They are joined by callserv.cid -> mastname.nameid. callserv.cid is a unique value, where as mastname.nameid can have duplicate values.
When running the query by itself it is taking around 10 seconds to run, when I open the form that uses this query, the form takes about 10 seconds to run the first time, however subsequent times the form opens instantly.
Normally I wouldn't mind, but this form is used very heavily and I would like it to open instantly the first time.
I have the query sorting as well, based on two fields (incnoyear and incnoseq) which are numbered fields. I have tried all sorts of things, even running the query at the beginning of my application, which then results in the form opening right away, however the application seems like it's 'hung' for 10-15 seconds which is unacceptable.
I'm at a loss and was hoping one of you geniuses could help me out. I could post the code/tables if needed.