Append Query Too Slow

[sgidley]

Hello,

I have a query (qryTotal_Compliance_Data) whose select query takes about 5-8 seconds. Not the end of the world, but I am trying to improve it. This query reaches into other queries which then go to many tables. Because we only get daily updates anyway, I want to convert this query into a table, and then access the table throughout the day. This would work great, except the only way I can figure out how to create this table is through an Append Query (or a make-table query) but both take a LONG time. Around 10 minutes. We are talking about 20,000 records, with 130 fields. The table is ~50mb in size(or adds that much to the db) when it finally finishes.

Is it normal for this amount of data to take 10 minutes for an update query when I get the Select query results in seconds? Maybe I'm oversimplifying, but it just seems like once the query is done, the data is there, so save it as a table and we are ready to go. Guess not...

All of this is with the database on my machine locally, no networks, but the data being accessed by the query is all over a network.

Thanks for your help!


INSERT INTO tblTotal_Compliance_Data ( StockCode, qryCompliance_Description, ReleaseName, Version, PartCategoryName, ProductClass, qryProduct_Class_Description, ComplianceStatus, Name, SupplierName, strSupplierType, SupplierCatalogueNumber, DataSheetLink, ConversionCQRNumber, Expr1, Expr2, CertLink, [NVISION Pb Free Build Date], [EU-RoHS Compliant Date], RoHSExemptCode, [RoHS Exemption], [China-RoHS Review Date], [China-RoHS Compliant], China_EFUP, [Korea-RoHS Review Date], [Korea-RoHS Compliant], [Korea-RoHS Misc], CAD, ChromVI, Pb, Hg, PPBs, PBDEs, RoHSComplyStatment, RoHSComplySymbol, ASB, Azo, [PbPVCCables)], CFCs, HCFCs, PCBs, PolyNaphthalenes, Radioactive, ChlorinatedParaffins, TBTTPT, TBTO, Sb, [As], Be, Bi, BromFlameRetardants, Ni, Phthalates, Se, PVC, MoistureLevel, [Floor Life Time], [Floor Life Cond degC/%RH], [Standard Soak Requirements Time (hrs)], [Standard Soak Requirements Cond degC/%RH], [Accelerated Soak Requirements Time (hrs)], [Accelerated Soak Requirements Cond degC/%RH], Package, Packaging, QtyperReel, LeadFinish, BodyLength, BodyWidth, BodyHeight, LeadSpacing, UserField4, Marking, [Action], Location, Machine, Type, TapeSize, Status, Applied, Comment, [INSP Time], US_Safety_Standard_Link, US_UL_Qualification_Safety_Report_Link, UL_Inspection_Report_Link, [USA_UL_Catagory_&_File_Number_Link], [UL_Canada_Catagory_&_File_Number_Link], [EU-CENELEC_SafetyStandard_Link], EU_CE_Safety_Report_Link, [EU-CENELEC_EmissionsStandard_Link], EU_CE_Emissions_Report_Link, [EU-CENELEC_ImmunityStandard_Link], EU_CE_Immunity_Report_Link, NewSupCatNumber, [New SupplierName], [Current SupCatalogueNum], CurrentSupplierName, MfgToolNumber, ToolUsed, HoleDiameter, FPP, NVISIONToolNumber, CorrectTool, tblCompliantPressFitReferenceDocs_Ref_Doc_Link, tblCompliantPressFitReferenceDocs_1_Ref_Doc_Link, tblCompliantPressFitReferenceDocs_2_Ref_Doc_Link, Picture, Comments, CrimperNumber, CrimperTerminalWireSize, CrimperWireGuage, tblCrimperReferenceDocs_ReferenceDocLink, tblCrimperReferenceDocs_1_ReferenceDocLink, CrimperComments, Preferability_Name, Safety_Controlled_Info, UL_Report_Location, Machine_Name, Lead_Spacing, Body_Calibration, Body_Calibration_Diagram, Cut_Length, Body_Clearance, Machine_Manual, Hand_Prep_Data, Notes )

