SCM

[#1010780] Reduce use of utility queries for function calls (CommandType.StoredProcedure)

View Trackers | Patches | Download .csv | Monitor

Date:
2010-03-19 04:32
Priority:
3
State:
Closed
Submitted by:
Noah Misch (nmisch)
Assigned to:
Nobody (None)
Category:
Group:
Resolution:
Accepted
 
Summary:
Reduce use of utility queries for function calls (CommandType.StoredProcedure)

Detailed description
I looked into sources of overhead when calling a trivial function by way of CommandType.StoredProcedure. In my setup, the largest time sink was network latency to make three queries behind the scenes, in these NpgslCommand methods:

CheckFunctionReturn("refcursor")
CheckFunctionHasOutParameters
CheckFunctionNeedsColumnDefinitionList

I have attached two patches to assuage this burden. The first patch eliminates the CheckFunctionReturn query by instead looking at the result set to determine whether to invoke that logic. This produces slightly different behavior in a corner case, namely a function with return type "record" that happens to return a single column of type "refcursor"; we will now process those cursors as though the function had a declared "refcursor" return type.

The second patch merges the logic of CheckFunctionHasOutParameters into CheckFunctionNeedsColumnDefinitionList, cutting out another query. Since we need at least one Output or InputOutput parameter to construct a syntactically valid column definition list, we skip this query entirely when we have no such parameters. This patch also fixes a bug and includes a new test case: the existing code does not recognize a function like "create function testreturnsetofrecord() returns setof record as 'values (8,9), (6,7)' language 'sql'" as requiring a column definition list, but it does require one.

Overall, we go from three behind-the-scenes queries for each function-call NpgsqlCommand to zero or one, depending on whether the NpgsqlCommand has Output or InputOutput parameters. This accelerated my particular test case by 1/3.

003-implicit-refcursor.patch applies atop my previous npgsql-timeout-param.patch, and 004-simplify-col-def.patch applies atop 003-implicit-refcursor.patch. The patches are logically independent, and I can rebase them upon request.

Followup

Message
Date: 2010-03-22 17:28
Sender: Francisco Figueiredo jr.


Hmmmm, that's because I edited the files in OSX.

I'll fix that later. This problem is spread in all the project files. :(

Nice to see it is working ok.

I'll close the bug then.

Thanks for your help.
Date: 2010-03-22 17:27
Sender: Francisco Figueiredo jr.


Hmmmm, that's because I edited the files in OSX.

I'll fix that later. This problem is spread in all the project files. :(

Nice to see it is working ok.

I'll close the bug then.

Thanks for your help.
Date: 2010-03-22 10:34
Sender: Noah Misch

Thank you. Applying the patch did somehow introduce a few
LF-only lines to the mostly-CRLF
src/Npgsql/NpgsqlCommand.cs, but the functional behavior
looks good.
Date: 2010-03-22 03:01
Sender: Francisco Figueiredo jr.


Patches applied!

Thank you very much!

Please, give it a try and let me know if there is any problem.

Clever idea of checking the result type of refcursor functions. I don't know I didn't think about that!

Nice Catch!
Date: 2010-03-19 18:14
Sender: Francisco Figueiredo jr.


Great!

I'm gonna check them out.

Thanks in advance.

Changes:

Field Old Value Date By
status_idOpen2010-03-22 17:28fxjr
ResolutionNone2010-03-22 17:28fxjr
close_date2010-03-22 17:282010-03-22 17:28fxjr
File Added460: 004-simplify-col-def.patch2010-03-19 04:33nmisch
File Added459: 003-implicit-refcursor.patch2010-03-19 04:32nmisch
Powered By FusionForge