使用oracle odbc缓慢的odbc oracle查询

本文介绍了使用oracle odbc缓慢的odbc oracle查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

你好，

我有一个大问题广告，我无法解决它。我们有一台运行oracle 10g 10.1.0版本的服务器

。我们通常使用access作为前端

并连接数据库表以进行数据提取。我们一直在使用

oracle客户端10.1.0.2和它的odbc一段时间没有问题。当我们决定重新连接所有表并保存

密码时，出现了

问题。一些查询突然变得很慢。然后我发现

使用odbc附带的表连接了

oracle的7.3版本。唯一明显的变化是连接字符串可见

在访问中传递鼠标在查询之间改变了什么

像ODBC; DSN = dnsname; DBQ = OdbcName ; ASY = OFF ;; TABLE = Tabl eName"

类似

" ODBC; DSN = dnsname; UID =用户名; DBQ = OdbcName; DBA = W; APA = F; EXC = F; FEN = T ; QTO = T; FRC = 10; FDL = 10; LOB = F; RST = T; BTD = F; BAM = IfAllSuccessful; NUM = NLS; DPM = F; MTS = T; MDI = F; CSR = F; FWC = T; FBS = 64000; TLO = O ;; TABLE = TableN ame

查询为：

SELECT QSHOC00.SHOCPORD， QSHOC00.SHOCITEM，Sum（（[SHOCQTRC] / 100））AS

QtaOk，QGDLN00.GDLNDESC

来自TabellaOrdini INNER JOIN（QSHOC00 INNER JOIN QGDLN00 ON

QSHOC00.SHOCITEM = QGDLN00.GDLNITEM）ON TabellaOrdini.Ordine =

QSHOC00.SHOCPORD

WHERE（（（QSHOC00.SHOCDTIV）< DateValue（" 31/12 / 4712"））和

（（QSHOC00.SHOCDTFV）= DateValue（" 31/12 / 4712"））和（（QGDLN00.GDLNFINI）="

"）AND（（QGDLN00.GDLNLANG）=" I"））

GROUP BY QSHOC00.SHOCPORD，QSHOC00.SHOCITEM，QGDLN00.GDLNDESC，

QSHOC00.SHOCFINI

HAVING（（（QSHOC00.SHOCITEM）<>""）AND（（QSHOC00.SHOCF INI）=" ））;

表格如下：

1）TabellaOrdini：它只包含两个记录并且具有Ordine和Ordine。作为

主键

2）QSHOC00：它包含大约1870000条记录并且具有由字段SOCIETA，SHOCPORD，SHOCFASE组成的复合

主键，SHOCITEM，

SHOCPORD，SHOCFINI，SHOCLRIN

3）QGDLN00 ::它包含大约75000条记录并且有一个复合的

主键由SOCIETA，GDLNITEM，GDLNFINI，GDLNLANG

查询大约需要2分钟才能运行，而且只需几秒钟就可以获得
秒。如果我更改查询，则将两个值设为ordini。在

a where子句再次非常快，所以问题必须在加入

与TabellaOrdini。

最后一件事：我已经也尝试使用客户端10.2.3，但它更改

没有，除非我使用旧的odbc重新连接表然后它运行

一切都很好。

很抱歉很长的解释并提前感谢答案

给出。

hello,
I''ve got a big problem ad i''m not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it''s odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I''ve discovered
that the tables were connected using odbc shipped with version 7.3 of
oracle. the only apparent change is that the connection string visible
in access passing the mouse on the query is changed between something
like "ODBC;DSN=dnsname;DBQ=OdbcName;ASY=OFF;;TABLE=Tabl eName" to
something like
"ODBC;DSN=dnsname;UID=username;DBQ=OdbcName ;DBA=W;APA=F;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=F ;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS =T;MDI=F;CSR=F;FWC=T;FBS=64000;TLO=O;;TABLE=TableN ame"

The query is:

SELECT QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, Sum(([SHOCQTRC]/100)) AS
QtaOk, QGDLN00.GDLNDESC
FROM TabellaOrdini INNER JOIN (QSHOC00 INNER JOIN QGDLN00 ON
QSHOC00.SHOCITEM = QGDLN00.GDLNITEM) ON TabellaOrdini.Ordine =
QSHOC00.SHOCPORD
WHERE (((QSHOC00.SHOCDTIV)<DateValue("31/12/4712")) AND
((QSHOC00.SHOCDTFV)=DateValue("31/12/4712")) AND ((QGDLN00.GDLNFINI)="
") AND ((QGDLN00.GDLNLANG)="I"))
GROUP BY QSHOC00.SHOCPORD, QSHOC00.SHOCITEM, QGDLN00.GDLNDESC,
QSHOC00.SHOCFINI
HAVING (((QSHOC00.SHOCITEM)<>" ") AND ((QSHOC00.SHOCFINI)=" "));

The tables are the following:
1)TabellaOrdini: it contains only two records and has "Ordine" as
primary key
2) QSHOC00: it contains about 1870000 records and has a composite
primary key composed by fields SOCIETA, SHOCPORD, SHOCFASE, SHOCITEM,
SHOCPORD, SHOCFINI, SHOCLRIN
3) QGDLN00: : it contains about 75000 records and has a composite
primary key composed by fields SOCIETA, GDLNITEM, GDLNFINI, GDLNLANG

The query takes about 2 minutes to run and before it took only few
seconds. If I change the query putting the two values of "ordini" in
a where clause is again very fast so the problem must be in the join
with TabellaOrdini.
A last thing: I''ve also tried to use client 10.2.3 but it changes
nothing, unless I reconnect the tables using old odbc and then it runs
all fine.

Sorry for the long explanation and thanks in advance for the answers
given.

DateValue

使用oracle odbc缓慢的odbc oracle查询

问题描述

推荐答案