Forum OpenACS Development: Re: script that moves sql from tcl to xql files

5: Re: script that moves sql from tcl to xql files (response to 4)

Posted by Benjamin Brink on 09/17/14 12:59 AM

Thank you, Gustaf. Very interesting.

Are there performance benefits in moving sql from *.tcl to *postgresql.xql?

I have a habit of writing sql in the .tcl files during development phase, as you state, to make the code more legible.

If there is a performance benefit with sql in *postgresql.xql, I'll write a little script to move the sql once the code has matured.

6: Re: script that moves sql from tcl to xql files (response to 5)

Posted by Gustaf Neumann on 09/17/14 02:33 AM

The performance impact of using .xql files is very little. The decision of taking the generic .xql vs. db-specific .xql file happens at load time (during startup). Then the queries are stored in the nsv OACS_FULLQUERIES (see [1]). At runtime the performance difference is most likely hard to measure, since for every query (e.g. db_string), an nsv_exists is performed [2], for queries with .xql the query is fetched from there via nsv_get (when i look at [2], i can't see the reason for the actual 2 nsv_get operations. Under high load the mutex competition for the nsv might kick in, but on most sites, not an issue.

-g

[1] https://openacs.org/api-doc/proc-view?proc=%3a%3adb_qd_internal_store_cache&source_p=1
[1] https://openacs.org/api-doc/proc-view?proc=%3a%3adb_qd_internal_get_cache&source_p=1

7: Re: script that moves sql from tcl to xql files (response to 6)

Posted by Brian Fenton on 09/17/14 10:22 AM

Hi Benjamin

I do recall one situation where my code only worked when I moved the SQL to the XQL file (normally like you I find it much easier to leave the SQL in the TCL file during development) - I can't find it right now but I think this was to do with using pagination.

Another advantage to using XQL files is that you can reuse a query (using db_qd_replace_sql https://openacs.org/api-doc/proc-view?proc=%3a%3adb_qd_replace_sql%20&source_p=1 but it's not always as straightforward as that!). Although in practice, if a query merits being reused, it probably also merits being put in a library TCL proc.

best
Brian

8: Re: script that moves sql from tcl to xql files (response to 7)

Posted by Gustaf Neumann on 09/17/14 10:50 AM

concerning "code did not work when query was in tcl but worked in xql": if it is so, this would be a clear bug. if you are able to reproduce it, please enter an entry to the bug-tracker.

A nasty problem might be the following: when the query specified both in the .tcl and .xql file, then the query in xql has higher precedence. Therefore it could be completely misleading to read and maybe modify the query in the tcl code (changes have no effect), but modifying the query in xql "works".

The actual reuse of sql queries in different procs is very little, at least in the most common 80 packages. i observed the rather opposite that the same sql query is repeated in multiple .xql files: i remember this since i had to fix the same problems multiple times.

9: Re: script that moves sql from tcl to xql files (response to 7)

Posted by Benjamin Brink on 09/17/14 01:45 PM

Hi Brian,

There were some esoteric seeming cases while working with the ecommerce package that seemed to break as you suggest, but the problems ended up being some thing else. For example some were due to having more than one query using the same reference, or a cache'd query using an early coded caching technique before OpenACS had a fully standardized one. A particularly difficult case involved a poorly nested curly brace in a long tcl file that defined scheduled procs; Another involved an xql definition in a www dir; In both cases, I didn't realize that code there are only re-interpreted on server restart.

I agree, avoid duplication by making a proc out of it. Also avoiding functions and procs in sql help keep the system with a consistent interpretation of data and timing.

cheers,
Ben