Posted by --CELKO-- on 10/30/06 16:17

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Next, you have described a sequential magentic file system and an
RDBMS. Rows are nothing like records. We do not use loops in SQL; it
is declarative language, not a procedural one.

>> One of the batch tasks that I have to perform firstly builds a list of all keys for records [sic] from each of the three tables that I need to process (along with a flag [sic] to tell me which table the key is from). <<

Batch processing! We do not use flags in SQL. Flags are part of
process control, not data. We might have types. codes or other kinds
of attributes that are used declaratively. Next, a key is the key of
one and only one table; it can be referenced in several places. This
sounds like attribute splitting -- another characteristic of sequential
magentic file systems.

>> This list is populated into a table variable. <<

Using proprietary features to mimic a scratch tape?

>> I then loop through the table variable to process all the records[sic]with the particular key value. <<

Loops in a declarative language?

>> The updates are run in order of the tables .... Table A first, B next and finally C.<<

I wish you had given the tables better names. If the tables have no
relationship among themselves, then you would simply use 3 UPDATE
statements. If the tables have no relationship among themselves,
either DRI actions or triggers would propagate related changes among
the tables. That is how a relational solution works.

>> My problem is this ...... the processing of the key records [sic] from Table A runs well - it takes around 40 minutes which is acceptable for the level of processing being carried out. Though when I start processing the transactions for Table B the first couple of statements execute successfully though then the subsequent statements take a long time (in some cases hours) to complete. <<

Your looping has completely subverted any set-oriented processing which
SQL is designed to do.

>> The format of the statements for all tables is virtually the same .. <<

This sounds like you have split a single entity across multiple tables.
Tables need to be very different becasue they each model a lpgically
different set of entities. For example, would you split Personnel into
a table of short people and a table of tall people? Of course not!
You would have a height attribute (column) in a single Personnel table.


>> Can anyone suggest what might be the issue here? <<

Bad DDL, and non-relational programming.

>> Should I break up my processing so that it processes each table individually ? <<

Ask yourself, if this is one LOGICAL operation. The quick test is can
it be given a name in the form <verb><object> -- like
"UpdateInventory" or "InsertNewEmployee". The same rules you learned
about coupling and cohesion in your Software Engineering courses still
apply in SQL.

Please do not say that you cannot rewrite this stuff. Your DB will
only get bigger

I recently did a consulting job at a software company where they had
been trying to merge legs of a journey into longer trip -- say, you
have a journey from A to B, then from B to C, you want to show it as a
journey from A to C if certain temporal (consecutive legs of the trip
are no more than n days apart) and other constraints are met (the cargo
is of the same type for the same shipper, etc.). They were using
cursors and procedural code.

Thinking in sets, they reduced the existing procedure that was creating
a table to a single VIEW. The code was 90% smaller ~10 times faster
and the VIEW is *always* correct while the created table had to be
updated constantly. Years ago, I got a 2300% improvement and removed
~300 lines of code in an update procedure at an educational publisher
in California (I am good, but this was really because the original code
was that bad).

I know from a few decades of experience that it is worth it.

[Back to original message]