ADO.NET OracleCommandBuilder performance issue

Question

I'm currently investigating some performance issues in a .NET/Oracle application.

I've run an oracle trace file and I have noticed that the following query is being called a lot and is using a lot of resources:

select ac.constraint_name key_name, acc.column_name key_col,1 
from all_cons_columns acc, all_constraints ac 
where acc.owner = ac.owner 
and acc.constraint_name = ac.constraint_name 
and acc.table_name = ac.table_name 
and ac.constraint_type = 'P' 
and ac.owner = user 
and ac.table_name = :TableName 
order by acc.constraint_name

I have determined that this query is not being called from the application code, could it be generated by ADO.NET? The application uses OracleCommandBuilders.

Answer 1

Yes, I expect is is OracleCommandBuilders that is doing that: it is finding out which columns form the primary key for the given table. It needs to do this (and other metadata selects) to generate SQL statements for your application.