How To Make Special Query faster

itSDS · Yesterday 08:54:12

Hi Arnaud, using ConnectionProperties with UniDac i have the following Problem:

I Have to Create a Temp Table on my SQL Server (MS-SQL or MySQL)
The Table has only 1 Field TempID BIGINT (für int64 ID)

Now i have to insert 1 to 50K or 100K ID depends on the case into to Table. (It is used in other Queries to replace IN ... Queries with an INNER JOIN)

In Principle i have to execute a SQL Script

Begin Transaction
create table #tmp_ids_1F2575D95F4141A3B67B5051843D65F6 (TempID bigint primary key)
insert into #tmp_ids_1F2575D95F4141A3B67B5051843D65F6 (TempID) values (412953),(456388),(494710),(503522),(545873), ...
insert into #tmp_ids_1F2575D95F4141A3B67B5051843D65F6 (TempID) values (546308),(548805),(555293),(555830),(598162) ...
Commit

I tried now different ways but all take too long (About 1second for 50K ID's)

1. i tried to ExecuteInlined 50 Lines of code
2. i tried TUniScript Component

i found out: If i use Uniscript and repeat the same Insert Script 2 or more Times the Execution time goes down from 1000ms to 100-200 ms

ChatGPT Means that it could be done in under 50ms

what do you think ? (Or may be any other has a good idea)

ab · Yesterday 09:54:33

It sounds more like a UniDac question to me.

I guess ChatGPT is hallucinating, because 50K inserts per second is already not bad for a remote SQL database, and 50ms should be around 1,000,000 inserts per second which is way too much expectation for such a remote DB from my tests.

ttomas · Yesterday 15:11:57

Try to create table without index (primary key). After inserts, create index. Inserting in table with index is slower.

ab · Yesterday 15:56:19

My guess is that array binding of parameters is the way to go, but I don't think UniDAC supports it.

itSDS · Today 12:02:14

The Version with Index after Inserts i tried. but no real difference

Array Binding with UniDac i also tried, but it creates 50 Insert Statements with 1000 IDs as i did - also no difference.

itSDS · Today 12:08:48

I post my little code here, may be you see something
Some Testcode is commented out

procedure TDFSynDBStringBuilder.CreateTempTable(const AIDs: TIDDynArray; var ATablename: String);
begin
  TSynLog.Enter('Create Temp Table for % IDs', [System.Length(AIDs)], Self);
  if ATablename.IsEmpty then
    ATablename := 'tmp_ids_' + StringReplace(TGUID.NewGuid.ToString, '{', '', []).Replace('}', '').Replace('-', '');

  var LConnection := TSqlDBUniDACConnection(FConnectionProperties.MainConnection);
  var LInTransaction := LConnection.InTransaction;
  if not LInTransaction then begin
    if not LConnection.Connected then
      LConnection.Connect;
    LConnection.StartTransaction;
  end;
  try
    var LScript := TUniScript.Create(nil);
    var LSQLStream := TStringStream.Create;
    LScript.Connection := LConnection.Database;
    try
      var LBatchSize := 1000;
      case DBMS of
        dMySQL : begin
          LBatchSize := 10000;  // Teste
          SimpleQuery('create temporary table '+ATableName+' (TempID bigint primary key)'); // .ExecuteInlined(false);
          LSQLStream.WriteString(ToString);
        end;
        dMSSQL : begin
          ATablename := '#'+ATablename;
          LBatchSize := 1000;
          SimpleQuery('create table '+ATableName+' (TempID bigint primary key)'); // .ExecuteInlined(false);
          LSQLStream.WriteString(ToString);
        end;
      end;

    // Benutze UniDac Batch Insert Methode ! (Leider auch maximal 1000 / Execute !)
  //    var Q := TUniQuery.Create(nil);
  //    try
  //      Q.Connection := LConnection.Database;
  //      Q.SQL.Text := 'INSERT INTO '+ATablename+' (TempID) VALUES (:id)';
  //      Q.ParamByName('id').DataType := ftLargeInt;
  //      Q.Params.ValueCount := System.Length(AIDs);
  //
  //      for var i := 0 to High(AIDs) do
  //        Q.Params[0][i].AsLargeInt := AIDs[i];
  //
  //      Q.Execute(System.Length(AIDs));
  //    finally
  //      Q.Free;
  //    end;
  // Maximal 1000 Werte pro Zeile !
      var i := 0;
      while i < System.Length(AIDs) do begin
        SimpleQuery('insert into '+ATableName+' (TempID) values ');
        for var j := 0 to LBatchSize - 1 do begin
          if i >= System.Length(AIDs) then
            Break;
          Append('(').Append(AIDs[i]).Append('),');
          Inc(i);
        end;
        // letztes Komma entfernen
        Length := Length - 1;
        LSQLStream.WriteString(ToString);
//        ExecuteInlined(false);
       end;
  //    case DBMS of
  //      dMySQL : SimpleQuery('CREATE UNIQUE CLUSTERED INDEX idx_tempid ON '+ATableName+' (TempID)').ExecuteInlined(false);
  //      dMSSQL : SimpleQuery('CREATE UNIQUE INDEX idx_tempid ON '+ATableName+' (TempID)').ExecuteInlined(false);
  //    end;

      LScript.ExecuteStream(LSQLStream);
      if not LInTransaction then
        LConnection.Commit;
    finally
      FreeAndNil(LSQLStream);
      FreeAndNil(LScript);
    end;
  except
    if not LInTransaction then
      LConnection.Rollback;
    raise;
  end;
end;

mORMot Open Source

#1 Yesterday 08:54:12

How To Make Special Query faster

#2 Yesterday 09:54:33

Re: How To Make Special Query faster

#3 Yesterday 15:11:57

Re: How To Make Special Query faster

#4 Yesterday 15:56:19

Re: How To Make Special Query faster

#5 Today 12:02:14

Re: How To Make Special Query faster

#6 Today 12:08:48

Re: How To Make Special Query faster

Board footer