Subject Re: [firebird-support] Re: Composite vs single column keys

--- On Thu, 26/8/10, Steve Boyd <sboydlns@...> wrote:
From: Steve Boyd <sboydlns@...>
Subject: Re: [firebird-support] Re: Composite vs single column keys
Date: Thursday, 26 August, 2010, 22:44
>> Maybe the right solution is to use an ISAM system with a more modern
>> name ... SleepyCat for example. The suggestions about LIMIT/FIRST
>> may lead to other unpleasantness in Firebird because it has to
>> retrieve and sort the whole record set to find the first 100 unless
>> there's a single index with the same characteristics as the order
>> by clause.
>> Basically, if you're convinced that the only solution is a hammer -
>> as your programmers/bosses seem to believe - you'll be happier if
>> you make all problems be nails.
>Trouble is we need relational for other reasons. Not the least of which
>is customer perception. We also have to be able to support Firebird and
>SQL Server (yuck) from the same code base. There is going to be some
>serious jiggery pokery going on (hidden from the Cobol programmers) to
>make that happen.
>So to stretch your analogy to the breaking point, we need screws but the
>only tool the programmers want to use is a hammer. I guess that's what
>keeps me employed.

A bit late, but it should be possible to compromise. You need the new data stream to resemble the old, otherwise you basically have to start again from scratch with the whole application.

I believe that you can get the data ordered the way you want/need (or at least the way it is now) by adding an ascending compound index on CO, ACCT, etc. and then setting the start point 10, 1000, etc.

If this is likely to return an enormous dataset, you can break it into smaller packets by reading 100 or so records at a time. This would require an additional loop in the application to load the next set until done, but this is relatively simple (not much work).

Whether the data is returned from a random SQL query or a stored procedure is irrelevant; it just needs parameters.

I too have been a cobol programmer in a previous life, so it is possible to move on. Are they using