Get a count of shippable orders

Question

I have two tables: orders and order_lines. I need a fast, inexpensive way to display a count for orders that are going to be shipping today. The order_lines table is associated with the orders table view a foreign key based on order id. A line is shippable if the shipped field > 0. An order is "shippable" if any of its lines have a qty of shipped.

Right now I basically have a query that reads through the orders, then through the lines (via sqlalchemy in python), and then if any of the lines can be shipped a counter is incremented. This works but it is very slow.

Is there some way on the database side or can create something to speed this up?

Answer 1

From your question I'd translate that you have the following table structure:

Orders:
    OrderId,
    OrderDetails

OrderLines:
    OrderLineId,
    OrderId,
    Shipped

I'd use the following statements for reading data:

-- shippable order lines
Select ol.*
from OrderLines ol
where ol.Shipped > 0

-- shippable orders
select *
from Order o
where Exists
(
    select *
    from OrderLines ol
    where ol.Shipped > 0
    and ol.OrderId = o.OrderId
)

In case the last script is again slow, than I'd guess that you need an index on the column OrderId in tabe OrderLines. In case this also doesn't work, then come back with the query that you used, the real tables definition and, most important, the EXPLAIN ANALYZE of your query.

PS: please keep in mind that I used the MS SQL flavor of the language, you might need small adjustments to make it work properly in PGSQL :-).

Answer 2

select count(distinct(orders.order_id)) from orders,order_lines 
where orders.order_id=order_lines.order_id and order_lines.shipped > 0

I was over thinking this. The above statement gave me what i wanted almost instantly were before i was actually reading through all the records. At any rate I am not sure if its bad form to answer your own question but felt I needed to at least include my solution.