query running long time - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

query running long time

query running long time# Database - 数据库

w*h2010-08-03 07:08

1 楼

【以下文字转载自 History 讨论区】
发信人: philphy (海天之间), 信区: History
标题: 距离宪政最近的日子(十一)
发信站: BBS 未名空间站 (Sat Jan 1 23:05:53 2011, 美东)
再次借用赵忠祥老师的电话稿，

l*u2010-08-03 07:08

2 楼

select * from db.f4311 A where
NOT EXISTS (SELECT 1 FROM TESTDTA.F43121 WHERE A.PDDOCO=PRDOCO AND A.PDDCTO=
PRDCTO AND A.PDKCOO=PRKCOO )
and (NOT EXISTS (Select 1 from TESTDTA.f4311 B where A.pddoco = b.pddoco
AND A.pddcto = B.pddcto AND (B.pdtrdj not between @SD and @ED or B.pdnxtr
not in ('999','980') or B.pdlttr not in ('999','980'))))
Tried to run this query and it's been running very long without return. how
can I speed up the query?

c*t2010-08-03 07:08

3 楼

post your execution plan first

PDDCTO=
how

【在 l********u 的大作中提到】

: select * from db.f4311 A where
: NOT EXISTS (SELECT 1 FROM TESTDTA.F43121 WHERE A.PDDOCO=PRDOCO AND A.PDDCTO=
: PRDCTO AND A.PDKCOO=PRKCOO )
: and (NOT EXISTS (Select 1 from TESTDTA.f4311 B where A.pddoco = b.pddoco
: AND A.pddcto = B.pddcto AND (B.pdtrdj not between @SD and @ED or B.pdnxtr
: not in ('999','980') or B.pdlttr not in ('999','980'))))
: Tried to run this query and it's been running very long without return. how
: can I speed up the query?

l*u2010-08-03 07:08

4 楼

I'll ask db peoplefor that. I have very limited tools here.
any other suggestion

l*u2010-08-03 07:08

5 楼

I'll ask db peoplefor that. I have very limited tools here.
any other suggestion

S*02010-08-03 07:08

6 楼

Some suggestions:
1. Use set-based. NOT EXITS is more like row-based, and it kills when the
data volumn is high.
2. Create indexes on the join columns.
3. The approperiate locking hint helps.
4. Try to optimize the execution plan.
SELECT
A.*
FROM
DB.F4311 A (NOLOCK)

LEFT JOIN TESTDTA.F43121 B (NOLOCK)
ON ( (A.PDDOCO = B.PDDOCO)
AND (A. PDDCTO = B. PDDCTO)
AND (A.PDKCOO = B. PRKCOO)
)

