Is there a convenient way to break during a database select sequence?

[CHeighlund]

I have a program I'm working on that withdraws information from a database and displays it in a grid on the screen. Due to the way I need to present the information, the program has to pull off three nested database calls, alongside a few others in the middle. (Call one gets all field y values associated with user-chosen field x. Call two gets all field z values associated with each field y so found. Call three gets all presented data associated with each field z so found.)

Due to the hoops I'm jumping through to get the presentation of the data correct, it takes a while to burn my way through from the first values returned to the last ones. Because of this, my boss has requested that I put a 'break' button in the process that would stop the database withdrawals. I can see how I'd do this fairly easily (have the button click on a global boolean and check for it during the loop, breaking if found) but what would this be likely to do to my database connections?

 

[lespaul]

are you sure you need the three nested database calls? maybe there's a way to complete it all in a single SQL statement....maybe if you show some sample data and expected results we could help you minimize the number of queries required. Additionally, adding some indexes to the tables may speed up the process....

as far as the database connections, I don't see that it would be a problem....

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[CHeighlund]

I'm not sure I need it, but that's what I've got set up now and it seems to work.

Presentation format:
Two-dimensional grid with the following axes:

Top <horizontal axis>: Dates in one-month increments.

Left <vertical axis>: Location.

Location has to be divided into two types: Type one, aka 'device', and type two, aka 'device station'. These sets are specific to the user-defined location.

User-requested formatting:
Show device label (device 1, device 2, etc.) with summary of all records for that device type within the date range in question, also user-chosen. This is one of those 'few others' in midstream.
For each device station within the device, show summary of all station records within the date range. (Innermost loop)

For example, if the database had two devices with three stations each for a given location, and the range were across Jan/Apr 2004, then the results would need to look a bit like this:

+---------+---------+---------+---------+---------+
|         |Jan 04   |Feb 04   |Mar 04   |Apr 04   |
+---------+---------+---------+---------+---------+
|Dev 01   |   A     |   A     |   A     |   A     |
+---------+---------+---------+---------+---------+
|Stat 01  |   B     |   B     |   B     |   B     |
+---------+---------+---------+---------+---------+
|Stat 02  |   B     |   B     |   B     |   B     |
+---------+---------+---------+---------+---------+
|Stat 03  |   B     |   B     |   B     |   B     |
+---------+---------+---------+---------+---------+
|Dev 02   |   A     |   A     |   A     |   A     |
+---------+---------+---------+---------+---------+
|Stat 01  |   B     |   B     |   B     |   B     |
+---------+---------+---------+---------+---------+
|Stat 02  |   B     |   B     |   B     |   B     |
+---------+---------+---------+---------+---------+
|Stat 03  |   B     |   B     |   B     |   B     |
+---------+---------+---------+---------+---------+

I did it as three nested calls because that gives me a convenient set of loops to put the results in the right place on the grid. If you can think of a way to do this with fewer calls, you've got my full attention on the issue.

 

[lespaul]

So the user selects a date range and one or more devices to show the stations for that device? So each set of Stat 01, Stat 02 and Stat 03 are specific to the Dev that is listed before them?

How is the data structured in the table(s)?

 

[CHeighlund]

The user selects a date range and a location, which may have one or more stations on it. Each station has a certain device type, so it is possible for the location to have multiple device types (dev 0X) on it. Stations are to be grouped by device type within each location. And yes, each station has only one type, so each set of Stat 0Y are specific to the Dev 0X listed before them.

The whole thing inside the database is a single massive recordset which contains record number (key), location, device code, station number, date, and a few other values. Some of these will be used for fine-tuning the results, and at least two of them are needed in my program due to a slight idiosyncracy of the end-user's request. One is a quantity field, which is normally set to 1, but not always, and another is a marker field whose value I'm supposed to look at to determine whether or not the value in the quantity field should be included in the resulting output. That part was fairly easy to figure out, but still requires that I keep the innermost call intact, as I won't know until I get there whether or not I've got to include that value.

 

[lespaul]

what are the SQL statements you are running?

 

[harebrain]

I think lespaul is on to something here. It sounds as though you're thrashing that db to death. Unless there is a prohibitive volume of data, you'll be better off finding the minimum number of queries that will return the data you need, then manipulating the data in memory for presentation.