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.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
So at some stage the output from your DB2 database get's appended to the 4+ access databases.
And your pondering is whether substituting in MSDE/SQL Server Express would make things easier?
(Well - indeed possibly just *how* it would).

Is the data in these tables effectively of identical (or vaguely similar) structure?
So that it lends itself well to Unioning?
Would it in fact lend itself well to existing in the same table?

Is there no stage at which the criteria could be used on the DB2 server - to retireve only the 100,000 records?
A simple passthrough could be your saviour there no?

Failing that - with all records appended to the same table - missing out the need to Union your results first - may offer another avenue of improvement.
(I'm guessing that the records are moderately substantial - for 3,000,000 to be totally out of the question for Access/Jet).
0
nico5038Commented:
Did you try to create PassThrough queries for the mdb's and base the Union on those ?
It should "save" the creation of the 3Mb rows as they would only return the resultset...

Nic;o)
0
GRayLCommented:
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.  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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







0
nico5038Commented:
A passthrough query (See 1) is a query sent as SQL text to the database engine and executed there only the result set will be returned. The ODBC link will take care that this is works and network load is minimal.
Using a "normal" Access query (See 2) will force Access to retrieve first all rows of the linked table(s) and then execute the query.
See the difference:
1) Sent e.g. 60 characters to backend .mdb
    Returns 100,000 rows
2) Extracts 1,000,000 + rows into temp memory
    Returns 100,000 rows

Just check the helpfile for more detailed info.

Nic;o)
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
0
GRayLCommented:
Mike:  I had a problem with understanding the Access participation in a BE/FE setup as well as pass-through queries in general. You may get some help from:  http://www.experts-exchange.com/Databases/MS_Access/Q_21591293.html

In addition, Google is a great source of info and here is a thread from simply  -  pass through queries

http://www.techonthenet.com/access/tutorials/passthrough/basics01.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
Thanks, glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.