Memory issues

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Memory issues

Post by radub » Wed 28 Jul 2010 10:27

I am intending to replace BDE in a D6 environment in a production application; for the time being at client site there is SDAC trial version in real time test, but I find many errors raised related to memory, errors which didn't appear under BDE (Out of memory, Not enogh storage to complete the operation etc).

Is there any particular customization of SDAC which I missed?
These errors appear randomly, sometimes I can reproduce, sometime I can't.

Thank you

Radu B.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 28 Jul 2010 10:58

This is strange to hear and we did not encounter this with other users. Many users use SDAC components and it provides stable connection with SQL Server.
Try to use the SQL Native Client provider. For this set the TMSConnection.Options.Provider property to the prNativeClient value.
Also try to use SDACDemo. Does it generate errors?

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Post by radub » Wed 28 Jul 2010 12:09

For the "Out of memory" I find what was happened:
from the client side, a TClientDataset was opened with PacketRecords=0 only for the metadata; now, the table has over 1 million records and the query the TMSTable is doing (the dataset to which is connected the TClientdataset through a TDatasetProvider) is simply "select * from table", and from here the memory error; I was expecting to put a where clause (something like "where 0=1") in the query not to get all the records.
I solve this by DataRequest method where I put the where clause.

To conclude, with PacketRecords=0, SDAC has 2 issues:
- missing the where clause
- on a table with many records generates the error

Thank you,

Radu B.

Petya
Posts: 21
Joined: Wed 28 Jul 2010 11:24

Almost same here

Post by Petya » Wed 28 Jul 2010 12:31

Hello,

I got almost the same issue.
Delphi 5, SDAC 4.80 trial, new project, some basic controls to open .sdf file, one MSCompactConnection, one MSTable.
I set MSCompactConnection1.Datbase, call Connect, set MSTable1.TableName, set MSTable1.Active:=true;.
At this point, when I set a table that has below 3000 records, my App's mem usage goes to someway 500MB. In the database, I have tables with 30000 and 500000 records, I didn't risk a BSOD to click that. This is when I set FetchAll to true. When it's false, I see no mem increase, even with the half-million records table.
The whole database file (.sdf) size is 18 MB.

What's wrong ?

Thanks, Peter

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 28 Jul 2010 15:42

radub wrote:To conclude, with PacketRecords=0, SDAC has 2 issues:
- missing the where clause
- on a table with many records generates the error
1. To solve the problem use the TMSTable.FilterSQL property, that changes the WHERE clause of SELECT statement. Syntax is the same as the WHERE clause syntax.
2. Set the TMSTable.FetchAll property to False or TMSTable.UniDirectional property to True. You can find out more detailed information about it in the "Increasing performance" topic of SDAC help.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Re: Almost same here

Post by Dimon » Wed 28 Jul 2010 15:43

Petya wrote:I got almost the same issue.
Delphi 5, SDAC 4.80 trial, new project, some basic controls to open .sdf file, one MSCompactConnection, one MSTable.
I set MSCompactConnection1.Datbase, call Connect, set MSTable1.TableName, set MSTable1.Active:=true;.
At this point, when I set a table that has below 3000 records, my App's mem usage goes to someway 500MB. In the database, I have tables with 30000 and 500000 records, I didn't risk a BSOD to click that. This is when I set FetchAll to true. When it's false, I see no mem increase, even with the half-million records table.
The whole database file (.sdf) size is 18 MB.
What's wrong ?
Are you sure that you set only FetchAll to False, and do not set UniDirectional to True?

Petya
Posts: 21
Joined: Wed 28 Jul 2010 11:24

Post by Petya » Wed 28 Jul 2010 17:09

Dimon wrote: 2. Set the TMSTable.FetchAll property to False or TMSTable.UniDirectional property to True. You can find out more detailed information about it in the "Increasing performance" topic of SDAC help.
I have set FetchRows down to 1 which produced the smallest memory footprint that goes from 16MB to 53MB (+37MB) when opening the 500K records table, which is ~15MB on disk. This is acceptable. But if I set it back to the defaullt of 25, memsize goes to ~600MB. This is normal ???

The FetchRows help says :
The number of rows that will be transferred across the network at the same time. This property can have a great impact on performance. So it is preferable to choose the optimal value of the FetchRows property for each SQL statement and software/hardware configuration experimentally.
Out of the description it's not clear for me, what exactly FetchRows sets, and what does network traffic has to do with memory consumption.
It seems that MSTable creates some structures or objects for each record as many times as FetchRows is set to.
Well, I found a recourse for the problem myself, but I'm still confused about things and that's not good for anyone.

Cheers,

Peter

Petya
Posts: 21
Joined: Wed 28 Jul 2010 11:24

Re: Almost same here

Post by Petya » Thu 29 Jul 2010 07:07

Dimon wrote: Are you sure that you set only FetchAll to False, and do not set UniDirectional to True?
It's impossible to set both to true.
I set FetchAll to true in design time => UniDirectional is false.
I do not set them in runtime neither.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 29 Jul 2010 14:02

FetchRows defines the number of rows for which one memory block will be assigned and that will be transferred across the network at the same time. It means, that SDAC allocs memory bolcks size of which is equal to RecordSize * FetchRows.

I can not reproduce the problem with growing memory. Please send me a complete small sample to dmitryg*devart*com to demonstrate it, including a script to create and fill table.

Petya
Posts: 21
Joined: Wed 28 Jul 2010 11:24

Post by Petya » Fri 30 Jul 2010 17:12

Problem solved, topic closable.
Thanks.

Post Reply