We have upsized tables to SQL via the upsize wizard tool and are now using an mdb file as the front end with these linked tables.
We are experiencing significant performance issues when opening forms. To note, most forms are fairly complex with multiple subforms running mutliple queries within.
We have put more of the "robust" queries on SQL as views and linked to these via tables in our mdb which slightly improved speed, but still a major issue.
Has anyone experienced this? Are there any suggestions for improvement?
Thanks in advance
Having managed to get your tables into SQL server is a good start but unfortunately only a small part of the whole upsizing experience IMHO.
I am assuming your tables are successfuly installed on the server with correct datatypes and sizes mapped out for the fields and that you have all the relevant primary keys identified, set and indexes created, where relevant. The upsizing wizard is a good labour saving tool but frankly quite often makes incorrect assumptions.
I have to say it very much depends on the complexity of your MDB file. Suffice it to say, in my experience I have invested in rewriting frontend files more often than enough, rather than make do with the original. Why? well generally I've found that MDBs using JET have so much interwoven code in the background
(and why not of course it is meant to work with JET on the client PC - that is what it was designed for) that it becomes compromised in terms of scaleability so much so, that it takes
more time to convert the code than to rewrite it.
The utopia for working with SQL server is to use 'Stored procedures' these are precompiled processes working to an execution plan. Because they are precompiled the performance gain is huge.
Does your database have embedded SQL behind the forms that encapsulate inefficient or unduly complex functionality maybe joining on columns that have not been indexed on the server? firing SQL to the server hundreds of time maybe in a code loop rather than in a batched process on the server or maybe even returning inefficient datasets like SELECT * FROM TheBiggestTableInTheUniverse..........the list can go on and sound like a time consuming horror story in having to deal with a troublesome slow MDB file linked to SQL Server.
The reality is.....performance should be
'substantially' improved not to mention concurrency which invites me to wonder if there are design issues with the MDB file itself in what it is doing functionally in the 'client server' environment.
Depending on your version of Access there are two types of frontend file for working with SQL Server one the .MDB file and the other the .ADP file (I personally 'much' prefer the ADP for working with server side records thats a personal choice, but it is one that I recommend you at least look at because it functionally is the same as the MDB in terms of report capability, screen layout appearance etc you get the added advantage of seeing views and stored procedures in the frontend too)
It may be that your MDB is, as you outline, 'complex' and relied so much so that you feel you have to 'remain with it', in which case I'm afraid you have no option but to fine tooth comb it to track the performance issues.
Multiple subforms on a single form is for me a big no no Generally speaking one subform is quite sufficient and you merely swap its sourceobject if needs be. The idea being to minimise calls to the server for data otherwise it is going to be slow dependant on how many datasets are being opened at any one time this includes dropdown comboboxes not in use, (the datasource being set runtime when the dropdown gets the focus on screen. All of these techiques are matters of common knowledge with experience.
I'd be interested to know how a freshly created MDB file (ie: a new one with no forms reports macros modules and code) linked to the server performs in returning your datasets.IF it is slow you have ODBC,network or server database issues IF it is fast... then logically it cannot be the MDB format itself and therefore the evidence against the erstwhile the
main frontend file you have becomes greater.
I'd be interested to know how a freshly created ADP file (ie: a new one with no forms reports macros modules and code) linked to the server performs in returning your datasets. If this is slow then you 'definitely' have a network issues or a server side database configuration problem.
Either way all of these things to eradicate the problem as I am sure you aware anyway, is a process of elimination unless its blindingly obvious. I'd be interested to know how you get on with this because the long and short is you SHOULD be getting greatly enhanced performance... and no I have not had problems myself (in answer to your penultimate question)
Regards
Jim