BorlandTalk.com

Dod Crane · Guest

Hi ,
I'm a long time Delphi user(6-7 yrs). We've built applications in Delphi
1 to btrieve data base and to Dbase with Delphi 5...both environments run
pretty fast using Ttables. I am now converting a Delphi 1/btrieve
application to Delphi 7 with MS SQL using TADODataset, and it is running
very, very slow.
As a first test of the new environment I converted a report which reads
approx. 32,000 tax collection header records and then assembles a
master/details relationship from 2 files (each about 150,000 records ) to
determine payment status (need approx. 2-5 records from each of these two
detail files to assemble a payment status) of the individual tax item.
The Delphi 1/btrieve application runs this report in about 10 minutes;
the Delphi 7/MS SQL application takes 3-4 hrs. I did the Delphi
7/TADODataset implementation by loading the three main tables (a tax header
file and 2 details files containing payment status) as separate record sets
at the start of the application and using TADODataset.Locate to locate the
applicable detail records. Previously I tried loading only the tax header
file record set upon application open, and issuing SQL statements ( ie
DetailTable.Close, set DetailTable.CommandText, then DetailTable.Open) to
get applicable record sets for each of the two details files and this was
very slow too.
I am trying to preserve at least portions of my old application in
TTable form to avoid a complete rewrite of the application, which is very
large and complex. I understand I will have to make changes where I used to
do setranges and at least some filters but I was hoping to use TADODataset
with the same table names as my old Delphi 1 tables and keep
....FieldByName('fieldname')....
code unchanged where possible.
As my Provider when I set up my TADOConnection option I am using ->
'Microsoft OLE DB Provider for SQL Server'....should I be using some
other driver which would speed things up?
Any help would be appreiated

Kevin Frevert · Guest

"Dod Crane" <dodc (AT) sca-corp (DOT) com> wrote

Tony J Hopkinson · Guest

On Mon, 15 Dec 2003 14:27:26 -0500, "Dod Crane" <dodc (AT) sca-corp (DOT) com>
wrote:

David Smith · Guest

Kevin Frevert wrote:

Kevin Frevert · Guest

"David Smith" <david (AT) nowhere (DOT) com> wrote

Lluis Olle · Guest

Hi

I highly suggest you to give a try using Kim Madsen kbmMemTable Free
component, for Batch processing complex SQL querys, splittng those in
several
easy to follow and logical steps, and using "old fashioned" isam access to
process your task.

Basically, with this "in memory Table" component, you can do a series of
".LoadFromDataset" from TADODataset(s) into KbmMemtable(s), define apropiate
indexes into those memory tables, and play using efficient indexed
accesing, ranges, ... and so on, what is your old Btrieve application is
doing just now with great success. 150.000 records is not too much ... I
usually work with more
than that in my everyday batch jobs.

Use Server FastForwardOnly cursors in the TADODatasets, because you only
want those for feeding the in memory tables ... Shoot and forget !

I'm absolutely confident that you can reduce the processing time, not from
the "hours" is taking now relying purely on SQL, but to less that a half it
uses with Btrieve (and
that's my experience), as Btrieve is not specially efficient.

Regards

Arthur Hoornweg · Guest

Sounds like a pretty standard JOIN problem. Why not
let SQL server establish the master/detail relationship
instead of doing it like this?

Dod Crane wrote: