Aurelius slow when querying using _In with many items

BIZ TMS Aurelius
1

Using SQL Server and I was recently attempting to query using TLinq._In with a relatively large array (~1200 items) and found it really slow to retrieve the entities (ListValues call >10s) despite Profiler showing that the SQL command was executed really quickly (<150ms duration).

I did some digging and found that the call to TParams.ParseSQL in TOdbcNativeStatementAdapter.SetSQLCommand seems to be the culprit. TParams.Update fires for each new parameter added and this loops the entire collection again to clear TParam.FParamRef. I seem to be able to remedy this by wrapping the ParseSQL call with BeginUpdate..EndUpdate.

2

So, it looks like an unoptimized TParams.ParseSQL implementation then, which is Delphi Code?

You mean the workaround is this?

procedure TOdbcNativeStatementAdapter.SetSQLCommand(SQLCommand: string);
begin
  FSqlParams.BeginUpdate;
  try
    FSQL := FSqlParams.ParseSQL(SQLCommand, True);
  finally
    FSqlParams.EndUpdate;
  end;
  FreeAndNil(FPreparedStatement);
  FOpenStatement := nil;
  FPrepared := False;
  FParamsBound := False;
end;
1 Like
3

Yes, exactly! :+1:

4

Ok, great. Thanks for reporting, fix will be included in next release.

1 Like
5

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.