Jump to content


Photo

Suggestion to improve speed


  • Please log in to reply
19 replies to this topic

#1 dee

dee
  • Members
  • 13 posts

Posted 03 December 2007 - 09:29 AM

Hi all, I'm looking for suggestions to improve the speed for populating the NextGrid with data from a dataset. For this post, I've written a simple program that displays the result of a given query.

CODE

procedure TMainForm.btnExecuteClick(Sender: TObject);
var
TempStart, TempEnd: TTime;
TempSQL: string;
TempDataSet: TDataSet;
i, j: Word;
begin
Grid.BeginUpdate;
Grid.ClearRows;
Grid.Columns.Clear;
try
TempSQL := memoSQLCommand.Text;
TempDataSet := ExecuteQuery(TempSQL, False);

if TempDataSet.FieldCount > 0 then
begin
for i := 0 to TempDataSet.FieldCount - 1 do
AddGridCol(Grid, False, True , Grid.Columns.Count, TNxTextColumn, TempDataSet.Fields[i].FieldName, 80, True, taLeftJustify);
end;

TempStart := Now;
if TempDataSet.RecordCount > 0 then
begin
Grid.AddRow();
Grid.AddRow(TempDataSet.RecordCount - 1);
j := 0;

while not TempDataSet.Eof do
begin
for i := 0 to TempDataSet.FieldCount - 1 do
Grid.Cells[i, j] := TempDataSet.Fields[i].AsString;

TempDataSet.Next;
Inc(j);
end;
end;
TempEnd := Now;
finally
TempDataSet := nil;
FreeAndNil(TempDataSet);
end;
Grid.EndUpdate;
ShowMessage(FormatDateTime('s.zzzz', TempEnd - TempStart));
end;

AddGridCol is a simple procedure to add a column to the NextGrid with some properties at runtime.

CODE

Grid.AddRow();
Grid.AddRow(TempDataSet.RecordCount - 1);

Notice the part above. If I do not do this, there will be an "out of bounds" error.

With this code, I could populate the grid with 29 and 8,000 records at around 4.250 seconds. I think it's a bit slow, any suggestions to improve speed will be much appreciated. Thanx in advance smile.gif

#2 Vu Quang Thang

Vu Quang Thang
  • Members
  • 91 posts

Posted 03 December 2007 - 12:02 PM

Hi,

I think the problem lying on the way it locate the record in the dataset. If you remove every line of code that related to NextGrid, the time spend almost is the same.

BTW: you can make this code
Grid.AddRow();
Grid.AddRow(TempDataSet.RecordCount - 1);

shorter by

Grid.AddRow(TempDataSet.RecordCount);

Kind regards,

#3 dee

dee
  • Members
  • 13 posts

Posted 03 December 2007 - 12:28 PM

QUOTE (Sayxanath @ Dec 3 2007, 12:02 PM) <{POST_SNAPBACK}>
Hi,

I think the problem lying on the way it locate the record in the dataset. If you remove every line of code that related to NextGrid, the time spend almost is the same.

BTW: you can make this code
Grid.AddRow();
Grid.AddRow(TempDataSet.RecordCount - 1);

shorter by

Grid.AddRow(TempDataSet.RecordCount);

Kind regards,


Hi.... first of all, thanx for the response.

I have marked the time ONLY for copying the dataset to the Grid. Below is the procedure with the time markings.

CODE

procedure TMainForm.btnExecuteClick(Sender: TObject);
var
TempStart, TempQueryTime, TempFieldsTime, TempDisplayTime: TTime;
TempSQL: string;
TempDataSet: TDataSet;
i, j: Word;
TempRowsAdded: Longword;
begin
Grid.BeginUpdate;
Grid.ClearRows;
Grid.Columns.Clear;
try
TempSQL := memoSQLCommand.Text;
TempStart := Now;
TempDataSet := ExecuteQuery(TempSQL, False);
TempQueryTime := Now;

if TempDataSet.FieldCount > 0 then
begin
for i := 0 to TempDataSet.FieldCount - 1 do
AddGridCol(Grid, False, True , Grid.Columns.Count, TNxTextColumn, TempDataSet.Fields[i].FieldName, 80, True, taLeftJustify);
end;
TempFieldsTime := Now;

