View Single Post
  #2 (permalink)  
Old February 17th, 2004, 08:40 AM
planoie's Avatar
planoie planoie is offline
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts

Making multiple selects like you are doing is never the best way. If you have to make calls over and over to the database then you are going to see a real big hit in performance. The way I usually do something like this is to use a bunch of joins in the data to change relational data (best way to store it in the DB) to a "flat table". Then in your asp code you determine when the data changes within each level. You have to store the "last item" for each level you are working with. So you have a single loop that goes thru each row returned and you have several If structures that check for a new occurance of each level.

For Each row
    If row("platoon") <> sLastPlatoon Then
        'output platoon text
    End If
    If row("rank") <> sLastRank Then
        'output rank text
    End If

Although this is a little bit more clumsy, it's a whole lot more efficient than make repeated calls to the database. The storage you have for the data is quite correct, you just need to select it for processing in a different way that is more efficient for the data consumer (ASP). Post back if you need a hand constructing a query to give you a "flat file" structure.

Work smarter, not harder.
Reply With Quote