How can I apply SQL-Server express msde to speed up...
Background:
- 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
.
.
etc.
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.
Regards.
- 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
.
.
etc.
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.
Regards.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are in a position to determine criteria prior to download from DB2, then you should definitely consider a pass-through query which would contain these criteria and have DB2 provide only the required records. However, it sounds like the criteria come from Data1, and/or Data2, and/or Data3. If that is the case, I'm afraid Access is not the tool you should use to process 3+ Million records.
ASKER
LPurvis,
Re:> Is the data in these tables effectively of identical (or vaguely similar) structure?
They are identical. Select T1.* From Table1 T1 Union T2.* From Table2 T2 works.
Re:> ...criteria could be used on the DB2 server - to retireve only the 100,000 records?
User will be interested in searching for data in one of the sets as partioned and stored in either of:
Data1.mdb 1000 records
Data2.mdb 90,000 records
Data3.mdb 250,000 records
Data4.mdb 2,290,000 records
but ones in a while user may need to combine tow of them (if the speed of performane wasn't the problem, I would like to be able to union all). The problem with applying is that user has to handle all of the data with each search query. The other problem is; I have not been able overcome my mental block of this solution, applying filter directly to DB2, on that it my be very slow (only connecting to DB2 takes a minute or two and we don't have to deal with network traffic, etc.).
Re:> 3,000,000 to be totally out of the question for Access/Jet
Number of records will be substantially less with each search trial. Would you say 1,000,000 will be okay?
Q1: Is there options available other than Jet in Access environment to do the search?
-------------
Nico,
My understanding from a "PassThrough" query is that it is located in the backend and process there.
Q2: Is above statement correct?
If so, then we have four backends to work with.
Q3: Is it possible to combine them using Sequal-SQL Express or MSDE to combine them to form a single backend?
-----------------
GRayL,
There is an existing system with apparently a passthrough query of some sort. It is very slow and I was asked to do something about it. My last resort will be to ask the managment to come up with some criteria to eliminate some (or most of data which is of no interest), then the outcome my look like:
Data1.mdb 1000 records
Data2.mdb 60,000 records
Data3.mdb 100,000 records
Data4.mdb 900,000 records
In Data4.mdb, for example, there are some duplicate records (except for one of the field and the management is interested in most recent of record).
Thank you for the inputs,
Mike
Re:> Is the data in these tables effectively of identical (or vaguely similar) structure?
They are identical. Select T1.* From Table1 T1 Union T2.* From Table2 T2 works.
Re:> ...criteria could be used on the DB2 server - to retireve only the 100,000 records?
User will be interested in searching for data in one of the sets as partioned and stored in either of:
Data1.mdb 1000 records
Data2.mdb 90,000 records
Data3.mdb 250,000 records
Data4.mdb 2,290,000 records
but ones in a while user may need to combine tow of them (if the speed of performane wasn't the problem, I would like to be able to union all). The problem with applying is that user has to handle all of the data with each search query. The other problem is; I have not been able overcome my mental block of this solution, applying filter directly to DB2, on that it my be very slow (only connecting to DB2 takes a minute or two and we don't have to deal with network traffic, etc.).
Re:> 3,000,000 to be totally out of the question for Access/Jet
Number of records will be substantially less with each search trial. Would you say 1,000,000 will be okay?
Q1: Is there options available other than Jet in Access environment to do the search?
-------------
Nico,
My understanding from a "PassThrough" query is that it is located in the backend and process there.
Q2: Is above statement correct?
If so, then we have four backends to work with.
Q3: Is it possible to combine them using Sequal-SQL Express or MSDE to combine them to form a single backend?
-----------------
GRayL,
There is an existing system with apparently a passthrough query of some sort. It is very slow and I was asked to do something about it. My last resort will be to ask the managment to come up with some criteria to eliminate some (or most of data which is of no interest), then the outcome my look like:
Data1.mdb 1000 records
Data2.mdb 60,000 records
Data3.mdb 100,000 records
Data4.mdb 900,000 records
In Data4.mdb, for example, there are some duplicate records (except for one of the field and the management is interested in most recent of record).
Thank you for the inputs,
Mike
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank Nico. I had the same understanding of pass through query, now I am certain about it.
Going back to the subject I am dealing with. I reduced number of fields and that resulted in 80% of backend file size. I combined all those database and combined with the fron end mdb.
It seems the problem for my operation is no more.
However, for sake of this question, I would like to have some help (some information) with SQL-Server Express and or MSDE.
- Where to locate if download of some sort is required.
- Some demonstration where I can apply in some test operation.
Thank you.
Mike
Going back to the subject I am dealing with. I reduced number of fields and that resulted in 80% of backend file size. I combined all those database and combined with the fron end mdb.
It seems the problem for my operation is no more.
However, for sake of this question, I would like to have some help (some information) with SQL-Server Express and or MSDE.
- Where to locate if download of some sort is required.
- Some demonstration where I can apply in some test operation.
Thank you.
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, glad I could help.
It should "save" the creation of the 3Mb rows as they would only return the resultset...
Nic;o)