System Requirements for Complex Large Queries

[nguyeda]

Hello All,



If I have 13 sheets linked from excel containing about 15,000-20,000 rows and 15ish columns. A 3 have about 35,000 rows, 1 has 85,00 rows and 12 columns, 10 columsn and 2 have 400,000 (2-3 columns) then I create multiple quries then from those quries make more quiries...

Can a 2.8ghz, i7 (not sandy) 4gb system handle this OR would adding more RAM help?

It's going really slow right now making the 1st query from the table with about 85,000 rows and 12 columns. Can't wait to combine that with the 20,000 row database with 15ish columns...

Thanks!

[rpeare]

Post your SQL code.

If you are using domain functions (dsum, dcount, dlookup, davg) etc your queries will really bog down.

[RuralGuy]

FYI, it is my experience that linked spreadsheets are considerably slower than imported spreadsheets.

[nguyeda]

I'm not using those functions Rpeare. It's a lot of queries to post the SQL code for.

There are 28 queries right now but most of the are subqueries. There are only about 7ish actual queries I export to excel to share with others as reports.

I've been reading about increasing the performance of my database. It's gotten to the point that I can hardly get to "Design View" or just Open the query because it takes forever and Access just wants to close.

I read about the OpenDatabase Method but I can't figure it out. Since all the source tables are Linked to Excel, I cannot Split the database into a FE and BE.

My theory now is to creata VBA script to import the excel tables as access tables then I can split the database and hopefully it'll run faster.

I just don't know if 1) that will work or 2) how to do it.

The excel source has about 13 sheets and anywhere from 2 to 15 columns.

What do I do?? The Queries are so complex that my computer doesn't want to run them at all.

[nguyeda]

I want to create the import script because I update the excel database weekley. It'd be mundane to import all the tables in to Access 1 by 1. So I need the script to pull from each sheet in excel, with the corrent formats and overwrite the access tables.

[nguyeda]

My process right now is Export from SAP > Copy in to Excel Sheet > Update Links in Access (which runs the qurries) > Export Script to Excel Sheet for User Distribution

[nguyeda]

At worst, I'm using some iif functions. A lot of SUMs and a lot of comparing operators (>, <, etc).

I'm really lost and this is really important

[nguyeda]

Hello All,

I've done A LOT of research before coming to search here and thanks for anyone who helps!

Here is the backgroud of my goals: I have about 13 sheets with anywhere from 2 to 15 columns. They range from havine 2,000 rows to 220,000 rows. I pull them from an SAP system for cross analysis in Access. Right now I have them LINKED in Access (via the wizard) then those tables are part of 28 queries/subquries to output a 7 core query results which I then export back to Excel in a reporting format for distribution to upper management (assume no computer skills).

The each query has a one-to-many relationship, few have one-to-one.

Here is the problem: The problem is the LINKED tables are creating an enormous performance lag and I can hardly edit the queries by going in to design view without having to wait 30 minutes or Access just freezing up. This project is a template of a smaller Access project and this solution worked fine. Apprently you can't split linked tables.

My proposed unresolved solution and required help: Create a VBA script to import the tables from Excel (A datasource no one will use) in to Access (replacing the LINKED tables). Then I can create a front end and back end database. I could also establish keys. From what I've read, this should vastly increase performance.

Summary of current process: SAP > Excel > Linked to Access > Execute Quries and Export VBA script to Excel (works fine).

Summary of unresolved soltuon: SAP > Excel > VBA Script Export to Access (Help) > Execute Quries and Export VBA script to Excel (works fine).

Please do not request that I share the excel or access database because legally I cannot do it. I'd post the SQL code but it's A LOT of queries. The keys will be a 9 digit number and sometimes an alpha-numeric order number that is about 15 charectors long.

I export all this to excel in the end because the users have no knowledge in Access and barely can use Excel (other than their ability to read and click).

Thanks for anyone who helps! I'm a beginner in VBA but I know enough to peice stuff together frankinstien style to make it work but I need push!

[nguyeda]

So this works as far as getting the tables in to access but I need to to be able get the column headings in Excel as Column Headers in Access as well as the correct format.

 

Sub TRexcel()
strPath = "C:\Users\NguyenDH\Desktop\Supply Action Analysis\May 10 - 2011\Data\Data.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "BO Raw", strPath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "SOH Raw", strPath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ZMRP Raw", strPath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ZPR", strPath
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ZCN", strPath

End Sub

[nguyeda]

So just adding TRUE to the end works. Ok, next problem I ran in to was in the long-term flexibility of the code....

It doesnt work if:

1) I add columns to the orginal excel data sheet
2) I change the format of 1 of the columns
3) Create Primary Key


I guess a solution would be to just delete the tables and re-import them but you guys have to be knowledgeable enough to write some kind of "overwrite" code instead of importing. Right?

 
Sub TRexcel()
strPath = "C:\Users\NguyenDH\Desktop\Supply Action Analysis\May 10 - 2011\Data\Data.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "BO Raw", strPath, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "SOH Raw", strPath, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ZMRP Raw", strPath, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ZPR", strPath, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ZCN", strPath, True
 
End Sub

[nguyeda]

Actually, that transferspreadsheet is not even working right. It's just importing 2 rows and 2 columns that have some formulas in them....