SELECT qryCompliance.StockCode, qryCompliance.Description, qryCompliance.ReleaseName, qryCompliance.Version, qryCompliance.PartCategoryName, qryProduct_Class.ProductClass, qryProduct_Class.Description, qryCompliance.ComplianceStatus, qryCompliance.Name, qryCompliance.SupplierName, qryCompliance.strSupplierType, qryCompliance.SupplierCatalogueNumber, qryCompliance.DataSheetLink, qryCompliance.ConversionCQRNumber, qryCompliance.[tblAVLforImportNewToOldXref.CurrentSupplierName] AS Expr1, qryCompliance.[tblAVLforImportNewToOldXref.Current SupCatalogueNum] AS Expr2, qryCompliance.CertLink, qryCompliance.[NVISION Pb Free Build Date], qryCompliance.[EU-RoHS Compliant Date], qryCompliance.RoHSExemptCode, qryCompliance.[RoHS Exemption], qryCompliance.[China-RoHS Review Date], qryCompliance.[China-RoHS Compliant], qryCompliance.China_EFUP, qryCompliance.[Korea-RoHS Review Date], qryCompliance.[Korea-RoHS Compliant], qryCompliance.[Korea-RoHS Misc], qryCompliance.CAD, qryCompliance.ChromVI, qryCompliance.Pb, qryCompliance.Hg, qryCompliance.PPBs, qryCompliance.PBDEs, qryCompliance.RoHSComplyStatment, qryCompliance.RoHSComplySymbol, qryCompliance.ASB, qryCompliance.Azo, qryCompliance.[PbPVCCables)], qryCompliance.CFCs, qryCompliance.HCFCs, qryCompliance.PCBs, qryCompliance.PolyNaphthalenes, qryCompliance.Radioactive, qryCompliance.ChlorinatedParaffins, qryCompliance.TBTTPT, qryCompliance.TBTO, qryCompliance.Sb, qryCompliance.As, qryCompliance.Be, qryCompliance.Bi, qryCompliance.BromFlameRetardants, qryCompliance.Ni, qryCompliance.Phthalates, qryCompliance.Se, qryCompliance.PVC, qryCompliance.MoistureLevel, qryCompliance.[Floor Life Time], qryCompliance.[Floor Life Cond degC/%RH], qryCompliance.[Standard Soak Requirements Time (hrs)], qryCompliance.[Standard Soak Requirements Cond degC/%RH], qryCompliance.[Accelerated Soak Requirements Time (hrs)], qryCompliance.[Accelerated Soak Requirements Cond degC/%RH], qryCompliance.Package, qryCompliance.Packaging, qryCompliance.QtyperReel, qryCompliance.LeadFinish, qryCompliance.BodyLength, qryCompliance.BodyWidth, qryCompliance.BodyHeight, qryCompliance.LeadSpacing, qryCompliance.UserField4, qryCompliance.Marking, qryCompliance.Action, qryMachine_Data.Location, qryMachine_Data.Machine, qryMachine_Data.Type, qryMachine_Data.TapeSize, qryMachine_Data.Status, qryMachine_Data.Applied, qryMachine_Data.Comment, qryMachine_Data.[INSP Time], qryRegulatoryCompliance.US_Safety_Standard_Link, qryRegulatoryCompliance.US_UL_Qualification_Safety_Report_Link, qryRegulatoryCompliance.UL_Inspection_Report_Link, qryRegulatoryCompliance.[USA_UL_Catagory_&_File_Number_Link], qryRegulatoryCompliance.[UL_Canada_Catagory_&_File_Number_Link], qryRegulatoryCompliance.[EU-CENELEC_SafetyStandard_Link], qryRegulatoryCompliance.EU_CE_Safety_Report_Link, qryRegulatoryCompliance.[EU-CENELEC_EmissionsStandard_Link], qryRegulatoryCompliance.EU_CE_Emissions_Report_Link, qryRegulatoryCompliance.[EU-CENELEC_ImmunityStandard_Link], qryRegulatoryCompliance.EU_CE_Immunity_Report_Link, qryCompliance.NewSupCatNumber, qryCompliance.[New SupplierName], qryCompliance.tblAVLforImportNewToOldXref_1.[Current SupCatalogueNum], qryCompliance.tblAVLforImportNewToOldXref_1.CurrentSupplierName, qryCompliantPressFit.MfgToolNumber, qryCompliantPressFit.ToolUsed, qryCompliantPressFit.HoleDiameter, qryCompliantPressFit.FPP, qryCompliantPressFit.NVISIONToolNumber, qryCompliantPressFit.CorrectTool, qryCompliantPressFit.tblCompliantPressFitReferenceDocs.Ref_Doc_Link, qryCompliantPressFit.tblCompliantPressFitReferenceDocs_1.Ref_Doc_Link, qryCompliantPressFit.tblCompliantPressFitReferenceDocs_2.Ref_Doc_Link, qryCompliantPressFit.Picture, qryCompliantPressFit.Comments, qryCrimper.CrimperNumber, qryCrimper.CrimperTerminalWireSize, qryCrimper.CrimperWireGuage, qryCrimper.tblCrimperReferenceDocs.ReferenceDocLink, qryCrimper.tblCrimperReferenceDocs_1.ReferenceDocLink, qryCrimper.CrimperComments, qryCompliance.Preferability_Name, qryCompliance.Safety_Controlled_Info, qryRegulatoryCompliance.UL_Report_Location, qryPrepData.Machine_Name, qryPrepData.Lead_Spacing, qryPrepData.Body_Calibration, qryPrepData.Body_Calibration_Diagram, qryPrepData.Cut_Length, qryPrepData.Body_Clearance, qryPrepData.Machine_Manual, qryPrepData.Hand_Prep_Data, qryPrepData.Notes
FROM (qryCrimper RIGHT JOIN ((qryProduct_Class RIGHT JOIN (qryMachine_Data RIGHT JOIN (qryRegulatoryCompliance RIGHT JOIN qryCompliance ON qryRegulatoryCompliance.StockCode = qryCompliance.StockCode) ON qryMachine_Data.PartNumber = qryCompliance.StockCode) ON qryProduct_Class.StockCode = qryCompliance.StockCode) LEFT JOIN qryCompliantPressFit ON qryCompliance.SupplierCatalogueNumber = qryCompliantPressFit.MfgPartNumber) ON qryCrimper.SupplierCatalogueNumber = qryCompliance.SupplierCatalogueNumber) LEFT JOIN qryPrepData ON qryCompliance.StockCode = qryPrepData.StockCode;

 

[Remou]

There seems to be a bar to it running at all. I cannot see how you can have these names:
qryCompliantPressFit.tblCompliantPressFitReferenceDocs.Ref_Doc_Link,
qryCompliantPressFit.tblCompliantPressFitReferenceDocs_1.Ref_Doc_Link,
qryCompliantPressFit.tblCompliantPressFitReferenceDocs_2.Ref_Doc_Link,

There are too many dots.

 

[sgidley]

Thank you for your reply, Remou.

This code does work, and the reason the SQL is coded that way is because those 3 are actually a reference to the same field in the same table. The Query is set up to treat this one table as 3 different tables in order to access several parts of the table for the same query. But to be sure this wasn't the main cause of my problem, I re-structured the database and made duplicate tables with different table names and field names, to get rid of any "2 dot" scenarios. This may have improved performance, but I am still getting a dramatic difference in how long an Append Query takes vs a Select Query.

I have done some further analysis on my problem. I put in some Criteria in the Select Query to limit the number of entries to the Append-Query. Surprisingly, whether the Append Query appends 0 records, 4 records, or all 20,000 records, the query takes approx 4 1/2 minutes for each case. If I can do a Select Query in seconds, why 4 1/2 minutes with the INSERT INTO statement when nothing ends up getting inserted at all? What additional processing is the INSERT INTO clause causing?

