Load dataset / query faster

Question

I have an application when at the click of a button all records from a database table get loaded into a cxGrid (via tadquery / datasource). The number of records will increase as users insert them.

I was just wondering if there is anything I need to do or should do to make sure that it doesn't take too long to load all records.

This is what I'm doing when button is clicked:

with query1 do
begin
  Close;
  SQL.Clear;
  SQL.Text := 'SELECT * FROM DBA.Table1;
  Open;
end;

Suggestions would be appreciated.

cxGrids are optimized for speed, so maybe suggestions would be premature. Have you tested your app with a few k records in Table1? — MartynA, Nov 13 '15 at 12:01
There's about 3000 records at the moment and roughly takes about 4-5 seconds to load — 6String_Coder, Nov 13 '15 at 12:57
Is that with a local Sql Server or a nework one? My test app here loads and displays 7k records with 22 columns in 900ms without doing anything special to improve performance. — MartynA, Nov 13 '15 at 13:16
Have you read the DevExpress documentation on properties like ServerMode/GridMode etc, and searched [the DevExpress support](https://www.devexpress.com/Support/Center/) questions for keywords like *slow*, *speed*, *large*? Make sure you pre-select the [VCL][ExpressQuantumGrid suite] filter on the right. — Jan Doggen, Nov 13 '15 at 14:02
First recommendation in cases like these is always: do not retrieve the entire table if you can avoid it. Instead of TAdoQuery have you looked at dataset components that allow you to retrieve the data in batches 'as required'? — Jan Doggen, Nov 13 '15 at 14:06
Have you investigated your TAdoQuery parameters? The first one that comes to mind to look at is the CursorType. IIRC setting that to forwardonly could speed things up. — Jan Doggen, Nov 13 '15 at 14:07
I am no longer selecting all (*), so just the required fields on the grid and this has helped a lot and also changing the cursor type to forward only helps as well. I will test a few more things out and see how I get on. Thank you to all who have commented — 6String_Coder, Nov 13 '15 at 14:57
I would try setting the grid visible property to false while the data is being loaded. Also, list the fields being selected instead of using Select *. — C Harmon, Nov 13 '15 at 14:59

MartynA · Accepted Answer · 2015-11-13T14:17:12.553

1

It's not a particularly rigorous test, obviously, but with my test app below, setting GridMode to True results in the dataset being opened and the grid populated in about 125ms (for 7k 22-column rows) compared to 900ms with it set to False.

procedure TForm1.Button1Click(Sender: TObject);
var
  T1 : Integer;
begin
  if qNames.Active then
    qNames.Close;
  T1 := GetTickCount;
  cxGrid1DBTableView1.DataController.DataModeController.GridMode := CheckBox1.Checked;
  qNames.Open;
  Caption := IntToStr(qNames.RecordCount) + ':' + IntToStr(GetTickCount - T1) + ':' + IntToStr(qNames.FieldCount);
end;

This is with everything in the cxGrid set to their defaults except that I've set the KeyFieldNames of the DataController to the dataset's PK.

edited Nov 13 '15 at 14:17

answered Nov 13 '15 at 13:37

MartynA

30,454
4
32
73

I also found a great performance increase over 12k records on Gridmode true vs false. The issue however is also then you have to implement sorting and the grid filters yourselve, because this is something I need to be active all the time. It's better however to have the great performance and then should the user want to use a filter THEN you determine the value list, instead of assuming he'll always use it and then you waste that performance gain. – Frank Pedro Jan 29 '21 at 09:48

Load dataset / query faster

1 Answers1