vb.net to delphi

hi epasquier
yes, yes, yes.
yes execute it in dfferent thread

i think this can be done making a function threadlocrec?

threadlocrec.BeginInvoke("select id, name from enroll", Nothing, Nothing)
threadlocrec.BeginInvoke("select id, name from dropouts", Nothing, Nothing)
threadlocrec.BeginInvoke("select id, name from returns", Nothing, Nothing)

because I would like to open them unsynchronously.

the reason Id like to do that because i would like to store the selected id and name to a memory using stringlist, so everytime I call again them again?,  I will just look in the stringlist which is faster than getting from the disk.

actually i have the answer, but I really know where to catch the records and store them in the memory,  so next time I want to use it, I have to look at in the array/stringlist.
http://delphi.about.com/od/kbthread/a/query_threading.htm

you would have to be sure the thread are finished before checking the stringlist, so there is little gain to do those 3 queries in threads.
Beware that it can be slower to execute 3 threads simultaneously instead of one after the other if they are using the same resource. In the case of DB, you are limited by the disk access (which can be considerably slower if simultaneous) if local or by the network.

In the end, by doing that you will not be able to use the features of a TDataSet, and you have no guaranty that you won't have a loss of performance instead of a gain. And that is assuming that you are doing the job correctly with the threads.

>>Beware that it can be slower to execute 3 threads simultaneously instead of one after the other.
huh
why is slower while I am planning to execute on different thread?
of disk access?  What if your hardisk is using raid0, I think its fine.
What do you think?  Im only using it during the first load, after that I have to access only on the memory(list).

if you have a raid0 system, I'm sure the query will be executed fairly shortly even with standard way of using Dataset components in the main thread, and you won't need to move memory around from the dataset to the stringlist. The dataset will hold your data in memory just as well (even better), and subsequent recovery of data will be quicker. You will have benefits of filtering, record cursors, VCL DB aware components etc...

3 threads using heavy disk access will make the drive head go crazy by constantly seeking in different part of the drive, which can have advert effects in the magnitude of hundred times less performance on an average system. I'm not even talking about heavily fragmented drive. NCQ (native command queuing) enabled on modern harddrives with special SATA controler (especially RAID controlers) can reduce that significantly by ordering the access considering the physical location of all the concurrently asked sectors, therefore limiting the drive head movements but NEVER better than 3 files access that are done in sequence (only case where NCQ access on multiple files is better than non-NCQ sequence access : when the files in question are very very very fragmented).

Don't forget that while your threads are running and are not complete, you cannot rely on your main thread accessing in the string lists being filled. So your application will be allowed to work with those only when all 3 threads are terminated. And you will need synchronization systems that will slow further the process.

I bet hard currency that if you spend the next 2 weeks to create a good implementation of such a multi-threaded query loading, you will see that it is actually slower than the standard method of opening your 3 queries in sequence in a datamodule creation, which can be implemented in 10 minutes.

Only benefit with threads IMHO : your main form will appear instantly, and will react to user interaction right from launch. But since nothing interesting should be done while the loading occurs.........

Best solution :
- create the data module
- create the main form
- on MainForm onShow event, send a message to itself that will be queued and trigger a function when all initialization is done
=> the Main window will be drawn, then the function called, in the Application.Run; loop
- that function will create & display a splashscreen in modal style above your application main form
- that splash screen will call the queries sequentially, and before and after each query will update some information on the spash form + Application.ProcessMessages; to make sure it update the painting before being locked in the query processing
- when that is done, the splashscreen close itself, and you can work with your application Main form

==> user friendly, easy to do, and as quick as possible

>>if you have a raid0 system, I'm sure the query will be executed fairly shortly even with standard way of using Dataset components in the main thread,
>>AND you won't need to move memory around from the dataset to the stringlist.
>>The dataset will hold your data in memory just as well (even better), and subsequent recovery of data will be quicker.

epasquier?
Are you SURE,  I don't need moving it to the memory?
They said memory is better than disk access.
Raid0 is still a disk access.!?

