Splitting the Database has lead to very long delays.

[ork2002]

I am trying to split a large patient database into a front end and back end. I have a report that is vital to the daily operation, and it requires frequent refreshing for the controlling administrator. Splitting the database into a front end and back end is causing this report to go from loading in 1-2 seconds to requiring 30 seconds to load. Any suggestions/help would be appreciated.

[June7]

Buy a faster file server? Probably not an option and might not resolve the issue anyway.

How complex is the report? Does it use any domain aggregate functions?

[ork2002]

It is very complicated compiling data from two tables and many queries. It is a weekly operating room schedule where the week is one page and then there are multiple subreports in 5 columns representing the five days of the week where each patient is a subreport, usually 3-8 patients in a column an average of 25 subreports per page... It runs very quickly when the database is not split, even though the database is stored on the same server that the backend is when I try to split it... I think the report may just be too complex.

It was even slow when I copied it onto my semi-high performance computer and tried to split it on my computer. Would it help to move all the queries to the backend too?

[NoellaG]

The problem could be that for loading each subreport the report opens and closes a new connection to the backend. Moving the queries to the backend is not really an option as you need them in the same file as where you open the report, you can't link to a query. You could try to use pass-through queries or create a work table in the front end to store the result of the final query result and use this as the base of your report. You can then refresh this workset at regular times using a timer or letting the user hit the refresh button.
Also look at your report to see if you can't get a similar result using 'grouping and sorting' in stead of subreports. Subreports always draw a lot of resources.

[ItsMe]

It may be as simple as employing a Main Menu form that remains open while the application is open. You could bind the form, or a subform within the form, to a small table/recordset on the BE file.

I use a User Profile table and display the current User's name and department in my Main Menu forms.