Hi, what is wrong with this? It needs 6 seconds to execute from EDBManager on Server:

SELECT
SSL.SessionName,   
SSL.ObjectName,   
SSL.LockType,   
SSL.Number as RowNumber,   
SS.User,   
SS.IPAddress,
SS.Process
FROM Configuration.ServerSessionLocks SSL
LEFT JOIN Configuration.ServerSessions SS ON (SS.ID = SSL.SessionID)

WHERE LockType = 'Row'
AND UPPER(ObjectName) = UPPER('POSITIONEN')

Yusuf Zorlu
MicrotronX

Yusuf


Speed or lack of it is generally down to indices (or hardware). The way to find out is to run the query in EDBManager, Request Execution Plan and have a look at the results


This is what I get (on a local table)

UPPER("ObjectName") = UPPER('POSITIONEN') AND "LockType" = 'Row'

Row scan (ServerSessionLocks): 0 rows, 0B estimated cost

Hint: Create index(es) (ServerSessionLocks) on column(s) "LockType" for
possible better performance

IE its doing a row scan. You can't add an index here - that's down to Tim. There's no need for the UPPER() since the column is defined as ANSI_CI

6 seconds still seems slow unless you have a lot of locks, or EDBManager is having to "freeze" the situation to read the table

Try in EDBManager and see what it tells you. Post the result here


Roy Lambert

Hi Roy,

i don't know why but sometimes this select is hanging. This is on a customer side with >20 users with a total of more than 100 sessions.

I have added more fields as filter and it is better + i abort the query if it needs more than 5 seconds.
We need this only to show "Record locked by ... user".

Possible that tim can optimize this on server-side so it results faster ...

Yusuf Zorlu
MicrotronX

Yusuf

Again

The way to find out is to run the query in EDBManager, Request Execution Plan and have a look at the results. Post them here so we (and Tim) canget an idea of what's happening.

Roy Lambert

Roy Lambert wrote:

> Request Execution Plan

You can do that in code too can't you? Worth logging it.

--

Matthew Jones

Matthew


You sure can but unless I'm constructing a massive statement in code I find it easier to use EDBManager. Even when I build the sql on the fly I generally use showmessage to display it then cut'n'paste into EDBManager.

With the ability to enter parameters in EDBManager I rarely have reason to run in code these days.

Roy Lambert

Roy Lambert wrote:

Having to run it manually strikes me as painful. What I do is wrap all my queries with a prepare and check call, which in release is inert, but in development does the following:


{$IFDEF VERIFY_SQL}


procedure SQLVerifyPrepare(xQuery : TEDBQuery);
begin
{$IFDEF DEBUG}
   xQuery.RequestPlan := True;
{$ENDIF}
end;

procedure SQLVerifyCheck(xQuery : TEDBQuery; const szLocation : string);
begin
{$IFDEF DEBUG}
//    LogReport(leInformation, 'PLAN "' + szLocation + '" ' + xQuery.Plan.Text);
   if Pos('Row scan', xQuery.Plan.Text) > 0 then
   begin
       LogReport(leInformation, '##################################');
       LogReport(leInformation, '##################################');
       LogReport(leInformation, 'Query not optimal - ' + szLocation + #13#10 + xQuery.Plan.Text);
       LogReport(leInformation, '##################################');
       LogReport(leInformation, '##################################');
   end;
{$ENDIF}
end;

{$ENDIF}


This tells me that I have something inefficient happening, and thus tells me to go looking. In one version I check how long the query took (use thread-safe variable for the before time) so that if it takes a long time, I can again look to see if it was reasonable.

Automating this, perhaps optional on released systems, allows me to get an understanding of typical behaviour of the logged system.

--

Matthew Jones

Matthew


Neat

Roy Lambert

"Matthew Jones"

thanks for this!

Yusuf Zorlu
MicrotronX

Matthew


Out of interest had you thought of subclassing the TEDBQuery component so that all you'd need to do is flip a boolean to enable the logging? It would add a smidgeon to your code size but would enable you to turn it on/off at client sites dead easily.

Roy Lambert