Pagination issue related to pagelinks

Priti Barapatre · Post by **Priti Barapatre** » Thu Feb 17, 2011 9:41 am

Hi John,

We are facing some issue related to pagination that Thuderstone by default provides with pagelinks. When we apply an additional filter to the query like search based on location, it gives me total number of results which are equal to the query matches against the index without applying the location filter. And when we try to paginate though the page numbers, it then resets the total number of results(numrows) now to the query matches and location matches. We are applying the following query. Could you please suggest what should be the possible reason for the same and how we should solve this issue?

"select person_id, last_name, first_name, middle_initial, preferred_name,email, ohr_id, business_name, dialcomm,folders_flag, $$rank rnk from ( last_name\preferred_name\first_name\sc_first_name likep $search_string ) and active_flag=0 and location like $strLocation"

Thanks in advance.
~Priti

Post by **Kai** » Thu Feb 17, 2011 9:58 am

<pagelinks> depends on an accurate $indexcount value to compute pagination, and $indexcount is only accurate if a single index was used to fully compute the results. (Presumably there is a `... TableName where ...' clause inserted after your `from'.)

While you can unify the LIKEP and the `active_flag' query with a compound Metamorph index (and un-parenthesizing the query), the `like $strLocation' may not be appropriate for such an index; presumably `location' values might be variable-sized and more than 3-7 characters, correct?

In that case, you can do a count(*) to get the accurate row count, then pass that to <pagelinks> so it can paginate correctly:

<sql "select count(*) TotalNum from ... WHERECLAUSE">
</sql>

<sql row max=10 "select person_id, ... from ... WHERECLAUSE">
... print result as usual ...
</sql>


<pagelinks numrows=$TotalNum>

This requires doing the <sql> twice, which is less efficient. If it is possible to make `location' a small fixed-size (e.g. 4 or 8-byte) field, then adding `active_flag' and `location' to a metamorph compound index would make the query fully resolvable by 1 index (assuming no post-processing for the LIKEP user query), and $indexcount would be accurate, and the count(*)/numrows would not be needed.

Post by **John** » Thu Feb 17, 2011 12:47 pm

Assuming you have an index on location the issue should only show up if there are less than maxlinearrows (default 1000) matches for the rest of the query, so you may only need to do the explicit select count(*) if $indexcount is less than 1000.

Priti Barapatre · Post by **Priti Barapatre** » Mon Mar 07, 2011 10:53 am

Hi Kai,

We tried to implement the compound index like

SQL 1>CREATE METAMORPH INVERTED INDEX xpernames_x_temp on person_profile
SQL 2>( last_name\preferred_name\first_name\sc_first_name,active_flag,location);

But when we try to execute the search on front-end it hangs the browser and throws the following error in vortex.log. And hence we are not able to see if compound index would help us resolving the issue or not. Could you please suggest what's going wrong here?

000 2011-03-07 10:36:13 /webinator/SCPersonSearch:385: Vortex (2897) ABEND: signal 11 (SIGSEGV) at ip 0x1001e01e8
000 2011-03-07 10:42:07 /webinator/SCPersonSearch:385: Vortex (6906) ABEND: signal 11 (SIGSEGV) at ip 0x1001e01e8
000 2011-03-07 10:42:17 /webinator/SCPersonSearch

(7230) Terminated (signal 15)
000 2011-03-07 10:43:18 /webinator/SCPersonSearch:385: Vortex (8205) ABEND: signal 11 (SIGSEGV) at ip 0x1001e01e8

Thanks & Regards,
Priti

Post by **mark** » Mon Mar 07, 2011 11:05 am

What's on line 385 of SCPersonSearch?
Are there any related messages just before the ABEND?

Post by **mark** » Mon Mar 07, 2011 11:07 am

Also provide your full texis version printed by
texis -version

Priti Barapatre · Post by **Priti Barapatre** » Mon Mar 07, 2011 11:12 am

At line 385, we have following code, but not sure thats related. But before that we have some function call which actually executes the sql queries.

<if $lstrLayout eq "grid" and $dispFlag eq "true">

We use the following Texis version in our dev environment-

Texis Web Script (Vortex) Copyright (c) 1996-2009 Thunderstone - EPI, Inc.
Commercial Version 5.01.1249359916 20090804 (sparc-sun-solaris2.8-64-64)

Regards,
Priti

Post by **mark** » Mon Mar 07, 2011 2:05 pm

This same script was working before creating the mentioned index? What are the types of all of the
variables involved? last_name, preferred_name, first_name, sc_first_name, active_flag, location, IstrLayout, dispFlag

Can you reproduce the problem in a small script that just does the sql and that if?