Thread: stored procedure slower when called through c client than pgadmin
We have a stored procedure that takes two integers as parameters, a start date and a stop date.
It runs some calculations on a table and updates some columns in the same table.
The table being updated is partitioned.
The server version is 9.1.8.
When we run this stored procedure through pgadmin it runs in around 10 – 15 minutes.
When we run the same stored procedure through our c++ program that is using libpq it takes 1 hour.
Anyone have some insight on what could be the difference?
Thanks,
Brad
Hello 2013/3/8 Bradley Russell <bradley.russell@npcinternational.com>: > We have a stored procedure that takes two integers as parameters, a start > date and a stop date. > > > > It runs some calculations on a table and updates some columns in the same > table. > > > > The table being updated is partitioned. > > > > The server version is 9.1.8. > > > > When we run this stored procedure through pgadmin it runs in around 10 – 15 > minutes. > > > > When we run the same stored procedure through our c++ program that is using > libpq it takes 1 hour. > > > > Anyone have some insight on what could be the difference? > It is really strange - I never seen this issue. Can you send a test example or can you send source code? Regards Pavel Stehule > > > Thanks, > Brad
I will see if I can strip the code down more to a smaller sample. It is part of a larger project. Though I have debuggedit to confirm on that connection the only command being sent is the select of the stored procedure. The strange thing is the time is actually spent running on the server. We can see the query running in server status onthe server for the entire hour. We have also tried it in different orders so we don't believe it is a caching effect. Thanks, Bradley Russell Programmer NPC International -----Original Message----- From: Pavel Stehule [mailto:pavel.stehule@gmail.com] Sent: Friday, March 08, 2013 10:56 AM To: Bradley Russell Cc: PostgreSQL General (pgsql-general@postgresql.org) Subject: Re: [GENERAL] stored procedure slower when called through c client than pgadmin Hello 2013/3/8 Bradley Russell <bradley.russell@npcinternational.com>: > We have a stored procedure that takes two integers as parameters, a start > date and a stop date. > > > > It runs some calculations on a table and updates some columns in the same > table. > > > > The table being updated is partitioned. > > > > The server version is 9.1.8. > > > > When we run this stored procedure through pgadmin it runs in around 10 – 15 > minutes. > > > > When we run the same stored procedure through our c++ program that is using > libpq it takes 1 hour. > > > > Anyone have some insight on what could be the difference? > It is really strange - I never seen this issue. Can you send a test example or can you send source code? Regards Pavel Stehule > > > Thanks, > Brad
2013/3/8 Bradley Russell <bradley.russell@npcinternational.com>: > I will see if I can strip the code down more to a smaller sample. It is part of a larger project. Though I have debuggedit to confirm on that connection the only command being sent is the select of the stored procedure. > > The strange thing is the time is actually spent running on the server. We can see the query running in server status onthe server for the entire hour. > > We have also tried it in different orders so we don't believe it is a caching effect. please, check if you use same database from both environments. Usually, source of this kind of strange issues are hidden different environments. Next possibility is different configuration related to different users ??? Maybe ... Pavel > > Thanks, > Bradley Russell > Programmer > NPC International > > -----Original Message----- > From: Pavel Stehule [mailto:pavel.stehule@gmail.com] > Sent: Friday, March 08, 2013 10:56 AM > To: Bradley Russell > Cc: PostgreSQL General (pgsql-general@postgresql.org) > Subject: Re: [GENERAL] stored procedure slower when called through c client than pgadmin > > Hello > > 2013/3/8 Bradley Russell <bradley.russell@npcinternational.com>: >> We have a stored procedure that takes two integers as parameters, a start >> date and a stop date. >> >> >> >> It runs some calculations on a table and updates some columns in the same >> table. >> >> >> >> The table being updated is partitioned. >> >> >> >> The server version is 9.1.8. >> >> >> >> When we run this stored procedure through pgadmin it runs in around 10 – 15 >> minutes. >> >> >> >> When we run the same stored procedure through our c++ program that is using >> libpq it takes 1 hour. >> >> >> >> Anyone have some insight on what could be the difference? >> > > It is really strange - I never seen this issue. Can you send a test > example or can you send source code? > > Regards > > Pavel Stehule > >> >> >> Thanks, >> Brad >
So I have checked all that stuff several times but what I think I have narrowed it down to now is I can get the 15 minuterun time running the program on my development desktop but when I put the program on my production server to run it,I get the 1 hour runtime. My development box is xp and the production server is server 2003. DB server is Ubuntu 12.04. I'm testing this out some more but still don't know how the host box could affect the runtime of the stored procedure onthe server. One thing I wondered is the stored procedure is returning an error code 1 or 0. If there was a delay in the client pickingup the return value from the server would it appear the stored procedure was still running while it waited for theclient to grab the return value? Thanks, Brad -----Original Message----- From: Pavel Stehule [mailto:pavel.stehule@gmail.com] Sent: Friday, March 08, 2013 11:09 AM To: Bradley Russell Cc: PostgreSQL General (pgsql-general@postgresql.org) Subject: Re: [GENERAL] stored procedure slower when called through c client than pgadmin 2013/3/8 Bradley Russell <bradley.russell@npcinternational.com>: > I will see if I can strip the code down more to a smaller sample. It is part of a larger project. Though I have debuggedit to confirm on that connection the only command being sent is the select of the stored procedure. > > The strange thing is the time is actually spent running on the server. We can see the query running in server status onthe server for the entire hour. > > We have also tried it in different orders so we don't believe it is a caching effect. please, check if you use same database from both environments. Usually, source of this kind of strange issues are hidden different environments. Next possibility is different configuration related to different users ??? Maybe ... Pavel > > Thanks, > Bradley Russell > Programmer > NPC International > > -----Original Message----- > From: Pavel Stehule [mailto:pavel.stehule@gmail.com] > Sent: Friday, March 08, 2013 10:56 AM > To: Bradley Russell > Cc: PostgreSQL General (pgsql-general@postgresql.org) > Subject: Re: [GENERAL] stored procedure slower when called through c client than pgadmin > > Hello > > 2013/3/8 Bradley Russell <bradley.russell@npcinternational.com>: >> We have a stored procedure that takes two integers as parameters, a start >> date and a stop date. >> >> >> >> It runs some calculations on a table and updates some columns in the same >> table. >> >> >> >> The table being updated is partitioned. >> >> >> >> The server version is 9.1.8. >> >> >> >> When we run this stored procedure through pgadmin it runs in around 10 – 15 >> minutes. >> >> >> >> When we run the same stored procedure through our c++ program that is using >> libpq it takes 1 hour. >> >> >> >> Anyone have some insight on what could be the difference? >> > > It is really strange - I never seen this issue. Can you send a test > example or can you send source code? > > Regards > > Pavel Stehule > >> >> >> Thanks, >> Brad >