Long query against huge table returns partial result then times out

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Long query against huge table returns partial result then times out

Post by barry.marcus »

Without getting specific (yet) I have run into an issue with v7 of Texis. Following the upgrade I've run *many* queries to verify that all is still well in our code. For the most part everything looks identical in the results. But there is one query (at least so far) that is causing us trouble. It is long (3,220 characters) and it returns many hits (~21K rows when run against our production table that contains about 4.1M rows.) When I run the query using v6 the search takes about 4 minutes, but completes, and all 21K row ids are returned. Against v7 the first three thousand or so are returned and then nothing else. It seems to die. Eventually the texis process times out. (I guess it times out, although that's strange because the first line of code is <vxcp timeout -1>.)

Is there something new in v7 that I may be overlooking that would cause texis to choke on long query strings, or very large resultsets, or long query times?

Thanks
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

Long query against huge table returns partial result then times out

Post by Kai »

Have you checked vortex.log for any errors? Make sure logging isn't turned off via <putmsg log off>, <vxcp putmsg log off>, or a <putmsg> function (add <vxcp putmsg log on> in the latter case to also log).

Offhand I can't think of any changes that would cause v7 to ABEND or exit prematurely, but there are some behavior changes that could cause different results. The most prominent is the IN operator changes, especially with strlst fields: v7 requires *all* left-side values to be present on the right, not just *any* as with v6 and earlier.

There are other changes in v7 however; see http://www.thunderstone.com/site/vortex ... s_and.html for details, and see if any might apply to your scripts.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Long query against huge table returns partial result then times out

Post by barry.marcus »

There is no error in the log. There is no indication of ABEND in the log. It's not timing out because I am using <vxcp timeout -1> at the start of the script. In fact, the script does not seem to end, per se. It just stops doing anything at all! It hangs right in the middle of the <sql>...</sql> loop, and always at the same spot in the output result set. The texis process remains in memory, albeit at 25% CPU usage (which is unusual) and with no change in the memory usage once it hangs. There is no use of <putmsg> or <vxcp putmsg> at all in the test script. Nor is there a <putmsg> function. Moreover, as far as I can tell the offending query is not using the IN operator at all. It is entirely (more or less) of the form:

select PATN_WKU rtnVal from PATN where (PATN_TTL\ABST_PAx\CLMS_PAx like '<mmexpression1>' and PATN_TTL\ABST_PAx\CLMS_PAx like '<mmexpression2>' and ...) and ((PATN_ISD >= '19890103') and (PATN_ISD <= '20130305'))

where <mmexpressionN> are our metamorph expressions. There is an inverted metamorph index on the field PATN_TTL\ABST_PAx\CLMS_PAx, and a standard index on the field PATN_ISD. The logical grouping of "<field> LIKE <mmexpression>" phrases varies, depending on the intended logic. That is, some are ANDed together, some are ORed together, there are groupings of phrases with parens, etc. But that is all pretty straightforward, well-tested stuff that has been working fine for years.

And like I said, every query I've tested so far runs fine on both our v6 and v7 installations, except this one. The obvious difference between this query and the others is that it returns MANY more hits than all the others. Probably by a factor ten.

The actual call to <sql> in the test script simply looks like:

<sql row $theQuery>
$rtnVal<br>
<flush>
</sql>

So if it is somehow a memory issue it doesn't seem like it's the fault of the script itself. And as I said, there is no issue whatsoever on the v6 servers. I have upgraded to v7 on two of our servers, and this is the behavior on both of those machines.

Strange.
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

Long query against huge table returns partial result then times out

Post by Kai »

Open a tech support ticket with the full query, table size and row count, and we'll see if we can resolve it.
Post Reply