Query Taking too long to run

[Synergy]

The following query is using linked SQL Tables. It takes almost a minute to
run to return 210 records from about 30,000. I believe all of the records
are being returned from the SQL Server and processed by JET, but don't know
why. It is the (Shipping.Shipped) Is Null criteria which is causing the
slowdown.


INSERT INTO [_Task Scheduler Unshipped] ( ordDetID, [Qty Shipped] )
SELECT [Order Entry ST Products].ordDetID, Sum([Order Entry ST
Products].detQty) AS SumOfdetQty
FROM ([Order Entry ST Products Shipped] LEFT JOIN Shipping ON [Order Entry
ST Products Shipped].ShipID = Shipping.ShipID) RIGHT JOIN [Order Entry ST
Products] ON [Order Entry ST Products Shipped].ordDetID = [Order Entry ST
Products].ordDetID
WHERE (((Shipping.Shipped) Is Null Or (Shipping.Shipped)=0))
GROUP BY [Order Entry ST Products].ordDetID;


I need to speed this up and am at a loss as to what to do. I have more
complex queries than this that run in seconds.

Thanks and any suggestions.

God Bless,

Mark A. Sam

 

[Douglas J. Steele]

Why not try a pass-through query? Pass-through queries run on the server,
not the client.

 

[Van T. Dinh]

Yes. The whole process is being done on your PC.

You should use a Pass-Through Query and the work is done
on the MS-SQL Server which means that no data needs to be
transferred to your PC and the insertion of Records will
be much faster.

There are differences between JET SQL and MS-SQL Server
SQL but it looks like your SQL String can be processed by
MS-SQL Server without too many problems

HTH
Van T. Dinh
MVP (Access)

 

[Mark A. Sam]

Thanks for the Pass Through query suggestion Doug and Van. That did the
trick. Now the form opens in less then 5 seconds.

God Bless,

Mark