of course Raid0 is a disk access !!! Raid is just a mechanism to dispatch logical drive data in multiple physical drives, to (either/or)
- benefit from multiple times the bandwidth (especially in reading access)
- have a better security in case of one hard drive crash

that can be seen as a single drive with better perf, but a hard drive nonetheless, so with strong limitations compared to memory access (or flash drive access in a SSD, which is somewhere between the 2 opposites)

a Dataset object have some caching mechanism that, depending on the DB type and implementation, achieve a balance between those 2 contradictory constraints :
1- memory usage
2- access speed

if the implementation put the accent in access speed to the maximum, EVERY record resides in memory after the initial loading. that is so with the VirtualTable component for example, a pure memory Dataset (but not really a database) that can read/save its data in CSV files when you say so programmatically, or that you can construct entirely programmatically with Append/Post etc...

if the implementation put the accent in memory usage to the maximum, only a few records are cached in memory, (un)loaded as required when you move around in your dataset with First/Next etc.. methods. Usually those implementations at least keep in memory indexes on some key fields to quickly locate records.

Most Dataset components are somewhere in between, so that when you treat sequentially all your dataset the processor can do its job on a "page" of records in memory while at the same time the next page is loaded in memory from the disk (or the network). That way, the memory usage is fairly low even if the dataset has a gigantic number of records, and you don't have to wait for all the dataset to be loaded before you can start working (especially when network is concerned).
If you add the fact that the OS will also cache and preload frequently accessed files fragments, that complex mechanism usually gives the best of both world, and you barely notice the processor is waiting for the dataset to load because he can do other things during disk access (like updating the painting of the currently displayed dataset fragment in a DBGrid for example, or your other calculations)

Some implementations are better than others, of course. BDE & ADO are crappy with big DB because they have so many layers between your app and the DB, ODAC is extremely fast (Oracle Direct Access through TCP/IP)

Ok;
Im Convinced using ODAC, But ODAC is a huge component, I don't think its faster than MySQL
Unless you have proven that.

>>EVERY record resides in memory after the initial loading
yes, BUT during the SQL select statement moves, it gets data from the disk, not on the memory.
yes it resides in the memory after loading it.

if I command; (always use)
TSQLQuery.SQL.Text := 'select * from table1';
TSQLQuery.Open;
It always open from the disk,  record came from the disk directly.
The advantage of raid0 is far better than ordinary disk in either reading or writing.
But issuing that command is really from the disk.


How can it be faster than getting from the memory?
if I have 1million records
then searching each record from the disk is fairly slow,  BUT if it is from the memory? its far better?

eq., memory access
Ii dont have to open the table again because its million records
Openning a million records is good only during db loadtime and save it in the list(memory)

if I will use the SQL select command again, it will be 'select * from millionrecordsTable'
TSQLQuery.Open;  again  and it takes an hour to recover it.

How can I reuse the ( select * ) again from the memory?
Look at this code,  it is always openning the table,  which if I have a million record, its very slow.

// -----------------------------------------------------------------------------------
// Database routines
// -----------------------------------------------------------------------------------

unit uDBClass;

interface

uses
  ADODB, DB, classes, SysUtils;

const
  // the database we'll be connecting to
  DBFile = 'Sample.mdb';
  ConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + DBFile;

type
  // Class TTemplate
  // Define a type to temporary storage of template
  TTemplate = class
    public
      // Template data.
      tpt:        Pchar;
      // Template size
      size:       Integer;
      // Template ID (if retrieved from DB)
      id:         Integer;

      // Allocates space to template
      constructor Create;
      // clean-up
      destructor Destroy; override;
  end;

  TDBClass = class
  private
    // a data set to mantain all templates of database
    dsTemplates: TADODataSet;
    // the connection object
    connection: TADOConnection;
    // Template object used to get a template from database.
    tptBlob: TTemplate;
  public
    function openDB(): boolean;
    procedure closeDB();
    procedure clearDB();
    function addTemplate(template: TTemplate): Integer;
    procedure getTemplates();
    function getTemplate(id: Integer) : TTemplate;
    function getNextTemplate(): TTemplate;

  end;

