none
SQL Query design RRS feed

  • Question

  • Lets say I had the following tables:

    Table Items
    {
    itemID int,
    groupID int,
    dateEntered datetime
    description nvarchar(50)
    }

    Table ItemGroups
    {
            groupID int,
            groupDescrition nvarchar(50),
    }


    Item.groupID is an FK to ItemGroup.groupID. 

    Lets say I was using C# and I wanted to execute a couple of sql queries (or maybe just one) to bring in all items that have Item.dateEntered within a certain range. I also want to bring in all of the ItemGroups that are FK's of the items that result from the query that I just described. Hopefully that makes sense. I see a couple of ways to do this.

    1) I could do a query like so, "Select Items.* Fom Items where dateEntered < @endDate and dateEntered > @startDate". Then, I could loop through the records returned by that query, and do one SQL Query for every record that queries against Item.groupID. I recognize this as a bad approach.

    2) I could have 2 queries instead of the 1 + (numItemsReturnedByFirstQuery) queries that would happen if I went with approach #1. The query to bring in the Item records is given above. The query to bring in the item groups would be,
    "SELECT DISTINCT ItemGroups.* From ItemGroups INNER JOIN Items ON ItemGroups.groupID = Items.groupID WHERE Items.dateEntered < @endDate AND Items.dateEntered > @startDate" 
    I see this approach as being better. However, for every type of query I did on the Items table, if I wanted to bring in the groups as well, I would need to write a corresponding query for the ItemGroups table. Hopefully that makes sense.

    3) This approach would again have two queries. The query to bring in items would be the same as the one used in #1 and #2. This time, to bring in the ItemGroups, I would loop through the Items records, and build a string out of the groupId's which would look like this (g1, g2, g3, ...). Then I would do a query of the form, ( "SELECT ItemGroups.* FROM ItemGroups WHERE ItemGroups.groupID IN " + stringIJustMade ). This approace seems a little more scalable than #2, but it also seems slower.

    Can anyone comment on these approaches (hopefully, I have described them well enough)? I'm thinking that, It's probably best to, in some cases use #2 and in some cases use #3 depending on my needs. I'm not really sure though.

    Any comments will help!

    Thanks,
    Jordan
    Tuesday, February 17, 2009 9:29 PM

All replies

  • As you point out, 2 or 3 are better than 1 as this is not scaleable.

    3 can be improved by using a sub query like

    SELECT * from ItemGroups where groupid IN (SELECT groupid from items where items.dateentered<@endate and items.dateentered>@startdate)

    this is the same as yours except it doesn't looping round the item records to get a list of groupid's.

    Whichever you choose you can also you can get multiple recordsets by sending batches of Sql statements like this;

    SELECT * from items where [criteria]; SELECT * from itemGroups where [criteria];

    and then use the ado recordsets NextRecordSet() method to process the results. 

    This is more scaleable as it reduces the roundtrips you make to the database and SQL Server will optimize the query.


    Pl mark as answer or helpful if you found this useful
    Wednesday, February 18, 2009 12:41 PM