אייל wrote:

sending it again due to a mistake in the subject. sorry.

Martin, thanks for your response.

my actual application is now served by a mssql server, which has many stored-procedures, some of them are 45kb of TSQL-code, that may return about ten result-sets at once. these procedures are normally executed within about 50msec.

having this in mind... Quite substantial queries if they are composed of 45K ascii text. The performance depends on a lot of factors, so i keep it in mind.

for the sake of our discussion only, lets define : "optimization" : automatically turning queries into query templates. "compilation" : parsing the bulk of statements + deciding for each of the queries which "optimization" should be used (or built - if no suitable "optimization" found). "bandwidth": sending a large bulk of statements from the client to the server.

now i would like ask: dose MonetDB stores "compilation" for each stored-procedure for reuse?

if the answer is "no", then i understand: "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay only once :) if the answer is "yes": "optimization" i pay only once :) "compilation" i pay only once :) "bandwidth" i pay only once :)

the situation today is i cannot use stored-procedure (because they don't return result sets), so it actually dose not meter whether the answer is "yes" or "no": "optimization" i pay only once :) "compilation" i pay many times :( "bandwidth" i pay many times :(

since MonetDB actually support returning multiple result-sets (without stored procedure), and it even support returning a single result set (using stored-function), it looks like adding this feature to stored-procedures as well, is not so much of a work (i'm thinking of contributing this work myself, having your kindly help and general directions). but if MonetDB dose NOT even store a "compilation" for each stored procedure, i guess we are too much far away.. Functions, views and procedures are mostly inlined with the query, because

MonetDB translates SQL statements into a MAL function. The result can be seen with the EXPLAIN statement modifier. http://monetdb.cwi.nl/SQL/Documentation/EXPLAIN-Statement.html This translation is done once for a query, factoring out the constants that drive it. MonetDB will attempt to match new SQL statements against those for which it has a plan. In the case of JDBC coupling, you can use the prepare command to perform this step only once and 'call' the query with the arguments needed. this gives better opportunities for optimization. regards, Martin

note: every time i said "stored-procedure", i actually meant "stored-function" as well.

thanks again.

------------------------------------------------------------------------

------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev

------------------------------------------------------------------------

_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users