on 2016 Feb 05 7:27 AM
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∗'))
Request clarification before answering.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
47 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.