if TempDataSet.RecordCount > 0 then
begin
TempRowsAdded := TempDataSet.RecordCount;
Grid.AddRow();
Grid.AddRow(TempDataSet.RecordCount - 1);
j := 0;

while not TempDataSet.Eof do
begin
for i := 0 to TempDataSet.FieldCount - 1 do
Grid.Cells[i, j] := TempDataSet.Fields[i].AsString;

TempDataSet.Next;
Inc(j);
end;
end;
TempDisplayTime := Now;
finally
TempDataSet := nil;
FreeAndNil(TempDataSet);
end;
Grid.EndUpdate;

lblInfo.Caption := 'Added: ' + FormatFloat('###,##0', TempRowsAdded) + ' rows // ' +
'Query: ' + FormatDateTime('s.zzzz', TempQueryTime - TempStart) + ' sec // ' +
'Columns: ' + FormatDateTime('s.zzzz', TempFieldsTime - TempQueryTime) + ' sec // ' +
'Display: ' + FormatDateTime('s.zzzz', TempDisplayTime - TempFieldsTime) + ' sec // ' +
'Total Time: ' + FormatDateTime('s.zzzz', Now - TempStart) + ' sec';
end;

the lblInfo.Caption will be:
CODE

Added: 8,218 rows // Query: 0.860 sec // Columns: 0.0031 sec // Display: 4.531 sec // Total Time: 5.422 sec

And I can not do this:
CODE

Grid.AddRow(TempDataSet.RecordCount);
Because, it will return list of index out of bounds error. So I have to do this:
CODE

Grid.AddRow();
Grid.AddRow(TempDataSet.RecordCount - 1);
Maybe it's a bug?

#4 Vu Quang Thang

Vu Quang Thang
  • Members
  • 91 posts

Posted 03 December 2007 - 01:56 PM

Hi,

I mean that you just open the dataset, make a loop from the beginning to the end with just one line of code: TempDataSet.Next for 8000 records. You will see it also consume the same amount of time.

For the NextGrid.AddRow method, I don't see that error.

Kind regards,

#5 Boki (Berg)

Boki (Berg)

    Boki (Berg)

  • Forum Admin
  • PipPipPipPipPip
  • 7,911 posts
  • Gender:Male

Posted 03 December 2007 - 05:36 PM

Hello Dee,

If you are using ALL records from DataSet, maybe the best solution will be to use TDataLink and then set BufferRecord to be number of total records. In this case, goin trough records may be very fast.

I recommend checking TNxDataCellSource component from NxThirdpartyRun package. Maybe you can use some ideas from there.

Best regards
boki@bergsoft.net | LinkedIn Profile
--
BergSoft Home Page: www.bergsoft.net
Members Section: bms.bergsoft.net
Articles and Tutorials: dn.bergsoft.net (Developers Network)
--
BergSoft Facebook page
--
Send us applications made with our components and we will submit them on: www.bergsoft.net/apps.htm. Link to this page will be also set on home page too.

#6 dee

dee
  • Members
  • 13 posts

Posted 04 December 2007 - 04:43 AM

QUOTE (Sayxanath @ Dec 3 2007, 01:56 PM) <{POST_SNAPBACK}>
Hi,

I mean that you just open the dataset, make a loop from the beginning to the end with just one line of code: TempDataSet.Next for 8000 records. You will see it also consume the same amount of time.

For the NextGrid.AddRow method, I don't see that error.

Kind regards,


Oh so sorry, I missed your point. Yes, I think you are right. Thanx a lot to point that out smile.gif But I still don't understand why NextGrid.AddRow produces that error when the Grid has zero RowCount. But I will find out more by testing.

#7 dee

dee
  • Members
  • 13 posts

Posted 04 December 2007 - 04:47 AM

QUOTE (Boki (Berg) @ Dec 3 2007, 05:36 PM) <{POST_SNAPBACK}>
Hello Dee,

If you are using ALL records from DataSet, maybe the best solution will be to use TDataLink and then set BufferRecord to be number of total records. In this case, goin trough records may be very fast.

I recommend checking TNxDataCellSource component from NxThirdpartyRun package. Maybe you can use some ideas from there.

Best regards


Hi Boki,

I've been using your component for quite a while now, and it's the best Grid I've ever used so far. I'm not an expert programmer, because basically I'm a network engineer, but I also do A LOT of programming for my company using NextGrid. And I've been very satisfied by your component. Great job!!!

