cancel
Showing results for 
Search instead for 
Did you mean: 

full text search query

Former Member
1,781

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∗'))

Accepted Solutions (0)

Answers (1)

Answers (1)

Breck_Carter
Participant

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;