Thread: stored procedure slower when called through c client than pgadmin

stored procedure slower when called through c client than pgadmin

From
Bradley Russell
Date:

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

Re: stored procedure slower when called through c client than pgadmin

From
Pavel Stehule
Date:
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


Re: stored procedure slower when called through c client than pgadmin

From
Bradley Russell
Date:
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


Re: stored procedure slower when called through c client than pgadmin

From
Pavel Stehule
Date:
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
>


Re: stored procedure slower when called through c client than pgadmin

From
Bradley Russell
Date:
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
>