|
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] More Complex SELECT DISTINCT [PA].[idPA] AS RecordCount
FROM [PA] 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;
|
