full text search query

[Former Member]

Can anyone help with the queries below as I have found the query really slows down when I use an "OR" statement.

I have the same database in MsSql Server and we have no problem with the “OR” Statements.

Can you tell me what i am doing wrong and how I can improve the queries blow to speed up.

Table [PA] Has 718069 Row of data

Select idPA from PA where contains(A, B, '"robin∗" OR "hood∗"')

More Complex

Select idPA from PA where contains(L, M, '"robin∗" OR "hood∗"') OR contains(C, '"Arrow∗"')

Table [PA] Has 718069 Rows Table [CP] Has 1005764 Rows Table [CA] Has 170000 Rows

SELECT DISTINCT [PA].[idPA] AS RecordCount FROM [PA]
LEFT OUTER JOIN [CP] ON [PA].idPA = [CP].IDPA
LEFT OUTER JOIN [CA] ON [CP].idCA = [CA].IdCA WHERE Contains([CA].[A],'Alan∗')

More Complex

SELECT DISTINCT [PA].[idPA] AS RecordCount FROM [PA]
LEFT OUTER JOIN [CP] ON [PA].idPA = [CP].IDPA
LEFT OUTER JOIN [CA] ON [CP].idCA = [CA].IdCA WHERE (Contains([CA].[A],'Alan∗')
OR Contains([CA].[B],'Hood∗'))

[Breck_Carter]

OR clauses often perform badly. Perhaps SQL Anywhere doesn't optimize OR clauses involving full text searches as well as SQL Server.

One approach MIGHT be to eliminate the OR clauses altogether.

CAVEAT EMPTOR 1: I have NOT tested the following code.

CAVEAT EMPTOR 2: I do NOT know if the following code will improve performance.

-- Original query.

SELECT DISTINCT [PA].[idPA] AS RecordCount FROM [PA] 
LEFT OUTER JOIN [CP] ON [PA].idPA = [CP].IDPA
LEFT OUTER JOIN [CA] ON [CP].idCA = [CA].IdCA WHERE (Contains([CA].[A],'Alan*')
OR Contains([CA].[B],'Hood∗'))

-- Alternative 1: Divide and conquer to eliminate OR.

SELECT [CA].IdCA
  INTO LOCAL TEMPORARY TABLE [tempCA]
  FROM ( SELECT [CA].IdCA FROM [CA] WHERE (Contains([CA].[A],'Alan*')
         UNION DISTINCT
         SELECT [CA].IdCA FROM [CA] WHERE (Contains([CA].[B],'Hood*')
       ) AS [CA];

SELECT DISTINCT [PA].[idPA] AS RecordCount FROM [PA] 
LEFT OUTER JOIN [CP] ON [PA].idPA = [CP].IDPA
LEFT OUTER JOIN [tempCA] ON [CP].idCA = [tempCA].IdCA;

-- Alternative 2: Use a derived table instead of a separate temporary table.

SELECT DISTINCT [PA].[idPA] AS RecordCount FROM [PA] 
LEFT OUTER JOIN [CP] ON [PA].idPA = [CP].IDPA
LEFT OUTER JOIN ( SELECT [CA].IdCA FROM [CA] WHERE (Contains([CA].[A],'Alan*')
                  UNION DISTINCT
                  SELECT [CA].IdCA FROM [CA] WHERE (Contains([CA].[B],'Hood*')
                ) AS [CA] 
                ON [CP].idCA = [CA].IdCA;