Performance Issue when substr used in where clause

[Rattler1887]

After researching a problem with a report I found that the fields used in the where clause did not equal because of some data conversion done on one of the tables. During the conversion one of the fields was prefixed with "50".
So now A.Field does not equal B.Field.

So I decided that I would add "WHERE substr(A.Field, 3,15) = B.Field". The system timed out on me.

I read in another forum to add an index, but I don't have permissons to do so. I am just troubleshooting and trying to fix a crystal report.

Thank you in advance...pleas e advise.

[Rabbit]

Try a like with wildcards instead. That tends to run faster than a substring function.

[Rattler1887]

Try a like with wildcards instead. That tends to run faster than a substring function.

I don't think the wildcard is going to help me out, I changed it and it's still running...It's been running for the last 12 minutes

[Rabbit]

Show us what you changed it to.

If the like operator isn't any faster than the substring and your're not allowed to add indexes, there's nothing else you can do.

[Rattler1887]

Show us what you changed it to.

If the like operator isn't any faster than the substring and your're not allowed to add indexes, there's nothing else you can do.

Thanks Rabbit. My original where clause is comparing to fields. When I changed the statement to use wild card it was for a specific case number..., i.e.

[WHERE A.FIELD LIKE '%1999AP000198A XX%'] I guess my statement would be [WHERE A.FIELD LIKE B.FIELD]

[Rabbit]

Well it would be

WHERE A.FIELD LIKE '%' + B.FIELD + '%'

But if it's not running any faster, there's no point.

[Rattler1887]

Thank Rabbit, I appreciate your time. I am going to have to get the DBA to create an index for me and you are correct in that the wild card run's faster than the substr. I timed them both and the wild card cut off about 15 minutes...but overall time is still not acceptable.

[sbyna]

Use REGEXPR_INSTR function
Regards,
Sekhar