2

I'm Working in cognos 10.1 now.

I'd like to find the names which start with 'AB', 'CE', 'JA'. I'm concerned about the query's performance as the query subject (table) contains about 1,000,000+ records. Which filter should I use?

substring ([Participant],1,2) in ('AB', 'CE', 'JA')

or 

[Participant] like 'AB%' or [Participant] like 'CE%' or [Participant] like 'JA%'

Which would execute faster?

arunpandiyarajhen
  • 643
  • 12
  • 20
  • 51

2 Answers2

2

My experience in the past has suggested that if you're interested in strings that start with a particular character set then LIKE will yield a better result than the SUBSTR method, but the benefits generally only appear for strings of sufficient length for the optimiser to believe that an index scan is beneficial. This has usually been more than two characters, as I recall, so you may not see benefits in your case.

With the substr() predicate, in the absence of a function based index (see below), the best sort of index access you can hope for is a fast full index scan, which would generally not be as good as a regular index access method which Like might allow.

However, it is possible to define a function-based index on Substr(participant,1,2) that could be used by the substr() function. It would only be worthwhile if the start and length arguments on the substr (1 and 2 in your case) are fixed. A bitmap index may be a good choice if the table modification patterns make it suitable for them in general

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
2

You don't have an index on that column. So the only choice the optimizer has is a Full Table Scan. Frankly, the precise syntax of the filter isn't going to make any difference to that cost.

As David suggests, building an function-based index on the substr(participant,1,2) could give you some benefits. But it's only worthwhile if this is the sort of query you'll run a lot.

APC
  • 144,005
  • 19
  • 170
  • 281