implementation
uses uMain;


// Default constructor
constructor TTemplate.Create();
begin
  // Allocate memory for template and initialize its size to 0
  tpt := AllocMem(MAX_SIZE_TEMPLATE);
  size := 0;
end;

// Default destructor
destructor TTemplate.Destroy();
begin
  // free resources
  FreeMemory(tpt);
end;

// Open connection
function TDBClass.openDB(): boolean;
begin
  try
        dsTemplates := TADODataSet.Create(nil);
        tptBlob := TTemplate.Create();
        connection := TADOConnection.Create(nil);
        connection.ConnectionString := ConnectionString;
        connection.Open();
        openDB := true;
  except
        openDB := false;
  end;
end;

// Close conection
procedure TDBClass.closeDB();
begin
  dsTemplates.Close();
  dsTemplates.Free();
  tptBlob.Free();
  connection.Close();
  connection.Free();
  connection := nil;
end;

// Clear database
procedure TDBClass.clearDB();
begin
  // run "clear" query
  connection.Execute('DELETE FROM table');
end;

// Add template to database. Returns added template ID.
function TDBClass.addTemplate(template: TTemplate): Integer;
var
  rs: TADODataSet;
  tptStream: TMemoryStream;
  id: Integer;
begin
  // get DB data and append one row
  rs := TADODataSet.Create(nil);
  rs.Connection := connection;
  rs.CursorType := ctStatic;
  rs.LockType := ltOptimistic;


//HERE OPENNING everytime adding records
// -----------------------------------------------------------------------------------
  rs.CommandText := 'SELECT * FROM enroll';
  rs.Open();
-----------------
------------------------------------------------------------------
// 

  rs.Append();
  tptStream := TMemoryStream.Create();
  // write template data to memory stream.
  tptStream.write(template.tpt^, template.size);
  // save template data from memory stream to database.
  (rs.FieldByName('template') as  TBlobField).LoadFromStream(tptStream);
  // update database with added template.
  rs.post();
  // get the ID of enrolled template.
  id := rs.FieldByName('ID').AsInteger;
  // close connection
  tptStream.Free();
  rs.Close();
  rs.Free();
  addTemplate := id;
end;

// Start fetching all enrolled templates from database.
procedure TDBClass.getTemplates();
begin
  dsTemplates.Close();
  dsTemplates.CacheSize := 15000;
  dsTemplates.CursorLocation := clUseClient;
  dsTemplates.CursorType := ctOpenForwardOnly;
  dsTemplates.LockType := ltReadOnly;
  dsTemplates.Connection := connection;


//HERE OPENNING AGAIN
// -----------------------------------------------------------------------------------
  dsTemplates.CommandText := 'SELECT * FROM table';
  dsTemplates.Open();
-----------------
------------------------------------------------------------------
// 

  dsTemplates.BlockReadSize := 15000;
end;


// Returns template with supplied ID.
function TDBClass.getTemplate(id: Integer): TTemplate;
Var
  template: TTemplate;
begin
  dsTemplates.Close();
  dsTemplates.Connection := connection;
  dsTemplates.CursorType := ctDynamic;
  dsTemplates.LockType := ltReadOnly;
  dsTemplates.CommandText := 'SELECT * FROM table WHERE ID = ' + IntToStr(id);
  // Get query response
  dsTemplates.Open();
  // Deserialize template and return it
  template := getNextTemplate();
  dsTemplates.Close();
  getTemplate := template;
end;

// Return next template from dataset
function TDBClass.getNextTemplate(): TTemplate;
Var
  tmp: String;
begin
  // No results?
  if dsTemplates.Eof then
  begin
    tptBlob.size := -1;
    getNextTemplate := tptBlob;
  end else
  begin
    // Get template ID from database
    tptBlob.id := dsTemplates.FieldByName('ID').AsInteger;
    // Get template data from database as string.
    tmp := dsTemplates.FieldByName('template').AsString;
    // Get template size from database.
    tptBlob.size := length(tmp);

    // Move template data from temporary string
    // to template object.
    Move(PChar(tmp)^, tptBlob.tpt^, tptBlob.size);
    // move foward in the list of templates
    dsTemplates.Next();
    getNextTemplate := tptBlob;
  end;
