mdaems wrote:Only hope left for me are memory issues. Like memory not being freed at first query destroy. Then later queries might need new memory again maybe on places that are slower to read. (eg. Swap space) Recent versions of zeoslib shouldn't have this kind of memory leaks anymore, however.
If I had the data, I could give it a try using the debugger myself, but I think it's unpractical to arrange. (eventually use a private message if you want to do that)
Mark
No need to worry Mark, zeoslib is just great!
I got it, with your help and patience with me and for sure for the great tips you've pointed me, maybe you could help me in another way please read below :-)
Query result, using HeidiSQL
---------------------------------------------------------
Query1 SELECT * FROM Tbl1 t1; (This is a VIEW with union Select to join 3 Tables, creates 1 resultset for 3 Tables)
Query Execution Time: 22s
Recordcount : 22.944 record(s)
Fields : 6
Query2 SELECT * FROM TBL2 t2;
Query Execution Time: 0,328s
Recordcount: 16.164 record(s)
Fields: 17
Query3 SELECT * FROM TBL4 t3;
Query Execution Time: 0,078s
Recordcount: 2.399 record(s)
Fields: 15
Query4 SELECT * FROM TBL4 t4;
Query Execution Time: 0,378s
Recordcount: 4.568 record(s)
Fields: 15
None of the querys I've executed had a delay, doesn't matter how often I execute the querys...
Ok I guess, there must be a problem in my Application then?
Maybe it might help if I tell something about the Components I used and how I executing the querys?
1. TSyncEdit (used for SQL commands)
2. TSyncEdit (used for ZSQLMonitor, OnLogTrace Event)
3. Datamodule with ZConnection (Makes the Connection to the SQL Server Version 5) + ZReadOnlyQuery + TDatasource
4. ZReadOnlyQuery (Runs in a seperate Thread, used for SQL Commands which are typed in TSyncEdit1)
5. TDBAdvGrid from TMS (Used to displays the query resultset if there is any)
Query Thread declaration:
type
TQThread = class(TThread)
private
FQuery: TzReadOnlyQuery;
Window : HWND;
protected
procedure Execute; override;
public
ID : Cardinal;
constructor Create(w : HWND; Query: TzReadOnlyQuery);
end;
var
QRunThread : TQThread;
constructor TQThread.Create(w : HWND; Query: TzReadOnlyQuery);
begin
inherited Create(True);
FQuery := Query;
FreeOnTerminate := True;
Window := w;
ID := ThreadID;
end;
procedure TQThread.Execute;
begin
SendMessage(window, WM_START_Query, 0, 0);
try
FQuery.Open;
finally
SendMessage(window, WM_End_Query,0,0);
end;
end;
procedure TMain.StartQuery(var msg: TMessage);
begin
sqlStart := GetTickCount;
DM.zCon.SQLHourGlass := True;
end;
procedure TMain.EndQuery(var msg : TMessage);
begin
srecordcount := DM.DataSource1.DataSet.RecordCount;
SQLend := GetTickCount();
SQLTime := (SQLend - SQLstart) / 1000;
QTime := DM.FormatNumber( SQLTime, 3) +'s';
Statusbar1.Panel[0].Text := Format('%d rows fetched in %s', [srecordcount, QTime]);
DM.zCon.SQLHourGlass := false;
end;
I'm not sure what causes the delay, I'm thinking about the TDBAdvGrid.
So I investigate on it and yep bang! You here right and me too :-)
The Problem was at first my Thread for executing the query's (I'm still working on it cauze I can't kill a running query thread yet don't know way) and the DBAdvGrid was also a bit wrong see the changes I made below:
the thread:
type
TQThread = class(TThread)
private
Window : HWND;
protected
procedure Execute; override;
public
ID : Cardinal;
FDataSource : TDataSource;
FQuery: TZQuery;
FConnection : TZConnection;
constructor Create(w : HWND);
procedure FQueryBeforeOpen(DataSet: TDataSet);
procedure FQueryBeforeScroll(DataSet: TDataSet);
end;
constructor TQThread.Create(w : HWND);
begin
inherited Create(True);
// maybe not threadsafe but working will change it later thought...
FConnection := TZConnection.Create(Main);
FConnection.Database := DM.zCon.Database;
FConnection.User := DM.zCon.User;
FConnection.Password := DM.zCon.Password;
FConnection.Protocol := DM.zCon.Protocol;
FConnection.Port := DM.zCon.Port;
FConnection.Connect;
FQuery := TZQuery.Create(Main);
FQuery.SQL.Clear;
FQuery.SQL.Text := Main.SQLEditor.Text;
FQuery.Connection := FConnection;
FQuery.BeforeOpen := FQueryBeforeOpen;
FQuery.BeforeScroll := FQueryBeforeScroll;
FDataSource := TDataSource.Create(Main);
FDataSource.DataSet := FQuery;
Main.DBAdvGrid1.DataSource := FDataSource;
FreeOnTerminate := True;
Window := w;
ID := ThreadID;
end;
procedure TQThread.FQueryBeforeOpen(DataSet: TDataSet);
begin
Main.DBAdvGrid1.BeginUpdate; // this 3 lines where really importent now I retrieve 16.000 rows in 0.4s everytime!! :-))
Main.DBAdvGrid1.RemoveAllFields;
Main.DBAdvGrid1.AddAllFields;
end;
procedure TQThread.FQueryBeforeScroll(DataSet: TDataSet);
begin
Main.DBAdvGrid1.EndUpdate;
end;
procedure TQThread.Execute;
begin
SendMessage(window, WM_START_SCANNER, 0, 0);
try
FQuery.Open;
finally
SendMessage(window, WM_STOP_SCANNER,0,0);
end;
end;
procedure TMain.StartScanner(var msg: TMessage);
begin
sqlStart := GetTickCount;
Animate1.Active := true;
QRunThread.FConnection.SQLHourGlass := True;
end;
procedure TMain.ScannerStop(var msg : TMessage);
begin
Animate1.Active := false;
srecordcount := QRunThread.FDataSource.DataSet.RecordCount;
SQLend := GetTickCount();
SQLTime := (SQLend - SQLstart) / 1000;
QTime := DM.FormatNumber( SQLTime, 3) +'s';
dxRibbonStatusbar1.Panels[0].Text := Format('%d rows fetched in %s', [srecordcount, QTime]);
QRunThread.FConnection.SQLHourGlass := false;
PostThreadMessage(QRunThread.ID, wm_Quit, 0,0 );
end;
Maybe you can give me an Idea about 2 Problems which are still left.
I'm not sure whats wrong in my Thread declaration, but I need to find a safe way of killing the Thread e.g. when you run a query which never stops I need to find a way to kill that thread and come back to my application but how, everything I've try'd never stopped the running query yet?
E.G.
OnButtonClick I used:
PostThreadMessage(QRunThread.ID, wm_Quit, 0,0 );
But it doesn't stop the running Query how can I do that?
How could I identify a query which doesn't have a resultset?
The Problem here is the following, normal SELECT Querys I could run with FQuery.Open....
But when I type the following into SyncEdit and execute my QueryThread:
Create Table
DROP VIEW IF EXISTS `fullview`;
CREATE TABLE `fullview` (
`Haendlername` varchar(255),
`Hersteller` varchar(255),
`Produktpreis` varchar(255),
`Kategorie` varchar(255),
`Lagerbestand` varchar(255),
`Artikelbeschreibung` varchar(255)
);
It throws an execption Query doesn't have a resultset...
When I changed my Thread to FQuery.ExecSQL instead of using FQuery.open; another exeception is shown because of a closed dataset how to handle that too?
kindest regards
Marc
) and I certainly never wrote Delphi applications using threads. Usually the queries are short enough to wait for.