Here is the updated code:
INSERT INTO tblTotal_Compliance_Data ( StockCode, qryCompliance_Description, Version, PartCategoryName, ProductClass, qryProduct_Class_Description, ComplianceStatus, Name, SupplierName, strSupplierType, SupplierCatalogueNumber, DataSheetLink, ConversionCQRNumber, CertLink, [NVISION Pb Free Build Date], [EU-RoHS Compliant Date], RoHSExemptCode, [RoHS Exemption], [China-RoHS Review Date], [China-RoHS Compliant], China_EFUP, [Korea-RoHS Review Date], [Korea-RoHS Compliant], [Korea-RoHS Misc], CAD, ChromVI, Pb, Hg, PPBs, PBDEs, RoHSComplyStatment, RoHSComplySymbol, ASB, Azo, [PbPVCCables)], CFCs, HCFCs, PCBs, PolyNaphthalenes, Radioactive, ChlorinatedParaffins, TBTTPT, TBTO, Sb, [As], Be, Bi, BromFlameRetardants, Ni, Phthalates, Se, PVC, MoistureLevel, [Floor Life Time], [Floor Life Cond degC/%RH], [Standard Soak Requirements Time (hrs)], [Standard Soak Requirements Cond degC/%RH], [Accelerated Soak Requirements Time (hrs)], [Accelerated Soak Requirements Cond degC/%RH], Package, Packaging, QtyperReel, LeadFinish, BodyLength, BodyWidth, BodyHeight, LeadSpacing, UserField4, Marking, [Action], Location, Machine, Type, TapeSize, Status, Applied, Comment, [INSP Time], US_Safety_Standard_Link, US_UL_Qualification_Safety_Report_Link, UL_Inspection_Report_Link, [USA_UL_Catagory_&_File_Number_Link], [UL_Canada_Catagory_&_File_Number_Link], [EU-CENELEC_SafetyStandard_Link], EU_CE_Safety_Report_Link, [EU-CENELEC_EmissionsStandard_Link], EU_CE_Emissions_Report_Link, [EU-CENELEC_ImmunityStandard_Link], EU_CE_Immunity_Report_Link, NewSupCatNumber, [New SupplierName], [Current SupCatalogueNum], CurrentSupplierName, MfgToolNumber, ToolUsed, HoleDiameter, FPP, NVISIONToolNumber, CorrectTool, Ref_Doc_Link, Ref_Doc_Link_1, Ref_Doc_Link_2, Picture, Comments, CrimperNumber, CrimperTerminalWireSize, CrimperWireGuage, ReferenceDocLink, ReferenceDocLink_2, CrimperComments, Preferability_Name, Safety_Controlled_Info, UL_Report_Location, Machine_Name, Lead_Spacing, Body_Calibration, Body_Calibration_Diagram, Cut_Length, Body_Clearance, Machine_Manual, Hand_Prep_Data, Notes )