[nguyeda]

I figured that out, it's just importing the same 1st table. (which I had hidden until now).

[rpeare]

Your original question was about optimizing your query/queries. If you have successfully imported the data and are no longer using linked tables, does the query run any faster? If not please describe what you are trying to accomplish and list the SQL for the query you're trying to run. Your conversation is rather one sided at the moment and has strayed from your original question.

[nguyeda]

Yes, I realize that. I actually created a whole new thread, the majority of this one came from a different question is why it seems that way.

I have not succcessfully imported the data, the script above just copies the same 1st sheet of the source excel document over and over again.

Post #8 is my real question and would of perferred it to start out with that post as I orginally intended. Would it really be effective for me to copy over that many SQL statements?

Here is one of the most complicated:

SELECT MARA.[Analyst Code], [Oblg EBAN].Material, [Oblg EBAN].[Cnsm repair ind], [Oblg EBAN].PRON, [Oblg EBAN].[Purchase Order], EKPO.[Purchase Requisition], EKPO.[CLIN/Sub-CLIN], EKKO.[Dlvr/call Ord #], [Oblg EBAN].[Manuf Part No], EKPO.[Document number], EKKO.[Contract Number], IIf([EKPO]![Net Order Price]<[MARA]![AMDF Price],"Less","More") AS [Cost Chk], EKPO.[Net Order Value] AS [Total Contract Amt], Round(([Oblg EBAN]![Oblg Date]-[Oblg EBAN]![Date of order])/30.5,2) AS [Current ALT], MARA.[Administrative Lead Time], Round((Date()-[Oblg EBAN]![Oblg Date])/30.5,2) AS [PLT to Date], MARA.[Production Lead Time], IIf(Round((Date()-[Oblg EBAN]![Oblg Date])/30.5,2)>[MARA]![Production Lead Time],"Late","On Schd") AS [PLT Rq Chk], [Oblg EBAN].[Oblg Date], [Oblg EBAN].[Date of order], [EKPO Order Sum].[SumOfOrder Quantity] AS [Order Qty], [EKPO Order Sum]![SumOfOrder Quantity]-Sum([EKPO]![Total Quantity Shipped]) AS [Due In], LastDLV.[MaxOfMaxOfDelivery Date] AS [Last DLV Dt], LastDLV.LastOfQuantity AS [Last DLV Qty], EKPO.[Total Quantity Shipped], NxtDLV.NxtDLV AS [Next DLV Dt], NxtDLV.[Schd Qty] AS [DLV Qty], [Oblg EBAN].[SHIP TO], EKPO.Plant, ([EKPO Order Sum]![SumOfOrder Quantity]-[EKPO]![Total Quantity Shipped])*[EKPO]![Net Order Price] AS [Value of Due In], LFA1.Vendor, LFA1.[Name 1]
FROM NxtDLV RIGHT JOIN (LFA1 RIGHT JOIN (EKKO RIGHT JOIN ([EKPO Order Sum] RIGHT JOIN (EKPO RIGHT JOIN (LastDLV RIGHT JOIN (MARA RIGHT JOIN [Oblg EBAN] ON MARA.Material = [Oblg EBAN].Material) ON LastDLV.[Document number] = [Oblg EBAN].[Document number]) ON EKPO.PRON = [Oblg EBAN].PRON) ON [EKPO Order Sum].PRON = [Oblg EBAN].PRON) ON EKKO.[Purchasing Document] = [Oblg EBAN].[Purchase Order]) ON LFA1.Vendor = EKKO.Vendor) ON NxtDLV.[Purchasing Document] = [Oblg EBAN].[Purchase Order]
WHERE (((Mid([Oblg EBAN]![PRON],4,1)) Not Like "M"))
GROUP BY MARA.[Analyst Code], [Oblg EBAN].Material, [Oblg EBAN].[Cnsm repair ind], [Oblg EBAN].PRON, [Oblg EBAN].[Purchase Order], EKPO.[Purchase Requisition], EKPO.[CLIN/Sub-CLIN], EKKO.[Dlvr/call Ord #], [Oblg EBAN].[Manuf Part No], EKPO.[Document number], EKKO.[Contract Number], EKPO.[Net Order Value], MARA.[Administrative Lead Time], MARA.[Production Lead Time], [Oblg EBAN].[Oblg Date], [Oblg EBAN].[Date of order], [EKPO Order Sum].[SumOfOrder Quantity], LastDLV.[MaxOfMaxOfDelivery Date], LastDLV.LastOfQuantity, EKPO.[Total Quantity Shipped], NxtDLV.NxtDLV, NxtDLV.[Schd Qty], [Oblg EBAN].[SHIP TO], EKPO.Plant, LFA1.Vendor, LFA1.[Name 1], EKPO.[Order Quantity], EKPO.[Net Order Price], MARA.[AMDF Price]
HAVING ((([Oblg EBAN].[Oblg Date])>#4/1/2009#) AND (([EKPO Order Sum]![SumOfOrder Quantity]-Sum([EKPO]![Total Quantity Shipped]))>0));

[nguyeda]

Also, the script above just does 1/4 of the excel sheet just for testing because it takes to long to wait and see something fail.