12-29-2013 10:03 PM
We've added parameter to sm.ini file:
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?
Solved! Go to Solution.
12-30-2013 12:27 AM
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?
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.
Assign Kudo, if found post useful and mark it accepted if solves the issue.
01-06-2014 08:20 AM
This is a known issue
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
If you find that this post or any post resolves your issue, please make sure to mark it as an accepted solution."
01-12-2014 09:01 PM
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?
01-13-2014 01:00 AM
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.
01-19-2014 08:41 PM
in SM 9.32 Help:
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.