Query running slow

D

Daniel

Hi All,

I have got a query with 7-8 tables. Some of them are linked from SQL
server and some of them are local Access table. I just updated this
query and added another creteria and it became very slow (about 10
minutes) to return the result. If I remove one of the local table, it
just needs around 5-8 seconds. But I do need this table in the query.

There are three fields in the table, one of Number type field is
primary key. The creteria is nothing to do with the fields in the
local table.

What's the possible reason for that?

Any help will be appreciated.

Thanks
Daniel
 
D

Daniel

Please post the SQL statement for this query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.










- Show quoted text -
Click to expand...

It is a big query, TblTempPackingSchedule_Step3 is the local table I
mentioned. I have compact and repaired the database but no luck.

SELECT TblDbMaintenanceADS.DateDelivery,
TblDbMaintenanceADS.PackingSelection,
TblDbMaintenanceADS.DateEstDelivery, TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.MaintADSRef, TblDbMaintenanceADS.MaintADSRefNo,
TblDbMaintenanceADS.SiteWorkOnly, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS sort_DateDelivery, IIf([TblDbMaintenanceADS]!
[Pickup]=-1,"Pick Up",IIf([TblDbMaintenanceADS]!
[VehicalType]="Direct","Direct",IIf([TblDbMaintenanceADS]!
[VehicalType]="Crate","Fulton",""))) AS Pickup1,
IIf(IsNull([DateEstDelivery]) Or
IsNull([PackingLeadTime]),Null,Workday([DateEstDelivery],
[PackingLeadTime])) AS InTransit1, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS Prio1, TblDbMaintenanceADS.PackingDate,
TblDbMaintenanceADS.DateOnSiteComp, TblDbMaintenanceADS.Packed,
TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.DateOnSiteRequest,
TblSuburbListingsBranch.InransitLeadTime,
TblSuburbListingsBranch.BranchLeadTime,
IIf(IsNull([DateOnSiteRequest]),"N/A",Workday([DateOnSiteRequest],-
[BranchLeadTime])) AS BranchReq, IIf([BranchReq]="N/A","N/
A",Workday([BranchReq],-[InransitLeadTime])) AS ReqTransit,
TblDbMaintenanceADS.MaintADSId, TblDbMaintenanceADS.DateADSMaintComp,
TblSuburbListingsBranch.LocationID,
TblTempPackingSchedule_Step3.Complete,
IIf(IsNull([Complete]),"Complete","") AS Complete1,
TblDBADSFloor.Process AS ManufRespon,
Q_TblCrateIDAvailability.CrateID, TblDbMaintenanceADS.TransitDone,
TblDbMaintenanceADS.InTransit,
TblDbMaintenanceADS.TransittedTimestamp, TblDbMaintenanceADS.Pickup,
TblDbMaintenanceADS.DeliverConf, TblDbMaintenanceADS.BranchRecDone,
Q_TblCrateIDAvailability.DocketPrinted,
TblDbMaintenanceADS.NationalReceiveDone,
TblDbMaintenanceADS.NationalReceiveDate,
TblDbMaintenanceADS.BranchRequestDone,
TblDbMaintenanceADS.BranchRequestDate,
TblDbMaintenanceADS.NationalTransitDone,
TblDbMaintenanceADS.NationalTransitDate,
TblDbMaintenanceADS.TransitDocketNo,
TblDbMaintenanceADS.SelectForTransit, TblDbMaintenanceADS.BanchRec,
InStr([CustCompanyDimension3],"Dist") AS test, TblDBADSFloor.Qty AS
Packages, TblDBADSFloor.PackageConfirmation, TblDBADSFloor.BayNo,
TblDBADSFloor.MaintNumber AS MaintNo, TblDBADSFloor.Process AS
Department, TblDBADSFloor.ADSDone,
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location]))))
AS Location1
FROM (((((((tblClaytonsJobData LEFT JOIN TblClaytonsJobsDetails ON
tblClaytonsJobData.JobNumber = TblClaytonsJobsDetails.JobNumber) LEFT
JOIN TblSuburbListingsBranch ON
TblClaytonsJobsDetails.DedicatedLocation =
TblSuburbListingsBranch.LocationID) LEFT JOIN TblDbCustomerCompany ON
tblClaytonsJobData.Customer = TblDbCustomerCompany.CustCompanyName)
RIGHT JOIN TblDBADSFloor ON tblClaytonsJobData.JobNumber =
TblDBADSFloor.JobNumber) LEFT JOIN TblDBADSDPAndFloor ON
TblDBADSFloor.MaintNumber = TblDBADSDPAndFloor.MaintNumber) LEFT JOIN
TblDbMaintenanceADS ON TblDBADSDPAndFloor.MaintADSID =
TblDbMaintenanceADS.MaintADSId) LEFT JOIN TblTempPackingSchedule_Step3
ON TblDbMaintenanceADS.MaintADSId =
TblTempPackingSchedule_Step3.MaintADSId) LEFT JOIN
Q_TblCrateIDAvailability ON TblDbMaintenanceADS.MaintADSId =
Q_TblCrateIDAvailability.MaintNo
WHERE (((TblDbMaintenanceADS.MaintADSType)="E" Or
(TblDbMaintenanceADS.MaintADSType)="F") AND
((TblDbMaintenanceADS.SiteWorkOnly) Is Null Or
(TblDbMaintenanceADS.SiteWorkOnly)=0) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True)) OR
(((TblDbMaintenanceADS.MaintADSType)="H") AND
((TblDbMaintenanceADS.SiteWorkOnly)=True) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True))
ORDER BY IIf(IsNull([TblDbMaintenanceADS]![DateDelivery]),2,1),
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location])))),
TblDbMaintenanceADS.DateDelivery, TblDbMaintenanceADS.PackingDate;
 
J

Jerry Whittle

That is one ugly monster!

Is the TblTempPackingSchedule_Step3.MaintADSId field the primary key or
indexed?

The same question goes for TblDbMaintenanceADS.MaintADSId?

What happens when you remove either or both of these two lines:
TblTempPackingSchedule_Step3.Complete,
IIf(IsNull([Complete]),"Complete","") AS Complete1,

I'm especially interested in the second line as it looks like you are
dealing with nulls plus displaying the same data twice.

One thing that might really speed things up is to create a view in SQL
Server that joins all the tables needed there into one query. That way a lot
of the work would be done by the SQL Server engine and you wouldn't need to
bring so much data into Access for it crunch.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Daniel said:
Please post the SQL statement for this query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.










- Show quoted text -
Click to expand...

It is a big query, TblTempPackingSchedule_Step3 is the local table I
mentioned. I have compact and repaired the database but no luck.

SELECT TblDbMaintenanceADS.DateDelivery,
TblDbMaintenanceADS.PackingSelection,
TblDbMaintenanceADS.DateEstDelivery, TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.MaintADSRef, TblDbMaintenanceADS.MaintADSRefNo,
TblDbMaintenanceADS.SiteWorkOnly, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS sort_DateDelivery, IIf([TblDbMaintenanceADS]!
[Pickup]=-1,"Pick Up",IIf([TblDbMaintenanceADS]!
[VehicalType]="Direct","Direct",IIf([TblDbMaintenanceADS]!
[VehicalType]="Crate","Fulton",""))) AS Pickup1,
IIf(IsNull([DateEstDelivery]) Or
IsNull([PackingLeadTime]),Null,Workday([DateEstDelivery],
[PackingLeadTime])) AS InTransit1, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS Prio1, TblDbMaintenanceADS.PackingDate,
TblDbMaintenanceADS.DateOnSiteComp, TblDbMaintenanceADS.Packed,
TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.DateOnSiteRequest,
TblSuburbListingsBranch.InransitLeadTime,
TblSuburbListingsBranch.BranchLeadTime,
IIf(IsNull([DateOnSiteRequest]),"N/A",Workday([DateOnSiteRequest],-
[BranchLeadTime])) AS BranchReq, IIf([BranchReq]="N/A","N/
A",Workday([BranchReq],-[InransitLeadTime])) AS ReqTransit,
TblDbMaintenanceADS.MaintADSId, TblDbMaintenanceADS.DateADSMaintComp,
TblSuburbListingsBranch.LocationID,
TblTempPackingSchedule_Step3.Complete,
IIf(IsNull([Complete]),"Complete","") AS Complete1,
TblDBADSFloor.Process AS ManufRespon,
Q_TblCrateIDAvailability.CrateID, TblDbMaintenanceADS.TransitDone,
TblDbMaintenanceADS.InTransit,
TblDbMaintenanceADS.TransittedTimestamp, TblDbMaintenanceADS.Pickup,
TblDbMaintenanceADS.DeliverConf, TblDbMaintenanceADS.BranchRecDone,
Q_TblCrateIDAvailability.DocketPrinted,
TblDbMaintenanceADS.NationalReceiveDone,
TblDbMaintenanceADS.NationalReceiveDate,
TblDbMaintenanceADS.BranchRequestDone,
TblDbMaintenanceADS.BranchRequestDate,
TblDbMaintenanceADS.NationalTransitDone,
TblDbMaintenanceADS.NationalTransitDate,
TblDbMaintenanceADS.TransitDocketNo,
TblDbMaintenanceADS.SelectForTransit, TblDbMaintenanceADS.BanchRec,
InStr([CustCompanyDimension3],"Dist") AS test, TblDBADSFloor.Qty AS
Packages, TblDBADSFloor.PackageConfirmation, TblDBADSFloor.BayNo,
TblDBADSFloor.MaintNumber AS MaintNo, TblDBADSFloor.Process AS
Department, TblDBADSFloor.ADSDone,
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location]))))
AS Location1
FROM (((((((tblClaytonsJobData LEFT JOIN TblClaytonsJobsDetails ON
tblClaytonsJobData.JobNumber = TblClaytonsJobsDetails.JobNumber) LEFT
JOIN TblSuburbListingsBranch ON
TblClaytonsJobsDetails.DedicatedLocation =
TblSuburbListingsBranch.LocationID) LEFT JOIN TblDbCustomerCompany ON
tblClaytonsJobData.Customer = TblDbCustomerCompany.CustCompanyName)
RIGHT JOIN TblDBADSFloor ON tblClaytonsJobData.JobNumber =
TblDBADSFloor.JobNumber) LEFT JOIN TblDBADSDPAndFloor ON
TblDBADSFloor.MaintNumber = TblDBADSDPAndFloor.MaintNumber) LEFT JOIN
TblDbMaintenanceADS ON TblDBADSDPAndFloor.MaintADSID =
TblDbMaintenanceADS.MaintADSId) LEFT JOIN TblTempPackingSchedule_Step3
ON TblDbMaintenanceADS.MaintADSId =
TblTempPackingSchedule_Step3.MaintADSId) LEFT JOIN
Q_TblCrateIDAvailability ON TblDbMaintenanceADS.MaintADSId =
Q_TblCrateIDAvailability.MaintNo
WHERE (((TblDbMaintenanceADS.MaintADSType)="E" Or
(TblDbMaintenanceADS.MaintADSType)="F") AND
((TblDbMaintenanceADS.SiteWorkOnly) Is Null Or
(TblDbMaintenanceADS.SiteWorkOnly)=0) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True)) OR
(((TblDbMaintenanceADS.MaintADSType)="H") AND
((TblDbMaintenanceADS.SiteWorkOnly)=True) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True))
ORDER BY IIf(IsNull([TblDbMaintenanceADS]![DateDelivery]),2,1),
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location])))),
TblDbMaintenanceADS.DateDelivery, TblDbMaintenanceADS.PackingDate;
.
Click to expand...
 
