- Overnight, a separete Accsess application reads and stores some data from DB2 app. and stores each category of data in a database with single table.
Data1.mdb 1000 records
Data2.mdb 90,000 records
Data3.mdb 250,000 records
Data4.mdb 2,290,000 records
Now, there is another small separate Access App with no tables but one form.
In this form, user check chkData1 and chkData3, for example. When cmdSearch is clicked, some code makes a union query from Data1.mdb and Data3.mdb and shows the combined data via a single query. It is slow. It was expected to be slow.
I want to find a way to optimize the process.
Whay to download from DB2 to *.mdb files: Deal with the data locally and don't bother with network traffic etc.
Well, 3 million is not what Access can handle, that is understood very well. However, search starts after at least one criteria is selected first. Although there are about 3 million max in a uion query (without criteria which is never the case), with a criteria applied it comes down to no more than 100,000 records at high side.
But, the system has to process 3 million records, applying the criteria, to get the result down to 100,000 records.
I may have to give up the idea of union query. And trim down the initial data sets.
After all these background information, I would like to know if the concept of SQL-Server Express or msde possibly could be utilized in this to help speed things up a bit.