end;

end.

Select allOpen in new window

Please COMPARE THIS CODE which is fast (code1_Identify or code2_Identify)

Do you think using Raid0 is faster than code_2?
There are memory also that are advance during read write.

What do you think?

// FROM DISK...........................................................
function CODE1_Identify(var score: Integer): Integer;
Var
  ret: Integer;
  tptRef: TTemplate;
Begin

  //OPENNING TABLE
  getTemplates();

  tptRef := getNextTemplate();

  // Iterate over all templates in database
  if tptRef.size > 0 then
    repeat

      // COMPARING RECORD
      ret := Identify(tptRef.tpt, score, DEFAULT_CONTEXT);

      if (ret = MATCH) then
      begin
        Identify := tptRef.id;
        exit;
      end
      else if (ret < 0) then
        begin
          Identify := ret;
          exit;
        end;

      tptRef := DB.getNextTemplate();
    until tptRef.size <= 0;

    // end of database, return "no match" code
    Identify := NOT_MATCH;
end;


// FROM MEMORY.................................................

type
taid = array of integer;
tatemplate = array of PChar; 

var
arrayid: taid;
arraytemplate: tatemplate;
recount: integer;


function during_Form_Load
begin
       dsTemplates.SQL.Text := 'select * from enroll';
       dsTemplates.Open;

        recount := dsTemplates.RecordCount;

        //Loads every record in memory
        for i:= 0 to recount-1 do
        begin
        setlength(arrayid, i+1);
        arrayid[i] := dsTemplates.FieldByName('id').AsInteger;
        setlength(arraytemplate, i+1);

        tmp := dsTemplates.FieldByName('template').AsString;
        tptBlob.size := length(tmp);
        Move(PChar(tmp)^, tptBlob.tpt^, tptBlob.size);
        arraytemplate[i] := tptBlob.tpt;                 

end

//IDENTIFY from Memory........................................
function Code2_Identify(var score: Integer): Integer;
Var  i,ret: integer;
Begin

for i:= 1 to recount do
begin
  // COMPARING RECORD
 ret := Identify(arraytemplate[i-1], score, DEFAULT_CONTEXT);

if (ret = MATCH) then
 begin
 Identify := arrayid[i-1];
 exit;
 end
 else if (ret < 0) then
 begin
 Identify := ret;
 exit;
 end;

end;        
end;

Identify := NOT_MATCH;
end;

Select allOpen in new window

> TSQLQuery.SQL.Text := 'select * from table1';
> TSQLQuery.Open;
> It always open from the disk,  record came from the disk directly.
Sure. And where do you think your thread will read the info from ? Disk also. So initial loading of your app will be slow

> How can it be faster than getting from the memory?
> if I have 1million records
> then searching each record from the disk is fairly slow,  BUT if it is from the memory? its far better?
You will always have to read it ONCE from the disk

> Ii dont have to open the table again because its million records
> Openning a million records is good only during db loadtime and save it in the list(memory)
Who says you have to close the dataset ? Leave it open after initial load. It will be the dataset responsibility to decide when to load a page of data, what to keep in memory

> if I will use the SQL select command again, it will be 'select * from millionrecordsTable'
> TSQLQuery.Open;  again  and it takes an hour to recover it.
That is the trick : you never ever Close / Open again your dataset. Open at app initialisation, Close at end (not even useful it will be done automatically if you shutdown the DB session)

> How can I reuse the ( select * ) again from the memory?
Simply by not closing it

> Look at this code,  it is always openning the table,  which if I have a million record, its very slow.
Well of course it's slow. It is so badly designed. Why in the hell are you closing and reopening those DataSet before using them each time ? Open them ONCE only, when you open the TADOConnection

Use a DataModule to put all your DB components and manage them visualy at design time (that will replace your TDBClass). Let Delphi create all these for you, and use the fields objects created automatically instead of calling FieldByName ... Your code size will be divided by 10 (at least what you will need to type), and the speed.... Impossible to say.

Now, having said that, ADO is one of the worst DB component set, and your underlying MS access is definitely one of the worst DB system. I have no idea how well it caches data.
I suspect at least your OS can help, and also that ADO parameter : dsTemplates.CacheSize := 15000;
If you put there a value that is large enough, it could well be that your entire DB will be loaded in memory (except BLOB fields I hope). You'll have to play with this parameter to manually set the value that balance the memory usage and speed to your liking

>>That is the trick
This is what I need to hear from you,  I often wonder why sample programs in the internet always close then open it again, then open, then open.
Before the years I've seen there codes,  I never followed them.

Oh, you mean to say that sample programs in the internet are tricks.
I think not, there ignorant and stupid,  following what they see without understanding it.

One thing that I want to assure you that I was not tricked, even the years passed by,  that code above is not mine,  I did only post it to compare wha'ts fast.

Now my doubt is true, I know its wrong, and actually heard from you.

>>It is so badly designed
Glad to hear it from you,   I though the code was great, yes the code was great, but the database system code is wrong and bad.  Am I correct epasquier?

Ok;
I've read all your comment, glad too.

As I understand your comment about
>>COMPARE THIS CODE which is fast (code1_Identify or code2_Identify)
your answer is code2_identify?  Yes?

But if your the one who made it, you would probably use the dataset again and again not closing it, unless you need too.
(not even useful it will be done automatically if you shutdown the DB session)
I think your not really closing it.


>>MS access is definitely one of the worst DB system
its only my test database
I am using MySQL and I had use Oracle database too.
I'm not sure who's really fast, I think its PostGreSQL ;)

Ok;
I am NOT used to use DB components, drag and drop DB components.   I like more on coding...as you see the codes above.


Epasquier?
Can you change the database code design of unit uDBClass;?

you can delete some code of lines like; (there only use for msaccess)
  dsTemplates.CacheSize := 15000;
  dsTemplates.CursorLocation := clUseClient;
  dsTemplates.CursorType := ctOpenForwardOnly;
  dsTemplates.LockType := ltReadOnly;

Let me see how you handle that unit to speed up the identification function.

function CODE1_Identify(var score: Integer): Integer;
Var
  ret: Integer;
  tptRef: TTemplate;
Begin

  //OPENNING TABLE
  getTemplates();

  tptRef := getNextTemplate();

  // Iterate over all templates in database
  if tptRef.size > 0 then
    repeat

      // COMPARING RECORD
      ret := Identify(tptRef.tpt, score, DEFAULT_CONTEXT);

      if (ret = MATCH) then
      begin
        Identify := tptRef.id;
        exit;
      end
      else if (ret < 0) then
        begin
          Identify := ret;
          exit;
        end;

      tptRef := DB.getNextTemplate();
    until tptRef.size <= 0;

    // end of database, return "no match" code
    Identify := NOT_MATCH;
end;

I know you dont have the complete code, BUT I know you know by side seeing the code alone, you already know the result of the code.  That makes you Veteran.

I want to see your trick if it is speeder than mine.
Please,   I know its a bit time consuming, maybe an efforts for an hour and half for you.  And I have to see the way you code in a database design,  your coding style in a database system.

Im glad to hear the word "trick".
I'll wait for a day to close this, I have to wait for your style.
Please, just change some lines, do not include lines if not neeeded too.
https://www.experts-exchange.com/questions/26557844/vb-net-to-delphi.html#33956489

epasquier your speechless?
Ok, I understand you dont want to share your tricks.
nevermind

anyway I reposition dsTemplates to public and use it in the identify function  while not eof  ;)

the problem is,  I have to PChar each fieldvalue everytime an identification is process.

do you have any idea how to open a dataset from blobfield to PChar?

I have this one; during initialization;
dsTemplates.SQL.Text := 'select id, template from table';
dsTemplates.Open;

But template is a TBlobField saved,  
HOW can I automate during openning to change it to PChar ?

I cant do this;
dsTemplates.SQL.Text := 'select id, PCHAR(template) from table';
dsTemplates.Open;


this time I'm using your tricks, use dataset instead of memoryset. ;)


Thanks for the great trick

function TDBClass.addTemplate(template: TTemplate): Integer;
var
  tptStream: TMemoryStream;
  id: Integer;
begin
 dsTemplates.Append();
 tptStream := TMemoryStream.Create();
 tptStream.write(template.tpt^, template.size);

 // HOW ABOUT saving it TO a PCHAR instead of TBlobField, so during OPEN time theres no problem comparing it.
  (rs.FieldByName('template') as  TBlobField).LoadFromStream(tptStream);

  (rs.FieldByName('template') as  PCHAR????).LoadFromStream(tptStream);
 // CAN you correct me with this line, please.


  dsTemplates.post();

  id := dsTemplates.FieldByName('ID').AsInteger;

  tptStream.Free();

  addTemplate := id;
end;

Actually it works like this;
'select id, CAST(template as PCHAR) as template from table';

BUT identify function first parameter is a PCHAR,
so I should always use
identify(PCHAR(xxxxxx), score, constants);

ret := Identify( PCHAR (db.dsTemplates.Fields.Fields[1].AsString), score, DEFAULT_CONTEXT);
with using PChar always? it decreases identification process.


What dataset property I will use to set it directly to a pchar value?

What can be the most speedy solution for this kind of problem?


But the most effecient way to speedup this database system is to save it on a Pchar value
then
open it with that pchar value already
then
identify it without specifying a pchar, because its a pchar already.

I hope you get what I am saying with my 3 successive post ;)


Please help me with this.

sorry, lot of works lately. I'll see what you posted and try to help you next week

Ok;

>>But the most effecient way to speedup this database system is to save it on a Pchar value
(rs.FieldByName('template') as  TBlobField).LoadFromStream(tptStream); FIX

'select id, CAST(template as PCHAR) as template from table'; NO ERRORS
openning...


BUT identify function first parameter is a PCHAR,
so I should always use
identify(PCHAR(xxxxxx), score, constants);

ret := Identify( PCHAR (db.dsTemplates.Fields.Fields[1].AsString), score, DEFAULT_CONTEXT);
with using PChar always? it decreases identification process.

Wow thats a lot of thread. Of course I like long threads; Seems like your fishing to increase you application performance. Using a test database (Access) while intending to use a real SQL database for you final is a BIG MISTAKE!
@Systan,
I see you ask and answer a LOT of questions and
 I've answered several of your questions lately;
so I would like to give you some good advice.

Choose a database and become an Expert at THAT database (100 hours) before writing an application;
What I mean by an expert is; Every serious SQL database comes with tools that you can design and optimize;
lets take Microsoft SQL server 2005, they give you;
SQL Visual Studio 2005 with this GUI interface you can;

1. Create tables;
       Create Primary indexes
       create secondary indexes
       Relationship links
       Data constraints
       Triggers on Insert Delete and Update

2. Create Views
        Query Designer
        Multi-table joins
        Query Optimizer (performance) VERY IMPORTANT

3.  Stored Procedures  - The most under-utilized and most important features
     Used to do complicated processing on Server Side
      Can return Datasets as well as Updates
In fact our corporation does NOT allow direct SQL Table Queries from the client;
They must be either a stored procedure or a view.!

You need to understand that the is a LOT to know and learn on the SQL Server side before making improvments on the client code.

You can choose to not listen to what I'm trying to give you;
but it will continue to cause you great problems.
Anybody with years of database development would tell you the same thing.
 

briangochnauer?
Nice comments, thank you.

epasquier
I understand the time that you have.
I'm satisfied with your answers, regarding to my problem?  actually is not a problem,  its a choice.  
Its Ok now, I've managed to know your tricks. ;)  But I did not apply it, I used the array of pchar, which is faster than dataset field properties.(as I test, its 25% faster), that's my trick also. :)

Thank you very much