LEFT JOIN TESTDTA.f4311 C (NOLOCK)
ON ( (A.pddoco = C.pddoco)
AND (A.pddc

B*g2010-08-03 07:08

7 楼

曾经在组里和老印们讨论过not exists和left outer join在oracle里哪个更好，老印们
找出无数link说left outer join。我说要看具体情况,似乎汤姆支持我的说法
http://apex.oracle.com/pls/otn/f?p=100:11:0::NO::P11_QUESTION_ID:15246310003
46778953。其他数据库不知道，sql server, mysql大牛们出来讨论一下。

【在 S*****0 的大作中提到】

: Some suggestions:
: 1. Use set-based. NOT EXITS is more like row-based, and it kills when the
: data volumn is high.
: 2. Create indexes on the join columns.
: 3. The approperiate locking hint helps.
: 4. Try to optimize the execution plan.
: SELECT
: A.*
: FROM
: DB.F4311 A (NOLOCK)

B*g2010-08-03 07:08

8 楼

1. which database
2. which version
3. index on those tables
4. size of those tables
5. execution plan

PDDCTO=
how

【在 l********u 的大作中提到】

S*02010-08-03 07:08

9 楼

我做SQL Server，在数据量大的情况下，Left Join 表现的要好。不过，performance
tunning，看具体情况,　不是千篇一律。有空，我找找SQL Server是怎么实现这2种
operators的。

印们

【在 B*****g 的大作中提到】

: 曾经在组里和老印们讨论过not exists和left outer join在oracle里哪个更好，老印们
: 找出无数link说left outer join。我说要看具体情况,似乎汤姆支持我的说法
: http://apex.oracle.com/pls/otn/f?p=100:11:0::NO::P11_QUESTION_ID:15246310003
: 46778953。其他数据库不知道，sql server, mysql大牛们出来讨论一下。

a92010-08-03 07:08

10 楼

具体得看数据量的吧？

印们
the

【在 B*****g 的大作中提到】

S*02010-08-03 07:08

11 楼

From this page: http://blog.ragan.com/archives/sqlblog/2005/08/sargable.html. The main difference between JOIN and NOT EXISTS is: JOIN can use an index, but NOT EXISTS prevents the optimizer from using an index.
In the implementation, join is just a two-layer loop: http://en.wikipedia.org/wiki/Nested_loop_join. I guess both JOIN and NOT EXISTS would, eventually, come to the same implementation.

印们

【在 B*****g 的大作中提到】

B*g2010-08-03 07:08

12 楼

答案就是depends

【在 a9 的大作中提到】

: 具体得看数据量的吧？
:
: 印们
: the

B*g2010-08-03 07:08

13 楼

你给的link啥都没有呀，连exists都找不到。另外not exists不让用index也太扯了。
oracle没这问题，俺大部分时间不管用那个execution plan都是一样的。倒是not in常
有问题，不过有一个rumor说oracle 10g R2+，oracle把not in转成not exists

【在 S*****0 的大作中提到】

: From this page: http://blog.ragan.com/archives/sqlblog/2005/08/sargable.html. The main difference between JOIN and NOT EXISTS is: JOIN can use an index, but NOT EXISTS prevents the optimizer from using an index.
: In the implementation, join is just a two-layer loop: http://en.wikipedia.org/wiki/Nested_loop_join. I guess both JOIN and NOT EXISTS would, eventually, come to the same implementation.
:
: 印们

a92010-08-03 07:08

14 楼

我也觉得应该会用到index。像or等用不到index已经是老黄历了吧？

，老

【在 B*****g 的大作中提到】

: 你给的link啥都没有呀，连exists都找不到。另外not exists不让用index也太扯了。
: oracle没这问题，俺大部分时间不管用那个execution plan都是一样的。倒是not in常
: 有问题，不过有一个rumor说oracle 10g R2+，oracle把not in转成not exists

S*02010-08-03 07:08

15 楼

link上就这2点：
1。 Microsoft and Sybase redefined "sargable" to mean 'can be looked up via
the index.'"
2。Sargable predicates can be evaluated on the tuples of a single table.
Unsargable predicates, or join predicates, require one or more joins to be
performed before the predicate can be evaluated
我在另一本SQL Server 2008上也看到这个论点。就是NOT EXITS， EXITS这些是Non-
sargable。
我同意不用index,是不合理的。我没有做过Left Join和NOT EXISTS的execution plan
比较, 但在我的工作中，数据量大的时候, Left Join的优势很明显，所以现在我都用
join了。

【在 B*****g 的大作中提到】

B*g2010-08-03 07:08

16 楼

rule based好像确实问题挺多，cost based这些理论都扯淡了吧

了。
in常

【在 a9 的大作中提到】

: 我也觉得应该会用到index。像or等用不到index已经是老黄历了吧？
:
: ，老

B*g2010-08-03 07:08

17 楼

不用sqlserver，没有发言权。

via
plan
了。
in常

【在 S*****0 的大作中提到】

: link上就这2点：
: 1。 Microsoft and Sybase redefined "sargable" to mean 'can be looked up via
: the index.'"
: 2。Sargable predicates can be evaluated on the tuples of a single table.
: Unsargable predicates, or join predicates, require one or more joins to be
: performed before the predicate can be evaluated
: 我在另一本SQL Server 2008上也看到这个论点。就是NOT EXITS， EXITS这些是Non-
: sargable。
: 我同意不用index,是不合理的。我没有做过Left Join和NOT EXISTS的execution plan
: 比较, 但在我的工作中，数据量大的时候, Left Join的优势很明显，所以现在我都用