Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do you need to know anything about the orders or order lines, or just a count and that's it? –  Jon Seigel Aug 13 '11 at 21:15
    
Since you said "a count for orders that are going to be shipping today", is there any timestamp field involved ? –  RolandoMySQLDBA Aug 14 '11 at 16:45
    
you could use a trigger on insert/update to check if it's shipped or whatever, then have that update a counter table. Usually that's the fast way around a slow count. –  xenoterracide Aug 14 '11 at 18:30

2 Answers 2

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 :-).

share|improve this answer
    
+1 for suggesting your solution despite of lack of complete information. It's this type of initiative that gets others thinking and coming to the table. –  RolandoMySQLDBA Aug 15 '11 at 17:57
up vote 1 down vote accepted
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.

share|improve this answer
    
Actually, it is rewarding to see someone come to their own realization of a solution. It also prompts others in the DBA.SE to suggest alternative answers. Everyone can learn from your answer as well others. +1 !!! –  RolandoMySQLDBA Aug 15 '11 at 17:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.