Oh, and I will try checking out TNxDataCellSource component. I've never used that before. Thanx for the recommendation!

#8 dee

dee
  • Members
  • 13 posts

Posted 04 December 2007 - 05:58 AM

QUOTE (Boki (Berg) @ Dec 3 2007, 05:36 PM) <{POST_SNAPBACK}>
Hello Dee,

If you are using ALL records from DataSet, maybe the best solution will be to use TDataLink and then set BufferRecord to be number of total records. In this case, goin trough records may be very fast.

I recommend checking TNxDataCellSource component from NxThirdpartyRun package. Maybe you can use some ideas from there.

Best regards


Hi again Boki,

I've tried TNxDataCellSource and it's much slower than the TempDataSet.Next method.
CODE

NxDataCellSource1.Dataset := TempDataSet;
NxDataCellSource1.Execute;


Btw, I use TempDataSet.Next method A LOT, because most of the time, I need to do something for every queried record which I can not do on the database server. For example, I need to format the date to the client computer's local format. They have their own preferences of showing the dates. Sometimes I need to place some conditions, so I need to scan every record. So I don't think using TDataLink or TNxDataCellSource is a good idea. Does this make any sense?

#9 Boki (Berg)

Boki (Berg)

    Boki (Berg)

  • Forum Admin
  • PipPipPipPipPip
  • 7,911 posts
  • Gender:Male

Posted 04 December 2007 - 08:42 AM

Hello Dee,

Maybe DataSet is a bottle neck sad.gif But, maybe using TDataLink may help.

When you set DataLink.BufferCount to be the same size as RecordCount, all records will be buffered (downloaded) into DataLink. Then, with using DataLink.ActiveRecord you may go trough first to the last record and load them into grid. I think that this should be faster than calling .Next

I only don't know how you are familiar with DataLink component smile.gif

If you are not, maybe you can give few days to send you one small sample.

Best regards
boki@bergsoft.net | LinkedIn Profile
--
BergSoft Home Page: www.bergsoft.net
Members Section: bms.bergsoft.net
Articles and Tutorials: dn.bergsoft.net (Developers Network)
--
BergSoft Facebook page
--
Send us applications made with our components and we will submit them on: www.bergsoft.net/apps.htm. Link to this page will be also set on home page too.

#10 dee

dee
  • Members
  • 13 posts

Posted 04 December 2007 - 09:08 AM

QUOTE (Boki (Berg) @ Dec 4 2007, 08:42 AM) <{POST_SNAPBACK}>
Hello Dee,

Maybe DataSet is a bottle neck sad.gif But, maybe using TDataLink may help.

When you set DataLink.BufferCount to be the same size as RecordCount, all records will be buffered (downloaded) into DataLink. Then, with using DataLink.ActiveRecord you may go trough first to the last record and load them into grid. I think that this should be faster than calling .Next

I only don't know how you are familiar with DataLink component smile.gif

If you are not, maybe you can give few days to send you one small sample.

Best regards


As the matter of fact, I'm not familiar with TDataLink at all.
Your offer is very generous... and I accept that offer smile.gif Thank you in advance. Meanwhile, I wil try to familiarize myself with TDataLink...

#11 Vu Quang Thang

Vu Quang Thang
  • Members
  • 91 posts

Posted 04 December 2007 - 12:08 PM

QUOTE (dee @ Dec 4 2007, 11:58 AM) <{POST_SNAPBACK}>
Btw, I use TempDataSet.Next method A LOT, because most of the time, I need to do something for every queried record which I can not do on the database server. For example, I need to format the date to the client computer's local format. They have their own preferences of showing the dates. Sometimes I need to place some conditions, so I need to scan every record. So I don't think using TDataLink or TNxDataCellSource is a good idea. Does this make any sense?


Hi,

I think if you need to do something for every record, so DataSet.Next method is a must. The standard DBGrid runs very fast. So I assume that DBNextGrid should be alike and it can format your data somehow. Can you give it a try.

Kind regards,

#12 Boki (Berg)

Boki (Berg)

    Boki (Berg)

  • Forum Admin
  • PipPipPipPipPip
  • 7,911 posts
  • Gender:Male

Posted 04 December 2007 - 08:06 PM

Hello Dee,

I will write this sample for you. I will need few days (due to started bugs/features I need to finish) and then I will send it to you.

