How to return as little rows as possible from related table

Question

Hello,

I am new to ADO.net and have this problem:

Let's assume I have these two tables in a SQL Server 2005 database, with these columns:

[Orders]

• OrderID
• OrderDate
• ShopID
• TotalAmount
• TotalTaxAmount
• etc...

[OrdersDetails]

• OrderID
• ShopID
• ItemID
• Quantity
• Amount
• TaxAmount
• etc

I have started a WinForms application to get myself started. In this form, the user can select a list of Shops and select a date range to see all orders from this shop.

I have added a data source from Visual Studio, select both Orders and OrdersDetails table and dragged and dropped the Orders and related OrdersDetails tables into the form as DataGridViews.

When I select a row from the Orders DataGridView, I indeed see the corresponding Orders Details in the second DataGridView as I wanted. I had relationships inside this database and ADO.net caught them up and reflected them in the dataset.

I have then added a method to my typed dataset to get data by the OrderDate, and ShopID column. As the OrdersDetails table does not have an OrderDate column, I could only filter it by ShopID.

The issue is that it is time consuming to get the records from the OrdersDetails as it will retrieve more rows than needed into the DataTable for the OrdersDetails. The problem is that I can only filter the rows from the OrderDetails table by ShopID, which returns way too many records from the database.

Obviously, ADO.net is able to filter them appropriately on the client-side by using the OrderID relationship but it would make much more sense to retrieve only the rows from the OrdersDetails that I actually need.

I have modified my queries getting the data from the second table to add the OrderDate using a join, so I can filter by date when I retrieve the data from the database... however, it causes problems when I try to update my changes due to this foreign column...

I believe there must be an easy way around this, isn't there?

Thanks a lot in advance.

Answer 1

You want to do something like this

SELECT *
FROM OrderDetails
WHERE
    ShopID IN ( @listOfShopIds )
    AND
    OrderID IN (
        SELECT OrderID
        FROM Orders
        WHERE
            OrderDate BETWEEN @dateFrom AND @dateTo
    )

Answer 2

@David it's probably better to write code which expresses your intent and reflects the more performant algorithm, rather than relying on implementation details of the engine to perform the optimization.