SELECT Statements

[stOrM!]

Hello,
I wondering about the following which maybe someone can explain:

when running a query against a mysql 5 table which contains 16.000 recordsets the very first time (Select * from Tablename) then I got the resultset in 0.568s

not bad thought but now the funny part, if I run that query again it took between 15 - 22 seconds for exactly the same query so I wonder how this can happen or in other words how can I get rid of loosing speed?

btw. this happend always to me, when I run a few querys one afther another, the first time they are really fast while at the second time they are sloooooow...

kindest regards
s!

[mdaems]

Did you retrieve all records from the first query resultset? (= did you effectively scroll to the end of the recordset or did a recordcount)

0.5 seconds is very quick to retrieve all 16000 records, so maybe the first time you fetch only first XX records while second time all records are fetched.
To prove this : display recordcount after opening the query this forces fetching all rows.

Mark

[stOrM!]

Did you retrieve all records from the first query resultset? (= did you effectively scroll to the end of the recordset or did a recordcount)

0.5 seconds is very quick to retrieve all 16000 records, so maybe the first time you fetch only first XX records while second time all records are fetched.
To prove this : display recordcount after opening the query this forces fetching all rows.

Mark

Yep! Thats what makes me so wonder...
I retrieved all records both times and scrolled to the end...
To be exactly recordcount is 16.422 always which is true, same is display'd in MySQLQueryBrowser

kindest regards
s!

[mdaems]

Same delays in MysqlQueryBrowser as well?

Tried HeidiSQL? They use zeoslib components, so they should have the same issue if it's a zeoslib bound problem.

It's not a problem during close of the first dataset?
You could try this by doing a runtime create and destroy of the query (and eventually connection) components for every query try. Then you can time from create until destroy. Does that still give difference in execution time between 2 queries?

Did you try to use mysql-proxy to check how long it takes to serve the requests to the server (you can use one of the lua scripts included in the proxy release)

Mark

[stOrM!]

Same delays in MysqlQueryBrowser as well?

Tried HeidiSQL? They use zeoslib components, so they should have the same issue if it's a zeoslib bound problem.

It's not a problem during close of the first dataset?
You could try this by doing a runtime create and destroy of the query (and eventually connection) components for every query try. Then you can time from create until destroy. Does that still give difference in execution time between 2 queries?

Did you try to use mysql-proxy to check how long it takes to serve the requests to the server (you can use one of the lua scripts included in the proxy release)

Mark

MysqlQueryBrowser = no delays.
HeidiSQL = I've downloaded it but haven't test it yet, guess tomorrow I've more Info about heidsql for you...

Nope never got any problems while closing the first query.
BTW. I do create your components dynamically.

Well I've no idea about sql proxy (never used this before, I'm not an expert :-(, I should tell you that the delay happend on the machine where the sql server is running on, so I don't talk about remote connections, but those have a delay too if that would be the next question as the same not on running the query the first time but on the second!

kindest regards and thank you very much for your patience!

[mdaems]

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

[stOrM!]

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

[mdaems]

1) Threading: advanced topic for me... I'm not a real application developer (don't ask what I am ) and I certainly never wrote Delphi applications using threads. Usually the queries are short enough to wait for.

2) I think this are 2 queries... You should use a ZSqlProcessor to process that. Or parse them yourself and feed them 1 by 1 to a TZQuery.
You could also try to add CLIENT_MULTI_STATEMENTS=1 to the TZConnection.Properties property. But if it works, it's a mysql specific solution, however.

[stOrM!]

1) Threading: advanced topic for me... I'm not a real application developer (don't ask what I am ) and I certainly never wrote Delphi applications using threads. Usually the queries are short enough to wait for.

2) I think this are 2 queries... You should use a ZSqlProcessor to process that. Or parse them yourself and feed them 1 by 1 to a TZQuery.
You could also try to add CLIENT_MULTI_STATEMENTS=1 to the TZConnection.Properties property. But if it works, it's a mysql specific solution, however.

1) Ok I'll start a new Thread in the forum here.

2) Oh never have used SQLProcessor before so a new learning course for me hopefully I'll find some resources about it here.

btw. what are you ?

kindest regards
marc