Over my head, SQL

Factual Questions
1

There is a task that is currently written in FileMaker and it does everything it needs to do…but not quickly enough, so we’re contemplating porting that process to some flavor of SQL (let’s say MySQL for now) to bypass FileMaker’s speed limitations.

The “over my head” part comes from the fact that it is a nested looping script and I neither know how to write incrementing loops in SQL nor how to write some kind of static nonlooping query that would do the same thing.

There is a table called Bus. The looping script goes through each record of it. A start date and an end date is specified for a report.

… For each record in Bus a loop is started at Start Date, does stuff, goes to next date, and so on until End Date.

… For each Date, a list of Client record IDs is obtained, of Clients who were attached to that Bus that were active on that Date; and a loop is started at midnight and increments one hour at a time until 11 PM.

… For each Hour, for that Bus, on that Date, the clients on the client list is looped through and a reading taken, and then this loop increments to the next hour.

Within that list of Clients, parameters from each indiv client are obtained and then
a quantitative value is read in from a Readings table, the specific Readings record being identified by a combination of field values some of which come from the Bus record, some from the Date or from parameters in yet other tables that are specific to the Day of Week of that Date, some from the specific Hour, and some from the specific Client under consideration. As this inner loop runs, that reading is tossed into an equation to yield a result which is added to the result of all prior clients within this same hour / same date / same Bus, and the aggregate total for that Bus/Date/Hour is written out as a cell value in the report. The resulting value is “per the hour” and aggregates all the clients’s readings for that hour.
In FileMaker these loops are written as a script with this kind of syntax:

Loop
…Exit Loop if [specify exit condition]
…do stuff
… Loop
… Exit Loop If [specify exit condition]
… do stuff
… increment something to approach exit condition
… End Loop
… increment something to approach exit condition
End Loop
How would one do such a thing in a SQL environment? Would it normally be done outside of the SQL language environment in a front-end environment and, if so, is php the best place to write the loop syntax, or should I be investigating things such as Brio or Crystal Reports for this kind of task? (I assume Brio and Crystal are still around due to some things you can do better in them than in php?)

2

I don’t see anything in there that looks likely to require cursors (the SQL constructs that would operate loops). If I had that project, I’d try to build it using set-based queries, grouping by the client ID, date, and hour of day. Your front-end (Crystal Reports or whatever you end up using) would just be sending the date range parameters to a view or stored procedure, the data processing would all happen on the database server. This is, frankly, the sort of thing that databases were invented for …

I can’t really give more specific advice; I’d need (a) a lot more details about the data sources, and (b) to know where to send the invoice. :wink:

3

You CAN use a cursor, but cursor use is discouraged in SQL.

If you could post your table structure, I/we could take a stab at writing a query, or providing pseudocode that you can use as a design.

It does sound that using joins and aggregates might be helpful here.

4

Would you post the structure of all the tables that are used, especially Bus?

5

You almost definitely do not want to use a cursor. As others have said, depending upon the specifics of “do stuff” there is probably a way to use aggregates and joins to produce the functionality that you require. This would allow the database’s optimizer to partition the work in such a way as to do it faster.

6

If this is a one-time thing or something that runs infrequently, a cursor is okay. I’m a DBA and I use them all the time with no noticeable drag on the system.

7

Do you need the column defs or just the (equivalent of an) ER diagram?

8

Is all of this information in the BUS table or is it querying other tables as well? Because in most databases, this would be several tables.

Probably a diagram would be enough to get the general idea, as long as it shows all of the tables being queried and their relationships.

9

I’ve prepared a diagram but I’m awaiting a thumbs-up from my client, who may have some confidentiality issues with it.

(Also, it may be irrelevant, insofar as we may have found the speed bottleneck and eliminated it)