Query Taking too long to run

S

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
 
D

Douglas J. Steele

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

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)
 
M

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
 

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


Top