Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    6

    Unanswered: Nested Recordsets

    I need to decrease the amount of time it takes to return a set of data of the following format:

    State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]

    current behavior:
    1. currently user selects state all counties are returned from db
    2. foreach county cities are returned
    3. foreach city streets are returned
    4. objects hydrated with data & display drawn

    As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Unclear what you want here. If you just want to return all the counties, cities, streets, etc for a given State, just use a multi-table query with indexed foreign keys.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    Are you using Cursors or subqueries to retrive all those informations ?
    Sudar

    --
    My Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hydrated????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Look it up in Books Online, Rudy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you, but i couldn't find it

    would you be kind enough to give me the exact url
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ha! Made you look!

    Sorry, Rudy, but for some reason at 12:30 last night the idea of you searching Books Online for "hydrated" seemed hillariously funny to me.

    D'oh!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, well, i even tried google, searching for "microsoft sql server hydrate"

    found this -- http://www.devarticles.com/showblog/...andwich-please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The order of data retrieval looks a little backwards, - should you let the user select a country first? And don't you have a relation between countries and states?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •