Devart Forums

Posted: **Thu 13 Oct 2016 14:36**

I am testing perfomance using UniDAC in 3 database servers that we use: mssql, oracle and postgre

However when I set TUniTable.Unidirectional = false I get a Out of Memory

I have tried using the TUniTable.SpecificOptions.Values['FetchAll'] := 'False'; but made no difference

Using a small table it works. However is only make sense using a big table for this kind of test. The table I am using has a Geometry column. I see memory consumption rising very fast. There is plenty of memory for the test program to run even when the Exception happens.

this is the code (this is a test code, done only for the testing):

Code: Select all

procedure TForm1.Loop(db, server, database, username, password, schema, table: string; uni: boolean);
var
    Con: TUniConnection;
    Tbl: TUniTable;
    Qry: TUniQuery;
    st, ed: TTime;
    row: integer;
begin
  Con:= TUniConnection.Create(nil);
  Con.Server := server;
  Con.Database := database;
  Con.Username:= username;
  Con.password:= password;
  Con.LoginPrompt:= false;

  tbl:= TUniTable.Create(nil);

  if db = 'mssql' then
  begin
    tbl.TableName:= schema+'.'+table;
    Con.ProviderName := 'SQL Server';
  end;
  if db = 'oracle' then
  begin
    tbl.TableName:= table;
    Con.ProviderName := 'Oracle';
    Con.SpecificOptions.Add('Direct=True');
  end;
  if db = 'postgre' then
  begin
    tbl.TableName:= table;
    Con.ProviderName := 'PostgreSQL';
  end;

  Con.Open;

  tbl.Connection := con;
  tbl.SpecificOptions.Values['FetchAll'] := 'False';
  tbl.UniDirectional:= uni;
  tbl.Open;

  if uni then
    row := 1
  else
    row := 4;


  st := Now;
  if db = 'mssql' then
    sg.Cells[1, row] := TimeToStr(st);
  if db = 'oracle' then
    sg.Cells[2, row] := TimeToStr(st);
  if db = 'postgre' then
    sg.Cells[3, row] := TimeToStr(st);

  tbl.First;
  while not tbl.eof do
    tbl.Next;

  ed := now;
  if db = 'mssql' then
    sg.Cells[1, row+1] := TimeToStr(ed);
  if db = 'oracle' then
    sg.Cells[2, row+1] := TimeToStr(ed);
  if db = 'postgre' then
    sg.Cells[3, row+1] := TimeToStr(ed);

  if db = 'mssql' then
    sg.Cells[1, row+2] := IntToStr( SecondsBetween(st, ed));
  if db = 'oracle' then
    sg.Cells[2, row+2] := IntToStr( SecondsBetween(st, ed));
  if db = 'postgre' then
    sg.Cells[3, row+2] := IntToStr(SecondsBetween(st, ed));


  tbl.Close;
  con.Close;

  tbl.Free;
  Con.Free;
  Application.ProcessMessages;
end;

I execute this function using these calls:

loop('mssql', '192.168.0.11\SQLEXPRESS', 'data', 'data', 'psw', 'dbo', 'roads', true);
loop('oracle', '192.168.0.11:1521:xe', 'data', 'system', 'psw', 'dbo', 'roads', true);
loop('postgre', '192.168.0.11', 'data', 'postgres', 'psw', 'dbo', 'roads', true);

loop('mssql', '192.168.0.11\SQLEXPRESS', 'data', 'data', 'psw', 'dbo', 'roads', false);
loop('oracle', '192.168.0.11:1521:xe', 'data', 'system', 'psw', 'dbo', 'roads', false);
loop('postgre', '192.168.0.11', 'data', 'postgres', 'psw', 'dbo', 'roads', false);

the first 3 calls works fine on the table

the table in all 3 sql servers is the same and has 6,326,966 records

this is the DDL used for postgre, I am using the exact same structure for the other 2 sql servers (just the syntax is changed according sql server):

Code: Select all

CREATE TABLE public.roads
(
  ogc_fid integer NOT NULL DEFAULT nextval('roads_ogc_fid_seq'::regclass),
  osm_id character varying(10),
  name character varying(50),
  ref character varying(25),
  type character varying(25),
  attribute numeric(6,0),
  car numeric(1,0),
  maxspeed numeric(6,0),
  duration numeric(6,0),
  wkb_geometry geometry(LineString,4326),
  CONSTRAINT roads_pkey PRIMARY KEY (ogc_fid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.roads
  OWNER TO postgres;

CREATE INDEX roads_wkb_geometry_geom_idx
  ON public.roads
  USING gist
  (wkb_geometry);

What should be done to loop thru the table with unidirectional = false with big tables like this?

Posted: **Thu 13 Oct 2016 18:35**

Hello,

You can use LIMIT clause in the SQL query.
Unidirectional = false means Bidirectional and it takes more memory (see documentation of UniDac)
You have the geometry field which can takes a lot of memory for complicated objects. Some databases may treat this field like memo. If you fill in a table you probably don't view this field. Consider omit this field in the query results.

Regards
Michal

Posted: **Mon 17 Oct 2016 11:56**

UniDAC will be read out all the data completely when opening the dataset, include BLOB (geometry). In the described case, the 'Out of memory' error message may occur on lack of memory in the system for reading out a too large data volume.

To decrease memory consumption in UniDAC, try using SmartFetch Mode. For this, set the TUniTable.SmartFetch.Enabled property to True. See more details about SmartFetch options in our documentation: https://www.devart.com/unidac/docs/?dev ... embers.htm

Devart Forums

Out of Memory when Unidrectional = false

Out of Memory when Unidrectional = false

Re: Out of Memory when Unidrectional = false

Re: Out of Memory when Unidrectional = false