lounnaci ahmed
asked on
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
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
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)
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
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
ASKER
need for example
what does your query look like ?
ASKER
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
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
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)
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
Delphi Form
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.
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
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 ?
I create dbf files in oracle with initial size 1gb.
It's the size of the data.
Is this your first encounter with delphi ?
when you do a 'select' query you usually just open it (ADOQuery1.Open). You don't use ADOQuery1.ExexSQL.
@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.
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.
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
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
ASKER
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.
ASKER
Here is the code of my program in above
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 ...
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
i assume this TForm1 is the main form ?
just close it ... way easier
you only need the progressbar to be redrawn ... application.processmessage s can have odd side effects
you're using access for a database ?
that's probably not the fastest database
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+'%';
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;
i assume this TForm1 is the main form ?
just close it ... way easier
procedure TForm1.Button2Click(Sender: TObject);
begin
Close;
end;
you only need the progressbar to be redrawn ... application.processmessage
begin
ProgressBar1.Max:=MaxProgress;
ProgressBar1.Position:=Progress;
// Application.ProcessMessages;
ProgressBar1.Update;
end;
you're using access for a database ?
that's probably not the fastest database
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.
ASKER
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
When I execute my request the application freezes
ASKER
Can I cancel my request when it is in execution
Are you selecting values from just one table ? Also , are you sure your lookup combos are displaying the values before running query ?
ASKER
yes senad my combobox posters values before query execution
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 ...
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 ...
>> When I execute my request the application freezes
This is normal
What are you actually trying to solve ?
This is normal
What are you actually trying to solve ?
ASKER
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
But when I execute the request the form freezes and I can not press the button cancel
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Geert Gruwez I finally understood the threads and their roles in the analysis of processes
ASKER
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
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