TMSTable with 'Filter' setting very slow on Open

[lcoelho]

Dear Sirs,

I have a project I migrated from Delphi 5 to Delphi XE3. Both projects use your SDAC TMSTable component. However, after the migration, the Delphi XE3 version hangs (or otherwise is very, very slow) when oppening the TMSTable component in the following code:

TMSTable1.Filter := 'PersonRef = ' + '''' + PersonRef + '''';
TMSTable1.Filtered := true;

if TMSTable1.active = false then
begin
TMSTable1.open; --< Hangs here forever!!!
end;

If I change 'TMSTable1.Filter' to 'TMSTable1.FilterSQL' (for server-side filtering) then it is better but I would like to know why it suddenly takes so long with Filter? I have many other projects and forms that use 'Filter' and I wouldn't want to have to change them all to FilterSQL.

Thank you and kind regards

[AlexP]

Hello,

We cannot reproduce performance loss in older Delphi versions at local filtering - the test results on older and newer Delphi versions are similar. Please specify the field type you are filtering data by (or the script for creating the table), in addition, specify the number of records in the table.

[lcoelho]

Hi AlexP,

Thanks for your reply, please find the script to create the table structure for the table that is involved. There are 105197 records in the table.

As mentioned, in Delphi 5, it takes a few seconds but does return results. However, in Delphi XE3 it goes on forever without returning results.

Here is the script:

CREATE TABLE [dbo].[Employ](
[EmployNbr] [char](8) NOT NULL,
[CompRef] [char](8) NULL,
[PersonRef] [char](8) NULL,
[JobFunctId] [char](4) NOT NULL,
[JobTitle] [char](40) NULL,
[DateEmplyd] [smalldatetime] NULL,
[EmployeeNo] [char](20) NULL,
[Status] [char](2) NULL,
[ActiveStat] [char](1) NULL,
[IndustryNo] [char](8) NULL,
[TermStat] [char](1) NULL,
[TermYearNbr] [char](4) NULL,
[TermMonthNbr] [char](2) NULL,
[ValidatedDptmntYN] [char](1) NULL,
[DepartmentForm] [char](20) NULL,
[CompDptmntNbr] [int] NULL,
[ValidatedCCYN] [char](1) NULL,
[StaticPosYN] [char](1) NULL,
[CCFreeForm] [char](20) NULL,
[CompCCNbr] [int] NULL,
[BranchSetUpYN] [char](1) NULL,
CONSTRAINT [XPKEmploy] PRIMARY KEY CLUSTERED
(
[EmployNbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Employ] WITH CHECK ADD FOREIGN KEY([PersonRef])
REFERENCES [dbo].[Person] ([PersonRef])


Thank you for looking into this. Kind regards.

[AlexP]

Hello,

Using your sample, the difference between execution on different Delphi versions is about 0.1 second, and this is due to the fact that older Delphi versions are unicode, and since you filter by a text field, the performance is a bit lower, as the methods of unicode strings comparison are slower.

[lcoelho]

Hi,

Thanks for testing. There must be another problem then because on my side it takes forever (I haven't checked how long because I stop the process after about 20 minutes). But, when I change to FilterSQL then it is fine.

Is there something else you think I should check?

Thank you and kind regards

[AndreyZ]

In order to help you, I need the sample that demonstrates this problem. Please try creating such sample and send it to andreyz*devart*com . Also please specify the following:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of SQL Server server and client. You can learn it from the Info sheet of TMSConnection Editor.

Also note that this problem may be connected with the performance issue when working with string fields in Delphi XE3 that was discussed at http://forums.devart.com/viewtopic.php?f=6&t=26258 . Here is a link to Embarcadero Quality Central: http://qc.embarcadero.com/wc/qcmain.aspx?d=111942 . As you can see, this issue was fixed in Delphi XE4. If it possible for you, you can check if this problem persists in Delphi XE4.

[lcoelho]

Hi AndreyZ,

I have been running different tests to try and narrow down the problem.

To be more specific, the problem occurs when a table has a lookup field to another table. It seems that if the lookup fields are char or nvarchar, the problem occurs. Here is the setup.

Table1 (1600 records):
SchemeRef int
PersonRef char(8)
ActiveYN char(1)

Table2 (about 100000 records):
PersonRef char(8)
Surname char(60)
FirstName char(40)

In Table1, add a lookup field called 'Surname' with key field 'PersonRef', Data Set 'Table2', Lookup Key 'PersonRef' and Display Field 'Surname'. To this table (Table1) add the following filter ActiveYN = 'Y' and check the 'Filtered' checkbox. Then check the 'Active' option. It hangs indefinitely.

The problem is with SDAC because when I use Delphi XE3 dbGo connection and table components, it works fine without hanging.

Thank you for looking into this.

[AndreyZ]

Thank you for the information. We have reproduced the problem and investigation of the problem is in progress. As soon as we have any results, we will let you know.

[lcoelho]

Hi AndreyZ,

An further progress on this problem? In the meantime, what do you recommend we do? Should we replace the TMSTable components that have this problem with Delphi's native dbGo table components?

[AndreyZ]

This problem occurs only when local filtering is used for lookup fields. As a temporary workaround, you should not use local filtering for lookup fields. We will fix this problem in the next SDAC build.

[lcoelho]

Hi AndreyZ,

Thanks for sending me the workaround.

Before we change all 'Filter' properties to 'FilterSQL' in our projects, I just want to know whether there are any consequences this may have on anything else? What is the advantage of using FilterSQL instead of Filter? Would you recommend using FilterSQL allways instead of Filter?

Thank you and kind regards

[AndreyZ]

The Filter property is used for local filtering of a dataset and the FilterSQL property for filtering on the server side. FilterSQL adds conditions to the WHERE statement of your queries. For more information about the FilterSQL property, please refer to the SDAC documentation. It is up to you to decide which approach is more appropriate for you.

[AndreyZ]

Thank you for the information. We have fixed this problem. This fix will be included in the next SDAC build.