ir_max_relevant_answers:500, Warning displayed: SQL portion ... truncated at 32767 records. (471 Views)
Reply
Honored Contributor
VVV_3
Posts: 581
Registered: ‎08-01-2008
Message 1 of 6 (471 Views)
Accepted Solution

ir_max_relevant_answers:500, Warning displayed: SQL portion ... truncated at 32767 records.

Hello, everyone,

 

We've added parameter to sm.ini file:

ir_max_relevant_answers:500

 

But when I login as falcon and use Text search for Incidents, the search is working correctly, 500 records are returned, but the warning is displayed:

"SQL portion of combined query  has been truncated at 32767 records. Your IR query result might be incomplete. (display, show.rio)

 

Does anybody know how to get rid of this warning message?

Please use plain text.
Honored Contributor
Piku
Posts: 3,944
Registered: ‎06-17-2010
Message 2 of 6 (452 Views)

Re: ir_max_relevant_answers:500, Warning displayed: SQL portion ... truncated at 32767 records.

Hi,

from One of KD,

Summary: How is this parameter applied when performing a search

The parameter 'ir_sql_limit' can be applied to the sm.ini or to individual servlets, but how does it affect IR queries?

The default value of this parameter, if it is not declared, is '32767', does this define the number of results that can be displayed in total, or is it the number of results returned from the SQL database before the IR filters are applied?

When is this parameter invoked and how does it affect the search results?

Solution ::
The 'ir_sql_limit' parameter is only applied where a query includes a field that is keyed through the SQL database and through the IR keys. It is not applied where the query is based entirely on SQL indexes, or entirely on IR indexes.

For example, this parameter is applied when performing a search against the incident number and incident description against the probsummary table: 'number#"IM10" and brief.description#"printer failed"'

In this example the SQL portion of the query is applied first, and then the IR query is applied to the record set returned.

Therefore, if the SQL record set is greater than the 32767 default value, a message will be displayed: 'SQL portion of combined query has been truncated at 32767 records. Your IR query result might be incomplete.'

This value can be amended, but the default value is the lowest value that will be accepted. Therefore, for this parameter, valid entries are greater than 32767.

If this parameter is amended, there may be some performance degradation, although provided the SQL index is created and is efficient, this should not be significant unless many thousands of records are being returned.


hth,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Assign Kudo, if found post useful and mark it accepted if solves the issue.
http://h30499.www3.hp.com/t5/Tips-and-Tricks/Kudos-what-where-how-and-why/m-p/5677925#U5677925
Please use plain text.
HP Expert
lisajo
Posts: 480
Registered: ‎02-15-2010
Message 3 of 6 (416 Views)

Re: ir_max_relevant_answers:500, Warning displayed: SQL portion ... truncated at 32767 records.

Hi

This is a known issue

and

 new parameter ir_sql_limit:<nnn> which determines how many records shall be fetched from SQL before IR starts its internal search. Also added a warning which is sent to the end user, if the system detects that the record returned by SQL have been truncated

 

Thanks

Lisa

"HP Support
If you find that this post or any post resolves your issue, please make sure to mark it as an accepted solution."
Please use plain text.
Honored Contributor
VVV_3
Posts: 581
Registered: ‎08-01-2008
Message 4 of 6 (391 Views)

Re: ir_max_relevant_answers:500, Warning displayed: SQL portion ... truncated at 32767 records.

thanks for your answers!
The questions are:
- how to get rid of this warning?
- what is the best practice about this new parameter <ir_sql_limit>, which value to set?
Please use plain text.
Valued Contributor
SteveO_1
Posts: 63
Registered: ‎04-06-2010
Message 5 of 6 (382 Views)

Re: ir_max_relevant_answers:500, Warning displayed: SQL portion ... truncated at 32767 records.

Hi,

 

You can get rid of this warning by setting the ir_sql_limit parameter to a value that is too high for the search results ever to exceed this number.

 

For example, if you had 60,000 records in the database you could set the ir_sql_limit parameter value to 90,000, so you will not get this message, unless the SQL portion of the query returns over 90,000 records.

 

However, you should be aware of the potential performance issues with dealing with large datasets, returning this many records and then performing an IR search against these may result in slow performance.

 

Essentially what you need to do is to determine how many records you want to be allowed to be searched in the SQL portion of the query, and set that is the ir-sql_limit parameter.

 

It is a tuning issue related to the queries issued, the records available in your database, number of users, etc. Unfortunately there is no easy answer.

 

Also, there is no established best practice because this varies from implementation to implementation. If you set the value to allow all of your records to be searched, you will prevent the message appearing, you just need to be aware of the potential consequences.

 

Regards,

 

SteveO

Please use plain text.
Honored Contributor
VVV_3
Posts: 581
Registered: ‎08-01-2008
Message 6 of 6 (353 Views)

Re: ir_max_relevant_answers:500, Warning displayed: SQL portion ... truncated at 32767 records.

Hello!

in SM 9.32 Help:

Possible values:
The maximum number of records to be fetched from the RDBMS for a combined IR and SQL query. A value of 0 (zero) means there is no limit.

so, we've set ir_sql_limit:0 in sm.ini and now all is OK.

Thanks everyone for answers.
Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation