Re: ONE MORE PERFORMANCE ISSUE WITH POSTGRES

Hi,

My machine is a windowsNT machine.

In my stored procedure I am using some statements like
1.select count(*) into cust_count from os_customer_view;
2.select to_number(substring(os_crm_exchange_unit_id, 4), 999999999999) into id
from os_unit_view offset row;

SO If I call a query which is using this stored procedure than execution is very slow.

Is there any way to fine tune the statements inside the stored procedure.I have
created index on os_crm_exchange_unit_id field.

Like in ORACLE if you use count(*),sub-string,to-number then indexing won't work is
there something like this is there in postgres also.Is yes then what is the
appropriate solution for this.



Shridhar Daithankar wrote:

> On 29 Oct 2002 at 16:42, Savita wrote:
> > I just wanted to know is it possible to improve the performance of a query if
> > the query is calling any stored procedure in it.
>
> Generally if you are making 3-4 calls for select/update/insert, a stored
> procedure works much better compared to separate calls to database because
> communication overhead is only once and database probably pre-parse-pre-plan
> the queries in it. So yes, it does improve performance..
>
>
> > Because when I run this query it takes 100% CPU usage and other applications are
> > hanging.
>
> What's the query?  Could you elaborate more on that?
>
> And other applications are hanging is rediculous behaviour unless it's windows.
> On linux, any 100% CPU chewing task should not block any other task. At least
> 2.4.19 onwards, same goes for other unices.. Windows is a different story.(My
> mail client, pegasus  maxes out CPU, if it can not reach SMTP host for some
> reason. This is XP/SP1.. Go figure)
>
> Bye
>  Shridhar
>
> --
> Dijkstra probably hates me(Linus Torvalds, in kernel/sched.c)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------