?
Solved

How can I apply SQL-Server express msde to speed up...

Posted on 2006-03-24
8
Medium Priority
?
361 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Mike Eghtebas
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 400 total points
ID: 16287956
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
 
LVL 54

Expert Comment

by:nico5038
ID: 16288120
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16294900
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 16302309
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
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 400 total points
ID: 16303161
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 16305656
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
 
LVL 44

Accepted Solution

by:
GRayL earned 1200 total points
ID: 16305990
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16315402
Thanks, glad I could help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question