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

asked 05 Feb '16, 07:27

mantus's gravatar image

mantus
2612
accept rate: 0%

Comment Text Removed

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;
permanent link

answered 05 Feb '16, 08:36

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15
×4

question asked: 05 Feb '16, 07:27

question was seen: 238 times

last updated: 05 Feb '16, 08:36