I’m trying to add a prompt to a query based on a Sybase db that uses a % wildcard to return all values. I used the standard formula that has worked for me in SQL Server.
( account.account_cd IN @Prompt('Enter Account Number(s) or % for All:','A',,Multi,Free) OR '%' IN @Prompt('Enter Account Number(s) or % for All:','A',,Multi,Free)
So the SQL is generated like:
( account.account_cd IN (‘xxxxxxxx’) OR ‘%’ IN (‘xxxxxxxxx’))
This takes 20 minutes to return data when the OR ‘%’ IN (‘xxxxxxxxx’) is part of the query. If I cut that OR out it runs in seconds. When running a show plan on the query with the OR ‘%’ IN (‘xxxxxxxxx’) it table scans an additional worktable. Also account_cd is an index.
Does anyone have any ideas if there is a Sybase issue with this or if there is an optimizer setting that would help?
Any suggestions would be appreciated.
Thanks,
Jason
B.O. 5.1.7
Adaptive Server Enterprise/12.5.0.3/EBF 11443 ESD#4/P/RS6000/AIX 4.3.3/rel12503/1939/32-bit/FBO/
The fact that you are using a % sign means that the index will not be used. Indexes will only be used when the wildcard is directly after another character, not at the beginning.
You need to use a case statement so that case value input not ALL then search for it, case if calue input = ALL search for everything.
I have never used sybase before so this may be an issue, but I think the above method will be more efficient and may get rid of your problem.
I’m not using a true wildcard as far as Sybase is concerned. It’s just the character I decided to use to return all values. I could have used ‘All’ or ‘*’ instead of ‘%’ and had the same result. When the ‘wildcard’ is used the slq is generated like this:
( account.account_cd IN (’%’) OR ‘%’ IN (’%’))
Since an account_cd won’t = ‘%’ but ‘%’ = ‘%’ it will return all the account_cds. I just figured Sybase should be able to handle that more efficiently.
I tried using a Case statement but I couldn’t get to parse. This is the code that I used:
CASE WHEN
'%' = @Prompt('Enter Account Number(s) or % for All:','A',,Multi,Free)
THEN ( account.account_cd IN @Prompt('Enter Account Number(s) or % for All:','A',,Multi,Free) ELSE ''
END
I’m also concerned that if it worked the ‘’ resulting from entering a ‘%’ would create an empty ‘and’ in the where clause. Wouldn’t it look like this?
WHERE condition1
AND ‘’
AND condition3
I’ve seen many forum examples where multiple ‘ORs’ and ‘% for All’ have been stacked together and no one seemed to mention a performance problem. I should mention that 1 table that I’m joining to has 18 million rows.
Thanks Again,
Jason
[edited, added bbc CODE formatting for SQL code - Andreas]