When i run adoquery my application freezes

help me please
I have a simple request (select * from ..), but when I put active adoquery the application freezes until it finishes. It takes about 50 seconds, is there a possibility to cancel the execution of the request
thank you
lounnaci ahmedAsked:
Who is Participating?
 
Geert GruwezConnect With a Mentor Oracle dbaCommented:
You're not writing code in threads. So your app does only 1 thing at a time.
If you really want to work with threads, making your app responsive will running background jobs you'll have start learning about threads

imho, you're not ready to go down that path yet
but anyway, you need to start somewhere ...
http://edn.embarcadero.com/article/22411
http://delphi.about.com/od/kbthread/
http://www.delphibasics.co.uk/RTL.asp?Name=BeginThread
http://www.thedelphigeek.com/2012/02/blaise-pascal-magazine-rerun-5-four.html

i wrote a few articles on threads in delphi.
here is a link to timing a query in threads: https://www.experts-exchange.com/articles/10043/Timing-queries-with-threads.html
the main points of attention are to lock shared resources and to make distinction between background code and vcl front end code
0
 
BigRatCommented:
Two possibilities - run the query in a separate thread (Delphi thread programming http://edn.embarcadero.com/article/22411) or run thew query asynchronously (https://msdn.microsoft.com/en-us/library/ms681014(v=vs.85).aspx)
0
 
Geert GruwezOracle dbaCommented:
There is lots more possibilities

just some:
Tune the query, that's making it faster by adding indexes, hints, writing it differently
Put the database on better hardware, get a better network, get faster pc's
Use a different database
Change the database options: switch on in-memory module, if it has that option.

Ado is a slow component
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
lounnaci ahmedAuthor Commented:
need for example
0
 
Geert GruwezOracle dbaCommented:
what does your query look like ?
0
 
lounnaci ahmedAuthor Commented:
thank you,
I have a dbf file of a size > than 1GB and My query is very simple (select * from file), no condition and I put ExecuteOptions from my adoquery on eoAsyncFetch, eoAsyncFetchNonBlocking CursorLocation on clUseClient, But when I put my adoquery.open my application freezes. And I can not press any keys on the form.
This situation ends until the end of the execution of my request
need help please  thanks
0
 
BigRatCommented:
If you have a dbf file of over 1 gigabyte why on earth are you selecting all columns and all rows at once and using cursor client? You cannot display them all on the screen, so what are you going to do with them? (Provided of course you *could* select all those rows and columns)
0
 
Pierre CorneliusCommented:
The important one is eoAsyncFetchNonBlocking in ExecuteOptions. I think also cursorLocation has to be clUseClient.

Herewith an example (I just created some test data), you just need to change the ADOConnection connectionString and DefaultDatabase to your own:

Pascal code
unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Data.Win.ADODB, Data.DB,
  Vcl.Grids, Vcl.DBGrids, Vcl.ComCtrls;

type
  TForm1 = class(TForm)
    btnOpen: TButton;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    Memo1: TMemo;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    ProgressBar1: TProgressBar;
    StaticText1: TStaticText;
    procedure btnOpenClick(Sender: TObject);
    procedure ADOQuery1FetchProgress(DataSet: TCustomADODataSet; Progress,
      MaxProgress: Integer; var EventStatus: TEventStatus);
  private
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.ADOQuery1FetchProgress(DataSet: TCustomADODataSet; Progress,
  MaxProgress: Integer; var EventStatus: TEventStatus);
begin
  ProgressBar1.Position:= Progress;
  ProgressBar1.Max:= MaxProgress;
  StaticText1.Caption:= Format('Fetched: %d of %d', [Progress, MaxProgress]);
end;

procedure TForm1.btnOpenClick(Sender: TObject);
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Text:= Memo1.Text;
  ADOQuery1.Open;
end;

end.

Open in new window


Delphi Form
object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 379
  ClientWidth = 554
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  DesignSize = (
    554
    379)
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 8
    Top = 8
    Width = 19
    Height = 13
    Caption = 'SQL'
  end
  object btnOpen: TButton
    Left = 8
    Top = 145
    Width = 75
    Height = 25
    Caption = 'Open query'
    TabOrder = 0
    OnClick = btnOpenClick
  end
  object Memo1: TMemo
    Left = 8
    Top = 32
    Width = 529
    Height = 81
    Lines.Strings = (
      'select *'
      'from TestA a'
      'full join TestB b on a.valInt = b.valInt')
    TabOrder = 1
  end
  object DBGrid1: TDBGrid
    Left = 8
    Top = 176
    Width = 529
    Height = 194
    Anchors = [akLeft, akTop, akRight, akBottom]
    DataSource = DataSource1
    TabOrder = 2
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object ProgressBar1: TProgressBar
    Left = 89
    Top = 145
    Width = 448
    Height = 25
    Anchors = [akLeft, akTop, akRight]
    TabOrder = 3
  end
  object StaticText1: TStaticText
    Left = 89
    Top = 122
    Width = 448
    Height = 17
    Alignment = taCenter
    Anchors = [akLeft, akTop, akRight]
    AutoSize = False
    Caption = 'Progress...'
    TabOrder = 4
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString = 
      'Update to your connection string...'
    DefaultDatabase = 'TestingDB'
    IsolationLevel = ilReadCommitted
    LoginPrompt = False
    Mode = cmRead
    Provider = 'SQLNCLI10.1'
    Left = 384
    Top = 88
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    ExecuteOptions = [eoAsyncFetchNonBlocking]
    OnFetchProgress = ADOQuery1FetchProgress
    Parameters = <>
    SQL.Strings = (
      'select *'
      'from TestA a'
      'full join TestB b on a.valInt = b.valInt')
    Left = 392
    Top = 144
  end
  object DataSource1: TDataSource
    DataSet = ADOQuery1
    Left = 488
    Top = 104
  end
end

Open in new window

0
 
Geert GruwezOracle dbaCommented:
A dbf file size does not indicate anything.
I create dbf files in oracle with initial size 1gb.

It's the size of the data.
 
Is this your first encounter with delphi ?
0
 
senadCommented:
when you do a 'select' query you usually just open it (ADOQuery1.Open). You don't use ADOQuery1.ExexSQL.
0
 
Pierre CorneliusCommented:
@senad

I did not say anything about ExecSQL. Open also uses the ExecuteOptions. I tested my example and it showed progress whilst loading yet I was able to still do other things during this time. It was not blocking further execution.
0
 
Geert GruwezOracle dbaCommented:
lounnaci ahmed
what you have programmed is behaving exactly as it should

when someone asks you to go to a store and bring back 20 ton of sausages, it takes time to load the sausages and return them to you
you only get from a store what you can eat, not what you can afford
even billionaires don't get 20 tons of sausages for a weekend party
they only order what is needed

same goes with databases, only load what is needed or what you can see
0
 
lounnaci ahmedAuthor Commented:
unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Data.FMTBcd, Data.DB, Vcl.Grids,
  Vcl.DBGrids, Data.SqlExpr, Data.DBXDb2, Vcl.StdCtrls, Data.Win.ADODB,
  Vcl.ComCtrls, Vcl.DBCtrls, Vcl.WinXCtrls;
type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    ADOConnection1: TADOConnection;
    Button1: TButton;
    ADOQuery1: TADOQuery;
    Label1: TLabel;
    ProgressBar1: TProgressBar;
    Label2: TLabel;
    DBLookupComboBox1: TDBLookupComboBox;
    ADOTable1: TADOTable;
    DataSource2: TDataSource;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    DBLookupComboBox2: TDBLookupComboBox;
    Label3: TLabel;
    ADOTable2: TADOTable;
    DataSource3: TDataSource;
    Button2: TButton;
    Edit1: TEdit;
    Label4: TLabel;
    Edit2: TEdit;
    Label5: TLabel;
    Label6: TLabel;
    Edit3: TEdit;
    procedure Button1Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure ADOQuery1AfterOpen(DataSet: TDataSet);
    procedure ADOQuery1FetchProgress(DataSet: TCustomADODataSet; Progress,
    MaxProgress: Integer; var EventStatus: TEventStatus);
    procedure Button2Click(Sender: TObject);
    procedure ADOQuery1FetchComplete(DataSet: TCustomADODataSet;
      const Error: Error; var EventStatus: TEventStatus);
  private
    { Déclarations privées }
  public
    { Déclarations publiques }
  end;
var
  Form1: TForm1;

implementation

{$R *.dfm}
procedure TForm1.ADOQuery1FetchProgress(DataSet: TCustomADODataSet; Progress,
  MaxProgress: Integer; var EventStatus: TEventStatus);
begin
ProgressBar1.Max:=MaxProgress;
ProgressBar1.Position:=Progress;
Application.ProcessMessages;
end;

procedure TForm1.Button1Click(Sender: TObject);
var i,j:Real;
begin
ProgressBar1.Position:=0;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select ABONNE.NUMAB,RAISOC,NOUVNOM,BLOC,NDOM,SUM(MONTTC) as Montant,Count(MONTTC) as Nbr');
ADOQuery1.SQL.Add('from ABONNE,RUE,ABONMENT,factures');
ADOQuery1.SQL.Add('WHERE ABONNE.CODCAIS='+QuotedStr(DBLookupComboBox1.Text));
ADOQuery1.SQL.Add('and ABONNE.CODRUE=RUE.CODRUE');
ADOQuery1.SQL.Add('and ABONNE.NUMAB=ABONMENT.NUMAB');
ADOQuery1.SQL.Add('and ABONNE.NUMAB=factures.NUMAB');
ADOQuery1.SQL.Add('and ABONNE.NUMAB LIKE '+QuotedStr(DBLookupComboBox2.Text+'%'));
ADOQuery1.SQL.Add('and factures.DATREG IS NULL');
ADOQuery1.SQL.Add('group by ABONNE.NUMAB,RAISOC,NOUVNOM,BLOC,NDOM');
ADOQuery1.SQL.Add('Having Sum(MONTTC) >=:mt and Count(MONTTC)>=:nb');
ADOQuery1.SQL.Add('Order by ABONNE.NUMAB');
ADOQuery1.Parameters.ParamByName('mt').Value:=i;
ADOQuery1.Parameters.ParamByName('nb').Value:=j;
ADOQuery1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
Application.Terminate;
end;

procedure TForm1.FormShow(Sender: TObject);
var dbf_folder : string;
begin
Label1.Caption:='';
Edit1.Clear;
Edit2.Clear;
Edit3.Clear;
ADOConnection1.Connected:=False;
dbf_folder:='d:\retract';
ADOConnection1.LoginPrompt:=false;
ADOConnection1.ConnectionString:=Format('Provider=Microsoft.JET.OLEDB.4.0;Data Source=%s;Extended Properties=dBase IV;',[dbf_folder]);
ADOConnection1.Connected:=True;
ADOTable1.Connection:=ADOConnection1;
ADOTable1.TableName:='CAISSE';
ADOTable1.Active:=True;
DBLookupComboBox1.ListSource:=DataSource2;
DBLookupComboBox1.ListField:='CODCAIS;LIBCAIS';
DBLookupComboBox1.KeyField:='CODCAIS';
ADOTable2.Connection:=ADOConnection1;
ADOTable2.TableName:='QUARTIER';
ADOTable2.MasterSource:=DataSource2;
ADOTable2.MasterFields:='CODCAIS';
ADOTable2.Active:=True;
DBLookupComboBox2.ListSource:=DataSource3;
DBLookupComboBox2.ListField:='QUART;LIBQUART';
DBLookupComboBox2.KeyField:='QUART';
end;

end.

Open in new window

0
 
lounnaci ahmedAuthor Commented:
Here is the code of my program in above
0
 
Geert GruwezOracle dbaCommented:
indent your code with 2 or 3 spaces, makes it more readable !

why only use 1/2 the parameters for your variables ?
every query you give to the database is new and has to be parsed, check for errors, calculated an execution etc ...
ADOQuery1.SQL.Add('WHERE ABONNE.CODCAIS=:CODCAIS');
ADOQuery1.SQL.Add('and ABONNE.NUMAB LIKE :NUMAB);

ADOQuery1.Parameters.ParamByName('CODCAIS').Value := DBLookupComboBox1.Text;
// Are you sure you don't want a % in front of the text too ?
ADOQuery1.Parameters.ParamByName('NUMAB').Value:= DBLookupComboBox2.Text+'%';

Open in new window


i, j: real ????
count returns whole numbers, there is no fraction !
so j should be integer
real is an old identifier, double is the current type to use
and why do you put the having clause in your where ?
you didn't initialise the variables i and j
does the query ever return a result ?

besides that, there is no point in putting that clause in as you don't initialise the variables with any form entries
the only sense would be to set both to zero, but you can just as well delete these 3 lines, makes no difference, except in performance
ADOQuery1.SQL.Add('Having Sum(MONTTC) >=:mt and Count(MONTTC)>=:nb');
ADOQuery1.Parameters.ParamByName('mt').Value:=i;
ADOQuery1.Parameters.ParamByName('nb').Value:=j;

Open in new window


i assume this TForm1 is the main form ?
just close it ... way easier

procedure TForm1.Button2Click(Sender: TObject);
begin
  Close;
end;

Open in new window


you only need the progressbar to be redrawn ... application.processmessages can have odd side effects
begin
ProgressBar1.Max:=MaxProgress;
ProgressBar1.Position:=Progress;
// Application.ProcessMessages;
ProgressBar1.Update;
end;

Open in new window


you're using access for a database ?
that's probably not the fastest database
0
 
BigRatCommented:
The question which naturally arises is whether there are keys on the following fields ABONNE.CODCAIS, then the joins ABONNE.CODRUE, RUE.CODRUE, ABONNE.NUMAB, ABONMENT.NUMAB and factures.NUMAB. If not there is going to be a lot of table searching.
0
 
lounnaci ahmedAuthor Commented:
Thank you Geert Gruwez, I initialized the variables i and j and I declare j in integer, but the problem still persists.
When I execute my request the application freezes
0
 
lounnaci ahmedAuthor Commented:
Can I cancel my request when it is in execution
0
 
senadCommented:
Are you selecting values from just one table ? Also , are you sure your lookup combos are displaying the values before running query ?
0
 
lounnaci ahmedAuthor Commented:
yes senad my combobox posters values before query execution
0
 
Geert GruwezOracle dbaCommented:
have you tried using another tool to do the same as you do ?
when you open a query in a query editor tool, how long does it take to return *all* the rows from that same database ?

if it takes 2 minutes in the other tool, it will take 2 minutes in Delphi

some people like databases to be consistent, no matter the tool ...
1
 
Geert GruwezOracle dbaCommented:
>> When I execute my request the application freezes

This is normal

What are you actually trying to solve ?
0
 
lounnaci ahmedAuthor Commented:
thank's Geert Gruwez I put a button on the form to cancel the request.
But when I execute the request the form freezes and I can not press the button cancel
0
 
lounnaci ahmedAuthor Commented:
Thanks Geert Gruwez I finally understood the threads and their roles in the analysis of processes
0
 
lounnaci ahmedAuthor Commented:
I have another question how to make nested queries in Adoquery
For example select field1, field2, ... form table1, table2, ...
Where field1 = field2 and field1 not in (select field1, date_begin from table1 where date_begin> '11 / 10/2016 ')
Order by field1
0
All Courses

From novice to tech pro — start learning today.