Sum So Slow

[lisbet]

I have a query which takes orders for today and sums the order quantity for each stock code.
When this is put on the networked pc it takes 5 minutes.
Why???
If I sum all of the orders it is immediate.
If I choose the stock codes from an In() list it is immediate, even if I choose exactly the same as was ordered.
I can see no reason for this.
I have even tried deleting and re-writing the query.
I have other more complicated queries that this doesn't happen to.
I'm very confused and frustrated.
Please help.

 

[Craig0201]

Lisbet,

Is there an index on the date field? This could be the cause in a big table.....

Craig

 

[lisbet]

Craig0201,

I can get the information without any problem it is just when i try to sum the quantity field on each stock code that it does this. So I don't think it is the size of the table which affects the speed of this particular query. But I'm not sure. How do you know if there is an index?
Thanks
Lisbet

 

[tlbroadbent]

Lisbet,

Post your query so we can see how you are obtaining the Sums. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[lisbet]

The query i am using is quite simple

SELECT OrderLine.stockCode, Sum(ORderLine.orderQuantity) AS SumOfOrderQuantity FROM OrderLine WHERE OrderLine.despatchDate = Date()

If I just run

SELECT OrderLine.stockCode, OrderLine.orderQuantity FROM OrderLine WHERE OrderLine.despatchDate = Date()

There is no problem

If I run

SELECT Sum(OrderLine.orderQuantity) AS SumOfOrderQuantity FROM OrderLine WHERE OrderLine.despatchDate = Date()

There is also no problem

If I run

SELECT OrderLine.stockCode, Sum(OrderLine.orderQuantity) AS SumOfOrderQuantity FROM OrderLine WHERE OrderLine.despatchDate = Date() AND OrderLIne.stockCode IN( a list of all the stock codes on order for today)

There is also no problem.

This is why I am so confused, as if all of these work immediately why does the first one take so long.

Thanks for your help

 

[tlbroadbent]

I'm surprised that the first query runs at all. It lacks a Group By clause to go with the Sum() function. Which version of Microsoft Access are you running?

Try the following query.

SELECT
OrderLine.stockCode,
Sum(ORderLine.orderQuantity) AS SumOfOrderQuantity
FROM OrderLine
WHERE OrderLine.despatchDate = Date()
Group By OrderLine.stockCode
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[lisbet]

I'm sorry that my syntax isn't great as I haven't been using databases for very long and I am trying to recall it from memory. Access has automatically but the group by clause into the statement, so I don't think that it is that which is causing the problem. Thanks anyway.

 

[tlbroadbent]

Sorry, but you didn't post the Group By clause so I assumed it was missing when you ran the query. I apologize for the bad assumption.

Back to the Index question. Check if there is an index on the columns DispatchDate and StockCode. Open the table in design mode, click on each column. Near the bottom of the design screen you'll see an Indexed property. Make sure it is set to Yes (Duplicates OK) for both columns.

Let us know the result.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[lisbet]

There are indexes set up on both the despatchDate and the stockCode fields. However the I have linked the tables from another database set up on infomix so I cannot alter the design of the tables.
I have overcome the problem by making the query perform a the calculation first(orderNo) on the orderNo field from the orderlink table, which then gives me the same results for the other fields. Which is strange as it is part of a combined primary key and not unique in the table and I would have thought that this would have taken longer.
The whole thing has definitely baffled me. I cannot see whhy it behaves this way.
Thanks for your time and help.

 

[tlbroadbent]

Linked Informix table? That's a very important piece of information to omit. We've spent a lot of time trying to optimize an Access query when the query involves not only Access but an Informix server.

Access attempts to optimize queries on linked tables by having the server do as much work as possible. The amount of work done on the server will vary depending on the query. My guess at this point would be that Access is doing more work on the problem query resulting in slower performance. I can't explain why that would occur given the performance of the other queries. Sometimes, optimizers just don't optimize very well.

In my experience, queries against linked tables in Access generally perform poorly. One way to optimize queries against tables on a server is to create pass-through queries where all of the work of selection, summarization, etc. is done on the server. Another option is to create a view on the server and select from the view. The view can handle all of the summarization.

I'm not familiar with Informix but suspect it should be possible to use either option. Of course, if you can't create objects on the Informix DB, you'll have to use pass-through queries. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.