SQL question

[MCSchlemme]

On the Page by SQL example 2, the bottom tool bar contains a field allowing you to specify the number of rows to display/retrieve (typically 25).

Is that number (25) used in building the SQL statement, specifically used in the FETCH FIRST 25 ROWS ONLY clause or is that something I should build into the statement before handing the statement off to the vvOut_execSlqToJson procedure?

Thanks...

[sean.lanktree]

In this case, there is no need to handle it yourself before passing off to vvOut_execSqlToJson. Setting "vvOut.applySorters = '1'" causes it to be automatically applied.

[MCSchlemme]

When I add the applysorters=1, my program bombs because the dump tells me that the applysorters added an order by to my sql statement. I build my own order by, so sql doesn't like 2 order by clauses.

My orginal question had more to do with performance than sorting. Adding the FETCH FIRST 25 ROWS ONLY optimizes the return of the data set. Apparently your programs do not add the FETCH FIRST X ROWS ONLY clause or otherwise the SQL processor would puke because of the duplicate FETCH clauses.

I looked in the VVOUT source and I don't see the FECTH FIRST x ROWS ONLY clause.

Is this a valuable option to add to Valence or should I just continue to do in on my own?

Thanks...

[sean.lanktree]

Sorry, I have caused some confusion. I meant to say if you use "vvout.applyPaging = '1'" then vvOut_execSqlToJson will handle the number of results returned.

To answer your question on "fetch first xx rows", you are correct in that vvOut does not automatically append this. I will add this to our internal to-do list as I agree this would be beneficial.

[MCSchlemme]

Follow up

Sean,
Please see attached PDF's relating to the FETCH FIRST conversation we had.

Thanks...

[sean.lanktree]

Thank you for this....we will definitely incorporate this (or some form of it) into the base source. As you mentioned, we will have to test for different scenarios so it may not make it into the upcoming maintenance release but certainly into the one after that. Love to hear that it improves performance...what kind of performance gain were you observing in your tests?

[MCSchlemme]

With the FETCH, based on SQLs retrieving 100, 1700, 2800 and 5300 records, not measured scientifically, I'd say minumum of 50% increase in speed.

Of course, you can be more scientific and tout that number in the next release notes!

Thanks for your help!

[MCSchlemme]

Heads up

After more testing, I found the FETCH I implemented broke forward paging.

When clicking the > for the next page, I get no records.

I tried debugging vvOut, however I'm couldn't find the problem.

I think the problem is in the SQL statement "Exec SQL fetch relative :startFrom from sqlExtract;"

I commented out the code I implemented and everything is back to normal.

I'll leave this one to you guys!

[sean.lanktree]

I think this will do the trick...

if not countEmUp and vvOut.maxResults <> 0;                                   
  stmt = %trim(stmt) + ' fetch first ' +                                      
         %char((vvOut.maxResults+vvOut.startFrom)) + ' rows only';            
endif;

[MCSchlemme]

You're awesome!

What's it like being a rock star?

Thanks...