Access query MAS90 slow

[hamking01]

Using Access I've connected to MAS90. Everytime I create a query linking to of the MAS90 tables it'll stop responding when I run the query. So I've created two queries, each retreiving data from a MAS90 table. I then create another query based on these two queries. This query will run with no problems, but will take up to 5 minutes.

I'm trying to pull data from MAS90 for each days orders and inputting them into Access. Is there another way to do this, or is there a way to speed up the query? Person responisble for MAS90 is saying that it's so slow because MAS90's indexing of tables isn't and can't be put to use, so it has to run through each record in the table.

 

[Blorf]

Well, I liken the Mas ODBC to be similar to sucking data through a straw instead of a pipe.

That being said, here is my solution, and I do it very routinely.

I am assuming that you are querying SO1 and SO2?

If so, do make tables on so1 and so2 to local tables, which will run fairly quick, then run your queries on the local tables, which also will run fairly quick.

Regarding your Mas persons reasons, I think thats a good explanation. Buy by copying each record each time on at a time, there is no indexing required.

Hope that helps ya,
ChaZ

 

[hamking01]

Blorf,

"do make tables on so1 and so2 to local tables" by this do you mean to import the so1 & so2 tables from mas90 and create them in new tables in access.

Currrently, I've used queries on linked tables. This way I can select pertinent data I need.

Also we have 3 different customers with 3 different Access DB's (one for each customer). So I'll be needing to get filtered data from MAS90 for particular customer. If I just import entire so1 & so2 tables I'd be getting a lot of useless info that would latered be needed to be deleted out.

Do you update the local so1 & so2 tables everytime or do you just import the so1 & so2 to local tables everytime?

 

[Blorf]

I do not import as you say so much as run an action query.

Here is an example:

SELECT SO1_SOEntryHeader.*, SO1_SOEntryHeader.CustomerNumber AS Filter INTO SO1
FROM SO1_SOEntryHeader
WHERE (((SO1_SOEntryHeader.CustomerNumber)="LACMTA "));

The above query will create a table or over write an existing table called SO1 with all the records in SO1_SOEntrHeader, but only if the Customer number is LACMTA, one of our customers.

If you do that, then you can create a similar query for SO2, but join on so1 (Your made table) on Sales Order Number, since SO2 does not have customer numbers in it.

Both will run much faster than linking the two mas tables together.

Hope that helps,
ChaZ

 

[hamking01]

I've used the following sql to create SO2 table joined on SO1 sales order number:

SELECT SO1.SalesOrderNumber, SO1.Filter1, SO2_SOEntryDetailLine.ItemNumber, SO2_SOEntryDetailLine.QtyOrdered INTO SO2
FROM SO1 LEFT JOIN SO2_SOEntryDetailLine ON SO1.SalesOrderNumber = SO2_SOEntryDetailLine.SalesOrderNumber;

However, this is still taking about 5 minutes to run. Please advise if I'm doing this correctly. Thanx

 

[Blorf]

It sounds like you are. I would suggest doing a make table query on so2 also. You will get lots of extra records, but thats ok, you can delete the table and compact / repair.

So a new query called make so2

SELECT SO2_SOEntryDetailLine.* INTO So2
FROM SO2_SOEntryDetailLine;

Then, when you make so1 with a filter, then make so2 with not filter, your result query will run faster.

SELECT SO1.SalesOrderNumber, SO1.Filter1, SO2.ItemNumber, SO2.QtyOrdered INTO SO2
FROM SO1 LEFT JOIN SO2 ON SO1.SalesOrderNumber = SO2.SalesOrderNumber;

So you make so1, make so2, then run your result query.

ChaZ

 

[Blorf]

Any luck?

ChaZ

 

[hamking01]

Yesterday afternoon inserting SO2_SOEntryDetailLine into SO2 still took approximately 2 minutes. Today Access would stop responding everytime a connection is made to MAS90. The connection is just too unstable that I'm just having users output through Report Master and paste into Access. This procedure only took a minute tops, whereas it woul've taken much longer importing both tables, requerying, and then inserting. Thanx for your help on this. I'll post back if I find another solution to this.

 

[Blorf]

Ok. Sorry the solution didn't work for you.

ChaZ

 

[hamking01]

Worked great, cut down query time in half. Main issue was with MAS90 performing better through itself rather than through Access. Many different users were also logged on creating sales orders may have also had an affect.