BorlandTalk.com

G. Bradley MacDonald · Guest

Hello

I am writing a data conversion program - where I read data from one DB -
and change it - and then write it out to a table in another Database.
The problem I am running into is that my SQL Server (Running on the same
box) is chewing up memory at a rate of about 600KB a second or so. Even
on my 2GB laptop - that is going to use up all available memory long
before I go through the 150,000+ records I need to convert.

The conversion process is fairly simple - and my program slowly climbs
in memory (1KB every minute or so...) - so - it is not a problem.

The records are being inserted - and are reasonably large - having
multiple Blob fields in each insert (containing Text - not images).

I have GetMetaData turned off, I am closing and opening the CDS I am
using to insert the records (and I see a drop in memory usage in my app
- but not the SQL Server) - and I have even tried using a SQL
Transaction and a commit statement - hoping that would free up memory on
the MS SQL Server - but it doesn't.

I am using D7 - and dbExpress. I have tried both the updated Borland
dbexpress driver and the CoreLabs drivers - so I am thinking it is
something I am doing
I have SQL Server 2000 with SP3.

Any thoughts

--

G. Bradley MacDonald
bradley_AT_telus_DOT_net

---------------------------------------------
Sample Code
---------------------------------------------
sds_ClinicNotes.First;
While NOT sds_ClinicNotes.EOF Do
Begin

TD.TransactionID := 1;
TD.IsolationLevel := xilREADCOMMITTED;
sct_NMS.StartTransaction(TD);
// If the datasets have been closed - reopen them (due to
hitting commit limits)
If NOT cds_NMS_EMR_ENTRY.Active Then cds_NMS_EMR_ENTRY.Open;

cds_NMS_EMR_ENTRY.Insert;

// Save the Current Doc - so we skip it at the bottom
CurrentDoc := sds_clinicNotes.FieldByName
('NOTE_ID').AsInteger;
// It is in this routine the record is advanced. Multiple
// Records per note!!
CurrentNote := BuildNotefromSections(CurrentDoc);
// Now Get the Document record for this note!
If cds_Document.Active Then
cds_Document.Close;
sds_Document.SQLConnection := sct_FromDB;
sds_Document.commandText := BaseDocSelect + IntToStr
(CurrentDoc);
cds_Document.Open;
cds_Document.First;

// Assign all columns that are known
cds_NMS_EMR_ENTRY.FieldByName('EMR_ENTRIES_ID').AsInteger
:= CurrentDoc;
// Rest of the Fields havebeen removed for this sample
cds_NMS_EMR_ENTRY.Post;

NoOfLines := NoOfLines + 1;
edt_NoOfProcessedStatements.Text := IntToStr(NoOfLines);
edt_NoOfErrors.Text := IntToStr(NoOfErrors);

// Now Apply the Inserted Records
try
Errors := cds_NMS_EMR_ENTRY.ApplyUpdates(0);
if Errors > 0 then
begin
ShowMessage('Error when inserting EMR_Entry records.');
NoOfErrors := NoOfErrors + 1;
end;

// If it is greater than 1000 records - then close the
Datasets we are inserting into
// If the datasets have been closed - reopen them (due to
hitting commit limits)
If (NoOfLines Mod 1000) = 0 Then
Begin
If cds_NMS_EMR_ENTRY.Active Then cds_NMS_EMR_ENTRY.Close;
End; // If every thousand Records

except
on E: Exception do
begin
ShowMessage(E.Message);
NoOfErrors := NoOfErrors + 1;
end;
end; // Try..Except

// Now commit the transaction - hopefully freeing up the memory
sct_NMS.Commit(TD);

Application.ProcessMessages;
Sleep(25);

End; // While Not EOF

Euan Garden · Guest

Almost certainly by design;

http://support.microsoft.com/default.aspx?scid=kb;en-us;321363

Also discussed here several times.

-Euan
"G. Bradley MacDonald" <bradley (AT) _AT_telus (DOT) _DOT_net> wrote

Alain Quesnel · Guest

Have you tried a DTS package on MSSQL instead of going through a Delphi
client app? It might be faster and achieve the same results. Not to mention
making your memory issue moot.

--

Alain Quesnel
[email]alainsansspam (AT) logiquel (DOT) com[/email]

www.logiquel.com

"G. Bradley MacDonald" <bradley (AT) _AT_telus (DOT) _DOT_net> wrote