question about queries used in the component

question about queries used in the component

Postby DarrenColes » Tue 21 Jun 2011 10:06

While attempting to optimise performance on our oracle servers, two queries keep cropping up as using a significant amount of system resources:

select PIPELINED from sys.user_procedures where object_name = :packname and procedure_name = :procname

select PIPELINED from sys.all_procedures where owner = :schemaname and object_name = :procname and procedure_name is null


Can you please explain how and why these queries are used and if there is anything we can do to reduce the actions that cause these queries to run.

Thanks

Darren Coles
DarrenColes
 
Posts: 47
Joined: Mon 28 Aug 2006 11:07

Postby Shalex » Wed 22 Jun 2011 12:31

These queries are executed by provider in the background when you are:
1) assigning the CommandType property of OracleCommand/OracleDataTable to CommandType.StoredProcedure, and
2) setting function (not strored procedure) name in CommandText, and
3) not creating parameter collection manually (but using cmd.ParameterCheck = true;).

Please create parameter collection manually in this case to avoid executing the mentioned queries by the provider.
Shalex
Devart Team
 
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Postby DarrenColes » Wed 22 Jun 2011 12:44

i had a feeling it might be something like that. I can manually populate the parameters and that should solve my problem.

Cheers

Darren
DarrenColes
 
Posts: 47
Joined: Mon 28 Aug 2006 11:07


Return to dotConnect for Oracle