Best regards
boki@bergsoft.net | LinkedIn Profile
--
BergSoft Home Page: www.bergsoft.net
Members Section: bms.bergsoft.net
Articles and Tutorials: dn.bergsoft.net (Developers Network)
--
BergSoft Facebook page
--
Send us applications made with our components and we will submit them on: www.bergsoft.net/apps.htm. Link to this page will be also set on home page too.

#13 dee

dee
  • Members
  • 13 posts

Posted 05 December 2007 - 06:14 AM

QUOTE (Sayxanath @ Dec 4 2007, 12:08 PM) <{POST_SNAPBACK}>
Hi,

I think if you need to do something for every record, so DataSet.Next method is a must. The standard DBGrid runs very fast. So I assume that DBNextGrid should be alike and it can format your data somehow. Can you give it a try.

Kind regards,


Hi again Sayxanath,

Well, I've tried using DBGrid, but I don't think it suits my need exactly. I need a grid that's NOT DB-aware, and sometimes I need to represent the records in a "creative" non-2-dimensional way, so I think DataSet.Next is the only way I know for now to populate the grid, then manipulate it with loops.

After the manipulation of the grid, then I will decide which rows to update to database. Does this make any sense to not use a DB-aware grid?

Anyways, I really appreciate your inputs. It's invaluable for me to learn from other more experienced programmers...

#14 dee

dee
  • Members
  • 13 posts

Posted 05 December 2007 - 06:15 AM

QUOTE (Boki (Berg) @ Dec 4 2007, 08:06 PM) <{POST_SNAPBACK}>
Hello Dee,

I will write this sample for you. I will need few days (due to started bugs/features I need to finish) and then I will send it to you.

Best regards


Thank you very much, Boki... Please take your time smile.gif I highly appreciate it...

#15 wvd_vegt

wvd_vegt

    Master Member

  • Honorable Members
  • PipPipPipPipPip
  • 710 posts
  • Gender:Male
  • Location:the Netherlands

Posted 07 December 2007 - 01:33 PM

Hi,

QUOTE (dee @ Dec 5 2007, 06:14 AM) <{POST_SNAPBACK}>
Hi again Sayxanath,

Well, I've tried using DBGrid, but I don't think it suits my need exactly. I need a grid that's NOT DB-aware, and sometimes I need to represent the records in a "creative" non-2-dimensional way, so I think DataSet.Next is the only way I know for now to populate the grid, then manipulate it with loops.

After the manipulation of the grid, then I will decide which rows to update to database. Does this make any sense to not use a DB-aware grid?

Anyways, I really appreciate your inputs. It's invaluable for me to learn from other more experienced programmers...


I never can get the db stuff working (and am an experienced programmre) so always use a non dbaware grid. Some months ago I wrote a TCollection that loads a database table into memory using rtti (Runtime Type Information) so the loading routines automatically adjust to the table.

These TCollections are represented in various TNextGrid tables. When I edit someting there I flag the item as dirty and can then save all changed ones at once when destroying the collection for example. The Collections contain routines to create and populate the NextGrid as I like (for example in slide mode) and to do silent lookups in other collections/tables. I even managed to create slides that contains lists of dates (so group records together) that way.

To make sure the records are linked correctly to the table rows i use the GuidColumn to generate a unique id independend of the database auto increment fields. Drawback is that it's only working fine when the database is a single user one. Updates in tables are performed with SQL queries instead of direct table access.
G.W. van der Vegt

#16 dee

dee
  • Members
  • 13 posts

Posted 08 December 2007 - 06:32 AM

QUOTE (wvd_vegt @ Dec 7 2007, 01:33 PM) <{POST_SNAPBACK}>
Hi,
I never can get the db stuff working (and am an experienced programmre) so always use a non dbaware grid. Some months ago I wrote a TCollection that loads a database table into memory using rtti (Runtime Type Information) so the loading routines automatically adjust to the table.

These TCollections are represented in various TNextGrid tables. When I edit someting there I flag the item as dirty and can then save all changed ones at once when destroying the collection for example. The Collections contain routines to create and populate the NextGrid as I like (for example in slide mode) and to do silent lookups in other collections/tables. I even managed to create slides that contains lists of dates (so group records together) that way.

To make sure the records are linked correctly to the table rows i use the GuidColumn to generate a unique id independend of the database auto increment fields. Drawback is that it's only working fine when the database is a single user one. Updates in tables are performed with SQL queries instead of direct table access.


