Oracle查询速度慢（或失败）的.NET应用程序，但很快从SQL开发人员 [英] Oracle query is slow (or fails) from .NET app but is fast from SQL Developer

问题描述

我们使用ODP.NET对Oracle数据库执行查询，通常它工作正常。有一个特定的数据库，并在该数据库的特定视图，虽然，我们不能完全从.NET的查询。例如：

We use ODP.NET to perform queries on Oracle databases, and normally it works fine. There is a particular database, and a particular view in that database, though, that we just can't complete a query on from .NET. For example:

SELECT some_varchar_field FROM the_view WHERE ROWNUM < 5;

如果我从Oracle SQL Developer中执行此查询，它完成在不到一秒钟。如果我使用ODP.NET做一个相同的查询，从我们的.NET应用程序，它挂起并最终产生ORA-03135：连接失去联系的错误。我认为它限制在只有几行消除的可能性，这是因为FETCHSIZE问题。

If I execute this query from within Oracle SQL developer, it finishes in less than a second. If I do an identical query from our .NET application using ODP.NET, it hangs and eventually produces an "ORA-03135: connection lost contact" error. I think that limiting it to just a few rows eliminates the possibility that it is as FetchSize issue.

有其他的疑问，我可以成功执行，但他们是从我们的程序慢不是从SQL开发人员。再次，我知道SQL Developer中只获取数据的第50行开始，但我认为ROWNUM条件发生了的方程。

There are other queries I can execute successfully, but they are slower from our program than from SQL Developer. Again, I realize SQL Developer only gets data for the first 50 rows initially, but I think the ROWNUM condition takes that out of the equation.

什么可能是关于连接的不同或命令的Oracle SQL Developer使用VS一个我们的应用程序使用，将导致在速度上的差异？

What might be different about the connection or command that Oracle SQL Developer is using vs the one our application is using that would cause a difference in speed?

不幸的是，我没有访问服务器（而不是反对它运行的Oracle查询）。

Unfortunately, I do not have access to the server (other than to run Oracle queries against it).

感谢你。

更新：我曾尝试与微软的Oracle提供相同的查询，并很快执行。不幸的是，供应商是pcated所以这次去$ P $是不是一个长期的解决方案。

UPDATE: I have tried the same query with Microsoft's Oracle provider and it executes very quickly. Unfortunately, that provider is deprecated so this is not a long term solution.

推荐答案

这一点关系都没有的ODP.NET提供商。问题是，我们用它来创建我们的连接库总是在执行做任何事情之前，下面的语句（当然，不使用的Oracle SQL Developer，和我没有当我尝试微软提供者使用）：

It had nothing to do with the ODP.NET provider. The problem was that the library we use to create connections for us (which, of course, is not used by Oracle SQL Developer, and which I did not use when I tried the Microsoft provider) was always executing the following statements before doing anything:

ALTER SESSION SET NLS_COMP = LINGUISTIC
ALTER SESSION SET NLS_SORT = BINARY_CI

这些使得甲骨文不区分大小写。但是，他们也使所有的常规指标无用。因为我们是从一个视图查询，它已经订货内置的。因为我们没有自己的数据库，我们不能让索引语言来解决性能问题。

These make Oracle case-insensitive. But, they also render all conventional indexes useless. Because we were querying from a View, it had ordering built in. And because we don't own the database, we can't make the indexes linguistic to fix the performance problem.

提供了一种方法，在这种（罕见）情况下解决了这一问题不会执行这些语句。

Providing a way to not execute those statements in this (rare) scenario fixed the problem.

这篇关于Oracle查询速度慢（或失败）的.NET应用程序，但很快从SQL开发人员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！