SQL Select with AsString is Slow in XE3

[OlliWW]

I have the same code with Delphi 2007 and Delphi XE3, its a simple SQL Select:

Code: Select all

MSSQL1.SQL.Text := 'SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE'
    MSSQL1.Open;
    while not MSSQL1.Eof do
    begin
      object := TMyDataObject.Create;
      object.rec_field1 := MSSQL1.FieldByName('FIELD1').AsInteger;
      object.rec_field2 := MSSQL1.FieldByName('FIELD2').AsString;
      object.rec_field3 := MSSQL1.FieldByName('FIELD3').AsString;
      object.rec_field4 := MSSQL1.FieldByName('FIELD4').AsString;
      List.Add(object);
      MSSQL1.Next;
    end; //while

MSSQL1 is a TMSQuery component.

The Table holds about 9000 rows. This code runs quite fast and very good for years with Delphi 2007. If i use the same code with Delphi XE3 its 10 times slower than Delphi 2007.

Debugging the problem showed that all "AsString" (also: AsAnsiString) - Statements are much slower than in Delphi 2007. I also tried: Fields[0] and AsVariant / Value.
Select-Statements with only AsInteger for example is quite fast, so: Maybe it has something to do with String / Unicode?

I'm using SDAC Pro 6.6.12 with Delphi XE3 Update 2. Its a Mircrosoft SQL 2005 Standard (Problems are also with: 2008, 2008R2 and 2012)

I would be very thankfull for help.

[AndreyZ]

Please specify the script to create the TABLE table.

[OlliWW]

Code: Select all

CREATE TABLE [dbo].[TABLE](
	[FIELD1] [int] IDENTITY(1,1) NOT NULL,
	[FIELD2] [varchar](7) NOT NULL,
	[FIELD3] [varchar](40) NOT NULL,
	[FIELD4] [varchar](7) NOT NULL,
	[FIELD5] [smallint] NULL,
	[FIELD6] [smallint] NULL,
 CONSTRAINT [ANOTHER_TABLE] PRIMARY KEY CLUSTERED 
(
	[FIELD1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Additional Information:
In the meanwhile i tried converting the varchar-fields to nvarchar-fields. The performance is a little bit faster than with varchar-fields, but not as fast as Delphi 2007. And i need this speed because this is a very huge project with many tables for a lot of costumers.

[AndreyZ]

In Delphi 2007, the TStringField.GetValue method is declared as follows:

Code: Select all

function TStringField.GetValue(var Value: string): Boolean;
var
  Temp: String;
  pBuff: PChar;
  Buffer: array[0..dsMaxStringSize] of Char;
begin
  if DataSize > dsMaxStringSize then
  begin
    SetLength(Temp, DataSize);
    pBuff := pChar(Temp);
    Result := GetData(pBuff);
    if Result then
      SetLength(Temp, strlen(PBuff));
  end else
    Result := GetData(@Buffer);
  if Result then
  begin
    if DataSize > dsMaxStringSize then
      Value := Temp
    else
      Value := Buffer;
    if Transliterate and (Value <> '') then
      DataSet.Translate(PChar(Value), PChar(Value), False);
  end;
end;

In Delphi XE3, the TStringField.GetValue method is declared as follows:

Code: Select all

function TStringField.GetValue(var Value: AnsiString): Boolean;
var
  Buffer: TValueBuffer;
  NullIndex: Integer;
  Str: string;
begin
  if DataSize > dsMaxStringSize + 1 then
    SetLength(Buffer, DataSize)
  else
    SetLength(Buffer, dsMaxStringSize + 1);
  Result := GetData(Buffer);
  if Result then
  begin
    if Transliterate and (Buffer <> nil) and (Buffer[0] <> 0) then
      DataSet.Translate(PAnsiChar(@Buffer[0]), PAnsiChar(@Buffer[0]), False);
    // additional code
    Str := TEncoding.ANSI.GetString(Buffer);
    NullIndex := Str.IndexOf(#0);
    if NullIndex >= 0 then
      Value := AnsiString(Str.Remove(NullIndex))
    else
      Value := AnsiString(Str);
  end;
end;

As you can see, in Delphi XE3 there is an additional code (I marked the beginning of it by the '// additional code' line) that influences the performance. We cannot influence such Delphi behaviour. For more information, please write to the Embarcadero support.

[OlliWW]

Thank you for your reply.

I think that causes the problem:
http://qc.embarcadero.com/wc/qcmain.aspx?d=111942

I tried the same code with Delphi XE2 and it works normal-fast as in Delphi 2007. So Delphi XE3 is not usable yet.

[AndreyZ]

You are right, this is a reason of the performance problem.