Complex queries in LSCS ?

OK, running some benchmarks with complex queries. Playing with the query to improve as I can.

Thing is, I am getting it down to 100ms per complex query (querying multiple EAs - within a Date range, and specific model)

That seems to be pretty slow.

Queries for specific DCR values are 4-5 msec.

Am I jaded ? Does this seem reasonable ?

Funny thing, we have tried it on a few different servers, one is a super beefy Solaris server with 32GB of RAM and 8 multi-threaded CPUs.

It seems to be spending a ton of time querying the DB.

Any tips/pointers/RTFMs appreciated.

Find more posts tagged with

Comments

Bowker

I have seen the same thing with date comparisons. Our queries include something like:

now >= onlineDate AND now < offlineDate

That seemed to slow everything down by about the same amount you are seeing.

The only option I could see is to run a nightly job to set some other attribute based upon the onlineDate and offlineDate and use that attribute instead.

Adam Stoller

I have seen the same thing with date comparisons. Our queries include something like:

now >= onlineDate AND now < offlineDate

That seemed to slow everything down by about the same amount you are seeing.

The only option I could see is to run a nightly job to set some other attribute based upon the onlineDate and offlineDate and use that attribute instead.

Not that I'm doing anything with LSCS querying, so take this with the proverbial grain of salt, but if you converted dates to seconds-from-epoch (perhaps in another field or something) so that it was available in the DB as an INT or LONGINT or something like that, it might make the comparison's faster - though you'd have to convert "now" the same way before attempting to perform the comparisons.

It would be interesting to verify if the issue is with date/time manipulation during the query, or any mathematical comparison performed in the query.

Rick Poulin

Haven't tried this myself, but I saw a note in a recent HP presentation about pre-indexing fields that you query on repeatedly (similar to an index in a DB, I suppose). It was just a matter of specifying that field in idol's AutonomyContent.cfg using the weird internal syntax for that field name.

Bowker

Fish -

I believe internally dates are stored as INTs of some flavor so either you do the conversion in your code or IDOL/Database does it. I don't think that will matter.

However I am interested in the pre-indexing option. Any more information on how to do this?

Rick Poulin

However I am interested in the pre-indexing option. Any more information on how to do this?

Not sure if the slide deck I have is meant for distribution, so here's the copy+paste of the relevant portion.

Configuration: Config. [SetMatchFields]
Specify fields whose entire value is frequently used as a restriction in a search and are thus stored for optimized matching. Such fields cannot be numeric or parametric.

In the IDOL configuration file located in /idol/IWLSCSContent.cfg, add */ to the PropertyFieldCSVs property under [SetMatchFields] section.
For example, if you are frequently running queries on TeamSite/Metadata/Products attribute, then you can add the /idol/IWLSCSContent.cfg as follows:

[SetMatchFields]
Property=MatchFields
PropertyFieldCSVs=*/LSCS_STR_MVF_TeamSite__HSALS__Metadata__HSALS_Products

*Notice the format difference. You can find the corresponding format to put into IDOL by opening the lscsidx file generated by the PLC workflow.

A real customer example:

Property=MatchFields
PropertyFieldCSVs=*/parentDir,*/id,*/path,*/projectName,*/ARCHIVED,*/isDeleted,*/LSCS_STR_TeamSite__HSALS__Templating__HSALS__DCR__HSALS__Type,*/LSCS_STR_TeamSite__HSALS__Metadata__HSALS__AliasID,*/LSCS_STR_TeamSite__HSALS__Metadata__HSALS__LegacyDocID,*/LSCS_STR_TeamSite__HSALS__Metadata__HSALS__Audience,*/LSCS_STR_TeamSite__HSALS__Metadata__HSALS__Webable,*/LSCS_STR_TeamSite__HSALS__Metadata__HSALS__isFAQ,*/LSCS_STR_G11N__HSALS__Locale,*/LSCS_STR_MVF_TeamSite__HSALS__Metadata__HSALS__ID,*/LSCS_STR_MVF_TeamSite__HSALS__Metadata__HSALS__Products,*/LSCS_STR_MVF_TeamSite__HSALS__Metadata__HSALS__Projects,*/LSCS_STR_MVF_TeamSite__HSALS__Metadata__HSALS__OS,*/LSCS_STR_MVF_TeamSite__HSALS__Metadata__HSALS__PageInfo,*/LSCS_DT_TeamSite_HSALS__Metadata__HSALS__ExpireDate,*/LSCS_DT_TeamSite_HSALS__Metadata__HSALS__ReleaseDate,*/LSCS_DT_TeamSite_HSALS__Metadata__HSALS__NewsReleaseDate,*/LSCS_DT_TeamSite_HSALS__Metadata__HSALS__NewsExpireDate,*/LSCS_DT_TeamSite_HSALS__Metadata__HSALS__PartsCategory,*/docStatus

Configuration: IDOL Config. [SetNumericFields]
Specify which fields are entirely numeric (or a csv of numbers) to be stored for optimized numeric matching

In the IDOL configuration file located in /idol/IWLSCSContent.cfg, add */ to the PropertyFieldCSVs property under [SetNumericFields] section.

Customer example:

Property=NumericFields
PropertyFieldCSVs=*/contextDeployedDate,*/fileSize,*/LSCS_NUM_*

Configuration: regeneratematchindex=true
For SetNumericFields and SetMatchFields to take effect add:

regeneratematchindex=true

to the [server] section in IWLSCSContent.cfg and then restart IDOL. Once you restart IDOL, you need to remove this setting or set it to false. You do not want to regenerate every time.

nipper

Cool, thanks Rick

nipper

I tried some of these, redeployed the content and restarted the server. Didn't see much of a speedup (I am using Apache Benchmark)

Should I DREINITIAL and then deploy ?

Adam Stoller

Have you already tried DRECOMPACT'ing?
Have you configured it to do so nightly? (that's what I've done in the past since almost every problem I've had with IDOL - both for LSCS and Search - usually starts with a suggestion to do that from Support)

nipper

I went ahead and did a REINITIAL, dumps all projects and redeployed. Still not seeing a significant change in performance.

Rick Poulin

I have seen the same thing with date comparisons. Our queries include something like:

now >= onlineDate AND now < offlineDate

That seemed to slow everything down by about the same amount you are seeing.

The only option I could see is to run a nightly job to set some other attribute based upon the onlineDate and offlineDate and use that attribute instead.

For what it's worth, I too can second these findings.. the performance on the range operators is less than stellar. In my case the solution was runtime caching of the results, because for all of these suggestions, directly pinging LSCS still performs too slowly for my requirements.

Have you tried uninstalling LSCS and installing a NoSQL database instead? Smiley Very Happy

nipper

Actually I am on 741 Linux, I have a Perl script that runs 1000 queries (no caching) with 9 different URLs.

1 just gets the data, which takes about 10msec each. 7 of the other 8 run in about 20msec each. The last takes 80-100 msec and I am trying to find out why.

But most of my queries use this: (Start_Date Smiley Embarassed

=2014-04-21+AND+End_Date:<=2014-04-21) and when I removed it, did not make a difference.

nipper

OK, so this is really weird

One of the items is Product with A,B,C,D,E,F

I tried Products:* and it would take 90msec each

I tried Products:A,B which took 22 msec, Products:A,B,C was 24 msec, but when I went to Products:A,B,C,D it skyrocketed to 90msec each

I drop back to Products:A,B,D (making certain it was not data related) and it went back to 24msec.

what am I missing ?

nipper

OK, interestingly enough, it appears you can only index up to 8 EAs. The one that was 90msec was using #9. When I removed one of the others, it got much faster.