TMSTable with 'Filter' setting very slow on Open

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

TMSTable with 'Filter' setting very slow on Open

Post by lcoelho » Mon 22 Jul 2013 12:58

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TMSTable with 'Filter' setting very slow on Open

Post by AlexP » Tue 23 Jul 2013 11:55

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
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: TMSTable with 'Filter' setting very slow on Open

Post by lcoelho » Tue 23 Jul 2013 14:47

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: TMSTable with 'Filter' setting very slow on Open

Post by AlexP » Thu 25 Jul 2013 12:39

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
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: TMSTable with 'Filter' setting very slow on Open

Post by lcoelho » Tue 30 Jul 2013 13:16

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

Re: TMSTable with 'Filter' setting very slow on Open

Post by AndreyZ » Wed 31 Jul 2013 09:38

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
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: TMSTable with 'Filter' setting very slow on Open

Post by lcoelho » Fri 02 Aug 2013 15:22

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

Re: TMSTable with 'Filter' setting very slow on Open

Post by AndreyZ » Mon 05 Aug 2013 12:40

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
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: TMSTable with 'Filter' setting very slow on Open

Post by lcoelho » Tue 13 Aug 2013 11:28

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

Re: TMSTable with 'Filter' setting very slow on Open

Post by AndreyZ » Wed 14 Aug 2013 07:30

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
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Re: TMSTable with 'Filter' setting very slow on Open

Post by lcoelho » Wed 14 Aug 2013 14:50

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

Re: TMSTable with 'Filter' setting very slow on Open

Post by AndreyZ » Thu 15 Aug 2013 07:25

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

Re: TMSTable with 'Filter' setting very slow on Open

Post by AndreyZ » Mon 09 Sep 2013 06:52

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

Post Reply