D

Daniel

That is one ugly monster!

Is the TblTempPackingSchedule_Step3.MaintADSId field the primary key or
indexed?

The same question goes for TblDbMaintenanceADS.MaintADSId?

What happens when you remove either or both of these two lines:
TblTempPackingSchedule_Step3.Complete,
IIf(IsNull([Complete]),"Complete","") AS Complete1,

I'm especially interested in the second line as it looks like you are
dealing with nulls plus displaying the same data twice.

One thing that might really speed things up is to create a view in SQL
Server that joins all the tables needed there into one query. That way a lot
of the work would be done by the SQL Server engine and you wouldn't need to
bring so much data into Access for it crunch.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



It is a big query, TblTempPackingSchedule_Step3 is the local table I
mentioned. I have compact and repaired the database but no luck.
Click to expand...
SELECT TblDbMaintenanceADS.DateDelivery,
TblDbMaintenanceADS.PackingSelection,
TblDbMaintenanceADS.DateEstDelivery, TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.MaintADSRef, TblDbMaintenanceADS.MaintADSRefNo,
TblDbMaintenanceADS.SiteWorkOnly, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS sort_DateDelivery, IIf([TblDbMaintenanceADS]!
[Pickup]=-1,"Pick Up",IIf([TblDbMaintenanceADS]!
[VehicalType]="Direct","Direct",IIf([TblDbMaintenanceADS]!
[VehicalType]="Crate","Fulton",""))) AS Pickup1,
IIf(IsNull([DateEstDelivery]) Or
IsNull([PackingLeadTime]),Null,Workday([DateEstDelivery],
[PackingLeadTime])) AS InTransit1, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS Prio1, TblDbMaintenanceADS.PackingDate,
TblDbMaintenanceADS.DateOnSiteComp, TblDbMaintenanceADS.Packed,
TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.DateOnSiteRequest,
TblSuburbListingsBranch.InransitLeadTime,
TblSuburbListingsBranch.BranchLeadTime,
IIf(IsNull([DateOnSiteRequest]),"N/A",Workday([DateOnSiteRequest],-
[BranchLeadTime])) AS BranchReq, IIf([BranchReq]="N/A","N/
A",Workday([BranchReq],-[InransitLeadTime])) AS ReqTransit,
TblDbMaintenanceADS.MaintADSId, TblDbMaintenanceADS.DateADSMaintComp,
TblSuburbListingsBranch.LocationID,
TblTempPackingSchedule_Step3.Complete,
IIf(IsNull([Complete]),"Complete","") AS Complete1,
TblDBADSFloor.Process AS ManufRespon,
Q_TblCrateIDAvailability.CrateID, TblDbMaintenanceADS.TransitDone,
TblDbMaintenanceADS.InTransit,
TblDbMaintenanceADS.TransittedTimestamp, TblDbMaintenanceADS.Pickup,
TblDbMaintenanceADS.DeliverConf, TblDbMaintenanceADS.BranchRecDone,
Q_TblCrateIDAvailability.DocketPrinted,
TblDbMaintenanceADS.NationalReceiveDone,
TblDbMaintenanceADS.NationalReceiveDate,
TblDbMaintenanceADS.BranchRequestDone,
TblDbMaintenanceADS.BranchRequestDate,
TblDbMaintenanceADS.NationalTransitDone,
TblDbMaintenanceADS.NationalTransitDate,
TblDbMaintenanceADS.TransitDocketNo,
TblDbMaintenanceADS.SelectForTransit, TblDbMaintenanceADS.BanchRec,
InStr([CustCompanyDimension3],"Dist") AS test, TblDBADSFloor.Qty AS
Packages, TblDBADSFloor.PackageConfirmation, TblDBADSFloor.BayNo,
TblDBADSFloor.MaintNumber AS MaintNo, TblDBADSFloor.Process AS
Department, TblDBADSFloor.ADSDone,
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VI­C",IIf(Trim([Location])="Tasmania","TAS",Trim([Location]))))
AS Location1
FROM (((((((tblClaytonsJobData LEFT JOIN TblClaytonsJobsDetails ON
tblClaytonsJobData.JobNumber = TblClaytonsJobsDetails.JobNumber) LEFT
JOIN TblSuburbListingsBranch ON
TblClaytonsJobsDetails.DedicatedLocation =
TblSuburbListingsBranch.LocationID) LEFT JOIN TblDbCustomerCompany ON
tblClaytonsJobData.Customer = TblDbCustomerCompany.CustCompanyName)
RIGHT JOIN TblDBADSFloor ON tblClaytonsJobData.JobNumber =
TblDBADSFloor.JobNumber) LEFT JOIN TblDBADSDPAndFloor ON
TblDBADSFloor.MaintNumber = TblDBADSDPAndFloor.MaintNumber) LEFT JOIN
TblDbMaintenanceADS ON TblDBADSDPAndFloor.MaintADSID =
TblDbMaintenanceADS.MaintADSId) LEFT JOIN TblTempPackingSchedule_Step3
ON TblDbMaintenanceADS.MaintADSId =
TblTempPackingSchedule_Step3.MaintADSId) LEFT JOIN
Q_TblCrateIDAvailability ON TblDbMaintenanceADS.MaintADSId =
Q_TblCrateIDAvailability.MaintNo
WHERE (((TblDbMaintenanceADS.MaintADSType)="E" Or
(TblDbMaintenanceADS.MaintADSType)="F") AND
((TblDbMaintenanceADS.SiteWorkOnly) Is Null Or
(TblDbMaintenanceADS.SiteWorkOnly)=0) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True)) OR
(((TblDbMaintenanceADS.MaintADSType)="H") AND
((TblDbMaintenanceADS.SiteWorkOnly)=True) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True))
ORDER BY IIf(IsNull([TblDbMaintenanceADS]![DateDelivery]),2,1),
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VI­C",IIf(Trim([Location])="Tasmania","TAS",Trim([Location])))),
TblDbMaintenanceADS.DateDelivery, TblDbMaintenanceADS.PackingDate;
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi Jerry,

