windows 7 - SQLHourGlass - SLOW

[vajtsz]

Hi

I Have got a Delphi 7 + Zeos 6.6.5 project. (with DevExpress Quantum grid)

On some machine with windows 7 the the program slow down extremely. The symptoms are :
- I click on a menupoint, the program show the sqlhourglass, then wait wait wait....
- if I move the mouse cursor to the program icon on the windows7 tray, then the window immediately appear.
- some machine not show this error

I test a lot of windows7 visualization option, but none of them help.

I compile the code with Delphi XE2+ZEOS 7 - Results are the same...

Then I set the SQLHourGlass to FALSE, then the error disappeard.
But this solution not so good...

How can I solve this problem ?

Thanks for any advice...

[Wild_Pointer]

Hello, vajtsz,
I'm not sure what zeos is doing when you "click on a menupoint", but I can share my experience here.

I've noticed that if I do a TZQuery scan (read every row in a cycle) my program performance is poor. Also like You I observed, that SQLHourGlass := False eliminates the problem. So I removed the pointer picture changing from the FetchRows function in TZAbstractRODataset class.

patch is made using git - sorry about that. Also it is for Zeos 7, but I hope you will find it helpfull.

[mdaems]

No way to implement is as a ZQuery property? That way I'd certainly add a decent patch to the zeoslib source code.

Mark

[Wild_Pointer]

Hello mdaems,

sorry for a long delay. I would be glad to produce such a patch, but I wanted to consult You a bit before coding:
1) by saying ZQuery property did you mean new property of a component or a new parameter in TZQuery.Properties?
2) what name would you suggest for it? BusyPointerWhenScrolling seems bit too long...
3) I made the change like this because changing the pointer for row fetching seems useless to me, so I don't want it in any of my datasets. I understand some people might find it useful in their applications. Any thoughts on that? Maybe a parameter on TZConnection ?

[mdaems]

Wild_Pointer,

It's maybe a lot easier. When scanning a dataset row by row it shouldn't be necessary to set the hourglass.
So can you test this solution:
Set the hourglass when Rowcount = 0 (all rows) OR RowCount - CurrentRows.Count >1 (more than 1 row)

Of course, that makes it still slower for people scanning every second row, but I think that's quite a rare case.

Can you test and send a new patch? (Or do you want svn access? In that case, send me your sourceforge username.)

Mark

[Wild_Pointer]

Hello mdaems,

it took me some time . As usual, when a temporary solution is made, the proper fix is postponed.
Anyway after switching to Zeos 7.0.6 we faced the same decrease in performance so here I am sending a patch. It speeds things up for me.

I've used git on 7.0.6 version of ZeosLib. If I should use SVN or another branch to edit the file please let me know. I will try... (I'm not a SVN user)

[marsupilami]

Hello

I just want to add my experience with this problem. Maybe that helps you in taking decisions, maybe not
I had the exactly same experience with my applications becoming slow on some computers. I used a profiler and found out, that the action that seems to take a long time is not setting the mouse pointer to the SQL pointer, but going back to the original state takes long. My program spent 95 percent of its time in that procedure.
I decided, I cannot solve the problem at the root - setting the mouse cursor to default again. But I can circumvent it by taking the responsibility for changing to SQL and back on myself. So I introduced the following structure in the parts where my customers told me they are slow:

Code: Select all

  Datamodule.ZConnection.ShowSQLHourGlass;
  try
    // Do DB-intensive things
  finally
    Datamodule.ZConnection.HideSQLHourGlass;
  end;

Best regards,

Jan

[Wild_Pointer]

Hello, marsupilami

I thought about that too, like replacing all ZQuery.Open with OpenDataSet(ZQuery) where

Code: Select all

procedure OpenDataSet(ADataSet: TZQuery)
begin
  ADataSet.Connection.ShowSQLHourGlass;
  try
     ADataSet.Open;
  finally
    ADataSet.Connection.HideSQLHourGlass;
  end; 
end;

After thinking about that a bit longer I realized, that I would have to make a huge change to the program to manually trigger HourGlass. And the worst part of that decision - other programmers may still use ZQuery.Open (because they are used to it) and the HourGlass would not be displayed... So only then I made a patch attached above...

Thank you for your input! It is always good to know how other people solve the problems you have...

[marsupilami]

Hello Wild_Pointer,

I think, this is a misunderstanding. I always operate with ZConnection.SQLHourGlass = true. So on each TZQuery.Open I get the SQL hour glass. Usually if the operation gets slow on a point like this, this is because of components that get their list data by scrolling the result of the query. Sometimes this is also necessary to do by my programs.
On these Points I call ZConnection.ShowSQLHourGlass before doing the operations. This way I keep Zeos from setting the mouse pointer back to normal until I do it myself. By doing it this way, I get the sql hour glass at all times and when there are points, where the application gets too slow because of a lot of scrolling, I can implement the above strategy for speeding it up.
Best regards,

Jan

[Wild_Pointer]

Hello marsupilami,

Thank You for the explanation.

Best regards,
Wild Pointer

[WillMometti]

Really a slowness occurs when a SQL statement is executed and SQLHourGlass property of this component ZConnection "True". If you pass the property to "False" the slowness does not occur.

More slowly realized that this only occurs on Windows 7 64-bit and 8. The 32-bit Windows does not have this slowness.

That is, I believe it is some API call that is not compatible with 64-bit Windows.

[marsupilami]

Hello WillMometti,

the reset of the cursor to its original state in TZAbstractConnection.HideSQLHourGlass seems to take a long time - but I don't know why:
DBScreen.Cursor := CursorBackup;
On the other hand a similar line in TZAbstractConnection.ShowSQLHourGlass seems to work as fast as expected:
DBScreen.Cursor := dcrSQLWait;
Maybe it is even some Windows bug? That is why I decided to not try to debug this problem but apply the workaround described above. Below you can find the code for both methods.
Best regards,

Jan

Code: Select all

{**
  Turn on sql hourglass cursor
}
procedure TZAbstractConnection.ShowSQLHourGlass;
begin
  if not FSqlHourGlass then
    Exit;

  if SqlHourGlassLock = 0 then
  begin
    if Assigned(DBScreen) then
    begin
      CursorBackup := DBScreen.Cursor;
      if CursorBackup <> dcrOther then
        DBScreen.Cursor := dcrSQLWait;
    end;
  end;
  Inc(SqlHourGlassLock);
end;

{**
  Turn off sql hourglass cursor
}
procedure TZAbstractConnection.HideSQLHourGlass;
begin
  if not FSqlHourGlass then
    Exit;

  if SqlHourGlassLock > 0 then
    Dec(SqlHourGlassLock);
  if SqlHourGlassLock = 0 then
  begin
    if CursorBackup <> dcrOther then
      if Assigned(DBScreen) then
        DBScreen.Cursor := CursorBackup;
  end;
end;

[wiwiechris]

Hallo @All

some observations regarding that issue:

* The issue seems to persist in the 7.1.3stable code
* Win7 32/64 Bit OS seems to be not the core reason (On a Win7 Home Premuim it "runs", on a Win7 Prof it "does not").
* In our environment setting SQLHourGlass to FALSE and setting it to TRUE after the operation seemed to solve the problem.

Best regards
Chris

D2010, ZEOS 7.1.3stable