You are viewing limited content. For full access, please sign in.

Question

Question

Have issues with field search performance with LF 9.2.1 on Oracle

asked on June 2, 2015

I am working with a customer that has Oracle 11g as the DBMS.  In the test environment we have noted that when running a field search for a single value with no wildcards the performance difference between 8.2 and 9.2.1 is dramatically slower.  In 8.2 we are seeing a 1 second search time to return the results, whereas in 9.2.1 we are seeing 15 seconds or more to return the same results.  

The customer's Oracle DBA is analyzing the query that is running, but I thought I may get an answer here.  Any ideas why this dramatic difference in search times?

Thanks,

Bryce

1 0

Replies

replied on June 2, 2015

The Oracle DBA had the following input on this issue.

 

The system does some (unnecessary) NLS conversions during its queries

So, for example the following query runs in 30 seconds


insert into laserfiche.search_entry_cache2 (tocid, parentid, linkto)
select distinct t.tocid, t.parentid, t.linkto
from laserfiche.toc t
where t.tocid <> 2
and ((exists (select  tocid from laserfiche.propval pv
              where pv.tocid=t.tocid
              and pv.prop_id = 103
              and (((nls_lower(pv.str_val) = N'b190551803'))))))
and bitand(t.toc_flags, 2048) = 0
/

 

This should ideally use the index on propval.str_val in order to hit the specific record we are looking for but in fact does a full scan of propval due to the NLS conversion on the matching string (the “N” before the string).

Remove that “N”, and it runs in under 1 second.

I assume this is why you were seeing performance gains by converting from string to numeric?

I’ve not been successful in finding an Oracle way to get around this as yet so not sure on next steps.

2 0
replied on June 4, 2015 Show version history

I think the above is hard to follow. I asked the customer to convert the field to a number field and that provided near instant search results but there's another text field that's also slow so we're looking for a more general solution, such as a recommendation about a new index or converting the propval.str_val column to a different string type. 

 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.