Thread: function call
Hi list,
I have a SET returning function (defined as RETURNS TABLE), it takes 2 parameters and always returns one row with 3 columns. Now when I run it from pgAdmin it takes around 5 seconds but when I run it from the application its around 3 minutes (same parameters of course). It shows up in the postgres' status server and odbc log right away so I believe the applications has nothing to do with it. Where should I start looking ?
I'm running postgres 9.1
regards
mk
mk
On 08/05/2014 04:28 AM, Marcin Krawczyk wrote: > Hi list, > > I have a SET returning function (defined as RETURNS TABLE), it takes 2 > parameters and always returns one row with 3 columns. Now when I run it > from pgAdmin it takes around 5 seconds but when I run it from the > application its around 3 minutes (same parameters of course). It shows > up in the postgres' status server and odbc log right away so I believe > the applications has nothing to do with it. Where should I start looking ? More information would be helpful. For now I am guessing in your application you are using ODBC to connect to the server, correct? This is an additional step not found in the pgAdmin route which may be a clue. More questions: 1) What does the function do? 2) Where are the database and pgAdmin and the application in relation to each other? In other words are they on the same machine or are they going across a network? 3) What is the application? > > I'm running postgres 9.1 > > > regards > mk -- Adrian Klaver adrian.klaver@aklaver.com
On 08/05/2014 04:28 AM, Marcin Krawczyk wrote: > Hi list, > > I have a SET returning function (defined as RETURNS TABLE), it takes 2 > parameters and always returns one row with 3 columns. Now when I run it > from pgAdmin it takes around 5 seconds but when I run it from the > application its around 3 minutes (same parameters of course). It shows > up in the postgres' status server and odbc log right away so I believe > the applications has nothing to do with it. Where should I start looking ? Just had another thought. I found in the past that ODBC logging can slow things down considerably. What happens if you turn off the ODBC logging? > > I'm running postgres 9.1 > > > regards > mk -- Adrian Klaver adrian.klaver@aklaver.com
For now I am guessing in your application you are using ODBC to connect to the server, correct?
Correct.
1) What does the function do?
It calculates a lot of things and issues documents. I have much more complicated functions and never experienced such problems with this application.
2) Where are the database and pgAdmin and the application in relation to each other? In other words are they on the same machine or are they going across a network?
The application and the pgAdmin are on the same client, connecting to server through LAN.
3) What is the application?
It's an ERP system.
4) What happens if you turn off the ODBC logging?
It does not change anything.
It's not ODBC, I've just tested with simple C# through the same odbc source and the function takes 4 seconds as well.
regards
mk
mk
2014-08-05 15:01 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 08/05/2014 04:28 AM, Marcin Krawczyk wrote:Just had another thought.Hi list,
I have a SET returning function (defined as RETURNS TABLE), it takes 2
parameters and always returns one row with 3 columns. Now when I run it
from pgAdmin it takes around 5 seconds but when I run it from the
application its around 3 minutes (same parameters of course). It shows
up in the postgres' status server and odbc log right away so I believe
the applications has nothing to do with it. Where should I start looking ?
I found in the past that ODBC logging can slow things down considerably.
What happens if you turn off the ODBC logging?--
I'm running postgres 9.1
regards
mk
Adrian Klaver
adrian.klaver@aklaver.com
Wouldn't this issue be an excellent candidate to create a test example and then log a bug with all the details so others can recreate the issue? It's very difficult to divine what the problem is unless you can provide a working example that demonstrates it.
John
John
On 8/6/2014 12:41 AM, Marcin Krawczyk wrote:
It's not ODBC, I've just tested with simple C# through the same odbc source and the function takes 4 seconds as well.regards
mk2014-08-05 15:01 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:On 08/05/2014 04:28 AM, Marcin Krawczyk wrote:Just had another thought.Hi list,
I have a SET returning function (defined as RETURNS TABLE), it takes 2
parameters and always returns one row with 3 columns. Now when I run it
from pgAdmin it takes around 5 seconds but when I run it from the
application its around 3 minutes (same parameters of course). It shows
up in the postgres' status server and odbc log right away so I believe
the applications has nothing to do with it. Where should I start looking ?
I found in the past that ODBC logging can slow things down considerably.
What happens if you turn off the ODBC logging?--
I'm running postgres 9.1
regards
mk
Adrian Klaver
adrian.klaver@aklaver.com
On 08/06/2014 12:41 AM, Marcin Krawczyk wrote: > It's not ODBC, I've just tested with simple C# through the same odbc > source and the function takes 4 seconds as well. So it would seem that the issue is with ERP application returning the information. This probably hinges on what you are defining as returning? In your function description you note that the function issues documents. Is that what you are using to determine that the function has completed? If so, do you return the documents in the pgAdmin case as well? If not, how are measuring success(returning) in the application case? > > > regards > mk > -- Adrian Klaver adrian.klaver@aklaver.com
I've managed to determine that there's a trigger dependency tree that slows things down. When I DISABLE one of them the function takes under 30 seconds, still more than from pgAdmin but much quicker than before.
regards
mk
mk
2014-08-06 15:58 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 08/06/2014 12:41 AM, Marcin Krawczyk wrote:So it would seem that the issue is with ERP application returning the information.It's not ODBC, I've just tested with simple C# through the same odbc
source and the function takes 4 seconds as well.
This probably hinges on what you are defining as returning?
In your function description you note that the function issues documents.
Is that what you are using to determine that the function has completed?
If so, do you return the documents in the pgAdmin case as well?
If not, how are measuring success(returning) in the application case?
So it would seem that the issue is with ERP application returning the information.
This probably hinges on what you are defining as returning?
In your function description you note that the function issues documents.
Is that what you are using to determine that the function has completed?
The function makes some calculations, then INSERTs and UPDATEs data (the aforementioned documents) then returns 1 row with 3 columns with information on whether there were any errors or how many documents were created and so on.
Marcin Krawczyk-2 wrote >> >> So it would seem that the issue is with ERP application returning the >>> information. >>> >> >> This probably hinges on what you are defining as returning? >> >> In your function description you note that the function issues documents. >> >> Is that what you are using to determine that the function has completed? >> > > The function makes some calculations, then INSERTs and UPDATEs data (the > aforementioned documents) then returns 1 row with 3 columns with > information on whether there were any errors or how many documents were > created and so on. Are you positive the pgAdmin and your application are talking to the same machine? Is there any variation in run-time for the two clients? Can you get both of them to run the same query but using EXPLAIN ANALYZE? David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/function-call-tp5813776p5814428.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 08/11/2014 02:50 AM, Marcin Krawczyk wrote: > I've managed to determine that there's a trigger dependency tree that > slows things down. When I DISABLE one of them the function takes under > 30 seconds, still more than from pgAdmin but much quicker than before. I am with David. I am not sure you are calling the same function in each case. Are you sure you have not overloaded the function name and a difference in search_paths is not causing a different version to be run? > > regards > mk -- Adrian Klaver adrian.klaver@aklaver.com
I wish I was doing that :) that's the first thing I checked.
pozdrowienia
mk
mk
2014-08-11 21:01 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 08/11/2014 02:50 AM, Marcin Krawczyk wrote:I am with David. I am not sure you are calling the same function in each case.I've managed to determine that there's a trigger dependency tree that
slows things down. When I DISABLE one of them the function takes under
30 seconds, still more than from pgAdmin but much quicker than before.
Are you sure you have not overloaded the function name and a difference in search_paths is not causing a different version to be run?
On 08/18/2014 12:57 AM, Marcin Krawczyk wrote: > I wish I was doing that :) that's the first thing I checked. Hmmm. So what happens when you run it in the psql client? Also you say it is only slow when run from your ERP application. Running it from pgAdmin or directly from a C# program is fast. So is there a log for that ERP application that might shed some light on what is going on when the function is run from the application? > > > pozdrowienia > mk > -- Adrian Klaver adrian.klaver@aklaver.com