Solved

When i run adoquery my application freezes

Posted on 2016-11-14
26
100 Views
Last Modified: 2016-11-27
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
0
Comment
Question by:lounnaci ahmed
  • 10
  • 8
  • 3
  • +2
26 Comments
 
LVL 27

Expert Comment

by:BigRat
ID: 41886573
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41886839
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
 

Author Comment

by:lounnaci ahmed
ID: 41887905
need for example
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41888613
what does your query look like ?
0
 

Author Comment

by:lounnaci ahmed
ID: 41889670
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
 
LVL 27

Expert Comment

by:BigRat
ID: 41889796
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
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 41890086
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41890274
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
 
LVL 22

Expert Comment

by:senad
ID: 41890678
when you do a 'select' query you usually just open it (ADOQuery1.Open). You don't use ADOQuery1.ExexSQL.
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 41890892
@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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41890980
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
 

Author Comment

by:lounnaci ahmed
ID: 41891270
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:lounnaci ahmed
ID: 41891274
Here is the code of my program in above
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41891373
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
 
LVL 27

Expert Comment

by:BigRat
ID: 41891379
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
 

Author Comment

by:lounnaci ahmed
ID: 41894252
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
 

Author Comment

by:lounnaci ahmed
ID: 41894261
Can I cancel my request when it is in execution
0
 
LVL 22

Expert Comment

by:senad
ID: 41894306
Are you selecting values from just one table ? Also , are you sure your lookup combos are displaying the values before running query ?
0
 

Author Comment

by:lounnaci ahmed
ID: 41894360
yes senad my combobox posters values before query execution
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41894779
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41894780
>> When I execute my request the application freezes

This is normal

What are you actually trying to solve ?
0
 

Author Comment

by:lounnaci ahmed
ID: 41895794
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
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 41896903
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
 

Author Closing Comment

by:lounnaci ahmed
ID: 41903069
Thanks Geert Gruwez I finally understood the threads and their roles in the analysis of processes
0
 

Author Comment

by:lounnaci ahmed
ID: 41903074
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now