Thank you for your reply. I know that's ugly. Hopefully I can find
some time to re-do it in the future. But currently, I just find
another way to fix this problem. Ranther than enter another creteria
in another line, I use

Expr1: IIf([MaintADSType]="E" Or [MaintADSType]="F",
[SiteWorkOnly],IIf([MaintADSType]="H",False,True))

to replace SiteWorkOnly Field and set creteria to false. Then it
return the result pretty quick.

Thanks again!

Daniel
 
G

Gina Whipp

Daniel,

What is the criteria you added, though it might be better to post the SQL of
the query and tell us which table you added that caused the problem. I also
can't help but wonder why you would need 7 tables in one query? Might also
help to know your tables and their fields.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hi All,

I have got a query with 7-8 tables. Some of them are linked from SQL
server and some of them are local Access table. I just updated this
query and added another creteria and it became very slow (about 10
minutes) to return the result. If I remove one of the local table, it
just needs around 5-8 seconds. But I do need this table in the query.

There are three fields in the table, one of Number type field is
primary key. The creteria is nothing to do with the fields in the
local table.

What's the possible reason for that?

Any help will be appreciated.

Thanks
Daniel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Crosstab Query to Existing Table 0
Outlook emails loading very slowly 0
Outlook 2021 export question 0
Dates In Excel 5
Trying to create a report that will list unmatched records by Ticket Primary Key 0
"MSysCompactError", "System resource exceeded" & "You do not have the necessary permissions to use the...." 0
QBF & Query Malfunction 0
List all tables, including document structure 0

Top