SELECT qryCompliance.StockCode, qryCompliance.Description, qryCompliance.Version, qryCompliance.PartCategoryName, qryProduct_Class.ProductClass, qryProduct_Class.Description, qryCompliance.ComplianceStatus, qryCompliance.Name, qryCompliance.SupplierName, qryCompliance.strSupplierType, qryCompliance.SupplierCatalogueNumber, qryCompliance.DataSheetLink, qryCompliance.ConversionCQRNumber, qryCompliance.CertLink, qryCompliance.[NVISION Pb Free Build Date], qryCompliance.[EU-RoHS Compliant Date], qryCompliance.RoHSExemptCode, qryCompliance.[RoHS Exemption], qryCompliance.[China-RoHS Review Date], qryCompliance.[China-RoHS Compliant], qryCompliance.China_EFUP, qryCompliance.[Korea-RoHS Review Date], qryCompliance.[Korea-RoHS Compliant], qryCompliance.[Korea-RoHS Misc], qryCompliance.CAD, qryCompliance.ChromVI, qryCompliance.Pb, qryCompliance.Hg, qryCompliance.PPBs, qryCompliance.PBDEs, qryCompliance.RoHSComplyStatment, qryCompliance.RoHSComplySymbol, qryCompliance.ASB, qryCompliance.Azo, qryCompliance.[PbPVCCables)], qryCompliance.CFCs, qryCompliance.HCFCs, qryCompliance.PCBs, qryCompliance.PolyNaphthalenes, qryCompliance.Radioactive, qryCompliance.ChlorinatedParaffins, qryCompliance.TBTTPT, qryCompliance.TBTO, qryCompliance.Sb, qryCompliance.As, qryCompliance.Be, qryCompliance.Bi, qryCompliance.BromFlameRetardants, qryCompliance.Ni, qryCompliance.Phthalates, qryCompliance.Se, qryCompliance.PVC, qryCompliance.MoistureLevel, qryCompliance.[Floor Life Time], qryCompliance.[Floor Life Cond degC/%RH], qryCompliance.[Standard Soak Requirements Time (hrs)], qryCompliance.[Standard Soak Requirements Cond degC/%RH], qryCompliance.[Accelerated Soak Requirements Time (hrs)], qryCompliance.[Accelerated Soak Requirements Cond degC/%RH], qryCompliance.Package, qryCompliance.Packaging, qryCompliance.QtyperReel, qryCompliance.LeadFinish, qryCompliance.BodyLength, qryCompliance.BodyWidth, qryCompliance.BodyHeight, qryCompliance.LeadSpacing, qryCompliance.UserField4, qryCompliance.Marking, qryCompliance.Action, qryMachine_Data.Location, qryMachine_Data.Machine, qryMachine_Data.Type, qryMachine_Data.TapeSize, qryMachine_Data.Status, qryMachine_Data.Applied, qryMachine_Data.Comment, qryMachine_Data.[INSP Time], qryRegulatoryCompliance.US_Safety_Standard_Link, qryRegulatoryCompliance.US_UL_Qualification_Safety_Report_Link, qryRegulatoryCompliance.UL_Inspection_Report_Link, qryRegulatoryCompliance.[USA_UL_Catagory_&_File_Number_Link], qryRegulatoryCompliance.[UL_Canada_Catagory_&_File_Number_Link], qryRegulatoryCompliance.[EU-CENELEC_SafetyStandard_Link], qryRegulatoryCompliance.EU_CE_Safety_Report_Link, qryRegulatoryCompliance.[EU-CENELEC_EmissionsStandard_Link], qryRegulatoryCompliance.EU_CE_Emissions_Report_Link, qryRegulatoryCompliance.[EU-CENELEC_ImmunityStandard_Link], qryRegulatoryCompliance.EU_CE_Immunity_Report_Link, qryCompliance.NewSupCatNumber, qryCompliance.[New SupplierName], qryCompliance.[Current SupCatalogueNum], qryCompliance.CurrentSupplierName, qryCompliantPressFit.MfgToolNumber, qryCompliantPressFit.ToolUsed, qryCompliantPressFit.HoleDiameter, qryCompliantPressFit.FPP, qryCompliantPressFit.NVISIONToolNumber, qryCompliantPressFit.CorrectTool, qryCompliantPressFit.Ref_Doc_Link, qryCompliantPressFit.Ref_Doc_Link_1, qryCompliantPressFit.Ref_Doc_Link_2, qryCompliantPressFit.Picture, qryCompliantPressFit.Comments, qryCrimper.CrimperNumber, qryCrimper.CrimperTerminalWireSize, qryCrimper.CrimperWireGuage, qryCrimper.ReferenceDocLink, qryCrimper.ReferenceDocLink_2, qryCrimper.CrimperComments, qryCompliance.Preferability_Name, qryCompliance.Safety_Controlled_Info, qryRegulatoryCompliance.UL_Report_Location, qryPrepData.Machine_Name, qryPrepData.Lead_Spacing, qryPrepData.Body_Calibration, qryPrepData.Body_Calibration_Diagram, qryPrepData.Cut_Length, qryPrepData.Body_Clearance, qryPrepData.Machine_Manual, qryPrepData.Hand_Prep_Data, qryPrepData.Notes

FROM (qryCrimper RIGHT JOIN ((qryProduct_Class RIGHT JOIN (qryMachine_Data RIGHT JOIN (qryRegulatoryCompliance RIGHT JOIN qryCompliance ON qryRegulatoryCompliance.StockCode = qryCompliance.StockCode) ON qryMachine_Data.PartNumber = qryCompliance.StockCode) ON qryProduct_Class.StockCode = qryCompliance.StockCode) LEFT JOIN qryCompliantPressFit ON qryCompliance.SupplierCatalogueNumber = qryCompliantPressFit.MfgPartNumber) ON qryCrimper.SupplierCatalogueNumber = qryCompliance.SupplierCatalogueNumber) LEFT JOIN qryPrepData ON qryCompliance.StockCode = qryPrepData.StockCode;