I use a TAdvGridExcelIO together with TDBAdvGrid (PageMode=True), exporting a small amount of rows with 4 columns is fast (seconds). But when I want to export a large amount of data (ie. 4000 rows/4 columns) then it takes forever (many minutes) and sometimes it just freezes. I have tried several combinations of what to export but that doesn't help much. Which limits are there for this component and how does TMS test its capacities ? What can I do to speed up the export process ?
Does it make a difference when you set PageMode=false?
With Pagemode=False exporting 4122 rows takes 24 seconds. Pagemode=True with 4122 rows takes 6 minutes and 20 seconds. I have a table with 8 million records which can be selected by the user to display in the grid. That means I have to use PageMode=True. It's a NexusDB table with the server on the same PC and connected via NamedPipe.
I couldn't find the maximum capacity of TDBAdvGrid and TAdvGridExcelIO but I can imagine that 8 million is to much. However Excel since v2007 can hold just over 1 million rows.
So again the question: How did TMS test the speed and capacity ?
TAdvGridExcelIO generates .XLS files and the limitation for the .XLS file format is 65535 lines. This is the limit inherent to the Microsoft .XLS file format. If you need more rows than 65535, you'll need to export to .XLSX format and you can do this with TMS Grid Filters:http://www.tmssoftware.com/site/advgridfilters.asp (which is a bridge between TMS Grids & TMS Flexcel.)
With respect to speed, the speed is clearly affected by looping through each row in your dataset (PageMode = true) versus a one time load operation (PageMode = false), i.e. it is more affected by dataset speed than speed of the grid. We have not tested with Nexus DB but we have tested with other databases and have not seen such slow behavior.
What kind databases did you test ? How many records and fields ? Memory-based databases ?
The ADO export sample only show a test with 112 records, yes then it's fast because memory-based and almost no data. I did some profiling (AQTime) and there are some TMS functions which eats the most time because they are called more then 100.000 times for a 707 row, 4 columns table. Most time consuming is TOLE2File.WriteRawMem, called 11171 times.
Which routine "reads" the actual datafield from the table ?
GridFilters is only available when you have both ADVGrid and TFlexCell......
I have retested this here with an ADO dataset, 10000 records, 15 fields
PageMode = false : +/- 2.5sec
PageMode = true : +/- 9sec
Hi,
I am wondering: Which version of Delphi are you using? WriteRawMem should be fast as fast can be, it is the method that actually writes the bytes for the xls file into the stream - and being called 11171 times, when you are writing 700x 4 = 2100 cells isn't much at all. If you look at the code, you'll see that WriteRawMem just calls:
FStream.WriteBuffer(Buffer, Count);
Which in turn saves the file to disk. We can't get any faster than that to write bytes to disk.
So either the profiler is getting confused and the time is spend elsewhere, or there is a problem in WriteRawMem. And this is why I ask the version of Delphi you are using.
There was a change in XE3 which made TStream.WriteBuffer much slower;
And maybe this is why you are seeing this. IF you are in XE3, there is also this:
If this is the case, you might want to patch the VCL to make WriteBuffer fast again.
Another thing to try if the problem is really in writebuffer could be an antivirus which is constantly "examining" the bytes as they are written, if you have it turn it off and retry.
A last thing: While I originally thought that you might just be writing too much data and this why the slowness (after all, as I explained before filling a dataset with the database, then filling up a grid, thern reading the grid and exporting it to Excel will always be slower than just reading the values form the db and writing them in Excel), I think that maybe there can be something else.
So I'd like you if possible to install the FlexCel 6 trial and the filters, and report if you see the same times, or if it is faster. FlexCel 6 workarounds the WriteBuffer bug I mentioned here, so if that is the problem, it should be much faster. If it is as slow, then the problem is somewhere else
Regards,
Adrian.
Delphi XE, Win7 Pro. I wiil try Flexcel6
Did some test runs with Flexcel and Gridfilters, makes no difference.....
Yep, I expected it wouldn't make it when you said it was XE, the problem with Stream is in XE3.
Well, if that is the case, I am not really sure on why you might be seeing a bottleneck in WriteRawMem, other than a very slow disk or some antivirus. I think probably it is just AQTime reporting the wrong method.
But to be 100% sure, please try the following:
1)Create a console application.
2)Paste this code:
program Project28;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils, Windows,
VCL.FlexCel.Core,
FlexCel.XlsAdapter;
var
xls: TXlsFile;
row: integer;
col: integer;
Freq, StartCount, StopCount: Int64;
begin
QueryPerformanceFrequency(Freq);
QueryPerformanceCounter(StartCount);
xls := TXlsFile.Create(1, true);
try
for row := 1 to 60000 do
begin
for col := 1 to 75 do
if (col mod 5 = 0) then xls.SetCellValue(row, col, 'string ' + intToStr(row + col))
else xls.SetCellValue(row, col, row + col);
end;
xls.Save('r:\test.xls');
finally
xls.Free;
end;
QueryPerformanceCounter(StopCount);
WriteLn(FloatToStr((StopCount - StartCount) / Freq));
ReadLn;
end.
3)Run without debugging (shift ctrl F9)
I get 6.3 seconds, to write 60,000 rows and 75 columns, which is almost at the row limit in xls (65,535 rows). To be able to save more rows, you would have to save as xlsx.
If you are seeing a similar number, then the bottleneck isn't in WriteRawMem at all. If you are seeing much slower times, then I believe there might be something in your disk.
And finally, to test the speed of dataretrieval from the dataset, you can try:
program Project28;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils, Windows, DB, DBClient,
VCL.FlexCel.Core,
FlexCel.XlsAdapter;
procedure DumpDataSet(const ds: TDataSet);
var
xls: TXlsFile;
Row, Col: integer;
Fmt: TFlxFormat;
DateXF, DateTimeXF: integer;
begin
xls := TXlsFile.Create(1, true);
try
Fmt := xls.GetDefaultFormat;
Fmt.Format := 'dd/mm/yyyy hh:mm';
DateTimeXF := xls.AddFormat(Fmt);
Fmt := xls.GetDefaultFormat;
Fmt.Format := 'dd/mm/yyyy';
DateXF := xls.AddFormat(Fmt);
ds.First;
row := 1;
while not ds.Eof do
begin
for col := 1 to ds.FieldCount do
begin
case ds.Fields[col - 1].DataType of
TFieldType.ftDateTime:
begin
xls.SetCellValue(Row, Col, ds.Fields[col - 1].AsDateTime, DateTimeXF);
end;
TFieldType.ftDate:
begin
xls.SetCellValue(Row, Col, ds.Fields[col - 1].AsDateTime, DateXF);
end;
else
begin
xls.SetCellValue(Row, Col, ds.Fields[col - 1].Value);
end;
end;
end;
ds.Next;
Inc(row);
end;
xls.Save('r:\test.xls');
finally
xls.Free;
end;
end;
var
row: integer;
col: integer;
Freq, StartCount, StopCount: Int64;
Ds: TClientDataSet;
begin
ds := TClientDataSet.Create(nil);
try
for col := 0 to 74 do
begin
if (col mod 5 = 0) then ds.FieldDefs.Add('field' + IntToStr(col), TFieldType.ftString, 100)
else ds.FieldDefs.Add('field' + IntToStr(col), TFieldType.ftDateTime);
end;
ds.CreateDataSet;
for row := 1 to 60000 do
begin
ds.Insert;
for col := 0 to ds.FieldCount - 1 do
if (col mod 5 = 0) then ds.Fields[col].AsString := 'string ' + intToStr(row + col)
else ds.Fields[col].AsDateTime := now;
end;
QueryPerformanceFrequency(Freq);
QueryPerformanceCounter(StartCount);
DumpDataSet(ds);
QueryPerformanceCounter(StopCount);
WriteLn(FloatToStr((StopCount - StartCount) / Freq));
finally
ds.Free;
end;
ReadLn;
end.
I get 8.6 secs here. (note that we are only counting the time to generate the file, filling the dataset ca take longer). If you're getting similar numbers then the problem isn't in the exporting part.
The last thing to try would be to pass your real dataset to the method DumpDataset above, and look at the numbers. If it is slower, then ther's a bottleneck in the db code
I found the bottleneck after testing over and over again. The "ExportNotification" from DBAdvGrid is the problem. I have DataSetType=dtSequenced and DataSetTypeAuto=True, the last one decided that TnxTable is of type dtNonSequenced and therefore ExportNotification does for every record a "First' commando and then "MoveBy" the required records. That slows everything down !
After trying several combinations of settings, I discovered that in TnxTable.Options the dsoRecNoSupport must be True to speed things up.
Now I can export >4000 records in 29 sec. which was more then 6 minutes in the old situation.
That's better, maybe with some extra tweaking of the export software I can gain more speed.
Did some optimizing and tweaking in unit tmsAdvGridExcel and now I reduced the amount of time for exporting 4122 records from 29 sec. downto 8 sec. Now I am happy.....