Hi,

I understand what you are saying. And most of the time, our concepts are very similar. But maybe the main difference is I do not load a database table into memory, but instead, I read every record and populate each row of the NextGrid. This can be quite slow, so I need to find a fast way to load my dataset into memory and then populate the rows of NextGrid from memory.

Maybe I do not exactly understand how datasets work, but I use this code A LOT:
CODE

while not DataSet1.Eof do
begin
{ do things here, usually populate the grid }

DataSet1.Next;
end;


And from my new understandings I got from this great forum, the bottleneck is the DataSet1.Next. Can you give me some clues to improve the speed? Thanx in advance smile.gif

#17 didiergm

didiergm
  • Honorable Members
  • 148 posts
  • Gender:Male
  • Location:France

Posted 08 December 2007 - 05:31 PM

Dee,

Maximum speed would be achieved by making sure that
1- all windows refresh operations are blocked dataset->disablecontrols, ... do your work, the re-enablecontrols, same with the grid: begin update/endupdate
2- keeping the data in your dataset to the minimum required (ie do not use a ttable, use a tquery instead with a restrictive select)


If you want to load in memory, have a look at tClientDataset it will load a dataset through a Tdataset provider, so you have

TQuery (of some sort) -> tdatasetprovider ->tDatasource -> tClientDataset

there is a video from the last CodeRage II http://video.codegea...psAndTricks.zip which explains things

hth

Didier

#18 Boki (Berg)

Boki (Berg)

    Boki (Berg)

  • Forum Admin
  • PipPipPipPipPip
  • 7,911 posts
  • Gender:Male

Posted 08 December 2007 - 06:19 PM

Hello Dee,

I have build small example to show you how to fill grid with TDataLink. I am not sure that this is fast enough for you but it worth looking.

Attached File  DataLink.zip   48.06KB   13 downloads

I have use small ADO table in this example, but you may change this very easy.

I hope that this helps smile.gif

Best regards
boki@bergsoft.net | LinkedIn Profile
--
BergSoft Home Page: www.bergsoft.net
Members Section: bms.bergsoft.net
Articles and Tutorials: dn.bergsoft.net (Developers Network)
--
BergSoft Facebook page
--
Send us applications made with our components and we will submit them on: www.bergsoft.net/apps.htm. Link to this page will be also set on home page too.

#19 dee

dee
  • Members
  • 13 posts

Posted 10 December 2007 - 04:30 AM

QUOTE (didiergm @ Dec 8 2007, 05:31 PM) <{POST_SNAPBACK}>
Dee,

Maximum speed would be achieved by making sure that
1- all windows refresh operations are blocked dataset->disablecontrols, ... do your work, the re-enablecontrols, same with the grid: begin update/endupdate
2- keeping the data in your dataset to the minimum required (ie do not use a ttable, use a tquery instead with a restrictive select)
If you want to load in memory, have a look at tClientDataset it will load a dataset through a Tdataset provider, so you have

TQuery (of some sort) -> tdatasetprovider ->tDatasource -> tClientDataset

there is a video from the last CodeRage II http://video.codegea...psAndTricks.zip which explains things

hth

Didier


Hi Didier,

Thank you for the tips. I will remember and apply those to my code. I'm still downloading the video. Will take a look at it. Thanx again, I really appreciate it smile.gif

#20 dee

dee
  • Members
  • 13 posts

Posted 10 December 2007 - 10:24 AM

QUOTE (Boki (Berg) @ Dec 8 2007, 06:19 PM) <{POST_SNAPBACK}>
Hello Dee,

I have build small example to show you how to fill grid with TDataLink. I am not sure that this is fast enough for you but it worth looking.

Attached File  DataLink.zip   48.06KB   13 downloads

I have use small ADO table in this example, but you may change this very easy.

I hope that this helps smile.gif

Best regards


Hi Boki,

First of all, I would like to thank you for all your troubles. You have my gratitude smile.gif

I've tried to apply it on my application, but it didn't improve the speed for my application. I measured the time in 1/1000 of a second. Using DataSet.Next is around 10% faster. And I've tried it many times. Is this a weird case?

Anyways, the dataset I use is always from a query executed at runtime. It's not a table. Does this explain why using DataLink is not any faster?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users