Stored procedure Slo

[Priya.Sunil]

Hi
I have a db2 9.1 SP whihc passes where statement as the input paramter
from client and then concantenate it with a select statment ,get the
data in cursor and then return the cursor to the client.
It tis taking 20 secs to retunr data of 800 recs. In the same plcae
instead of SP if I use query directly from the client code it runs in
less than a second.How ever Architecture doesn't let me use query
inside my client app.
Please help me out in making this SP faster. May be I missed some
settings:(

[Serge Rielau]

Pr*********@gmail.com wrote:

Hi
I have a db2 9.1 SP whihc passes where statement as the input paramter
from client and then concantenate it with a select statment ,get the
data in cursor and then return the cursor to the client.
It tis taking 20 secs to retunr data of 800 recs. In the same plcae
instead of SP if I use query directly from the client code it runs in
less than a second.How ever Architecture doesn't let me use query
inside my client app.
Please help me out in making this SP faster. May be I missed some
settings:(

I think we need to get more context than that.
Are you using parameter markers (?). How complex is the query (does it
take 20seconds to compile?)
Ideally please post teh relevant (anonymized) part of the proc and a
sample where clause as well as you comparison query.

Note that passing WHERE clauses from the client to the client to the
server is risky business. It opens you up to SQL Injection attacks.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab