问个关于sql query 运行速度的问题 - 未名空间MITBBS历史存档

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

Redian新闻

>未名空间

>Database - 数据库

问个关于sql query 运行速度的问题

问个关于sql query 运行速度的问题# Database - 数据库

d*82008-04-08 07:04

1 楼

oracle, 3个tables，每个超过1m条数据，table A 有 ID 。table B 有 ID 和B_ITEM,
table C 有 ID 和C_ITEM，问题就是那种query 运行速度快？
1，select * from A
inner join B on ...
inner join C on ...
where B_ITEM = 123 and C_ITEM =456
2, select * from A where ID in (select ID from B where B_ITEM=123 union all
select ID form C where C_ITEM=456 )
3, 还有更有的方案吗？
另外，REGEXP_LIKE() 与REGEXP_INSTR() 那个快？
谢谢。

B*g2008-04-08 07:04

2 楼

without the detail off your table and data, can tell too much.
but I would guess 2.

ITEM,
all

【在 d*******8 的大作中提到】

: oracle, 3个tables，每个超过1m条数据，table A 有 ID 。table B 有 ID 和B_ITEM,
: table C 有 ID 和C_ITEM，问题就是那种query 运行速度快？
: 1，select * from A
: inner join B on ...
: inner join C on ...
: where B_ITEM = 123 and C_ITEM =456
: 2, select * from A where ID in (select ID from B where B_ITEM=123 union all
: select ID form C where C_ITEM=456 )
: 3, 还有更有的方案吗？
: 另外，REGEXP_LIKE() 与REGEXP_INSTR() 那个快？

c*d2008-04-08 07:04

3 楼

in general, I would guess 1

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

: without the detail off your table and data, can tell too much.
: but I would guess 2.
:
: ITEM,
: all

B*g2008-04-08 07:04

4 楼

原因？呵呵

【在 c*****d 的大作中提到】

: in general, I would guess 1

w*e2008-04-08 07:04

5 楼

second that.

【在 c*****d 的大作中提到】

: in general, I would guess 1

d*82008-04-08 07:04

6 楼

tables 只有 key，没有index，我没权限搞这些。也不想麻烦那帮大爷，反正一个
qurey即使运行一天我也刷网等结果。只不过等的过程有点烦。
id 是number(9)，B_ITEM和C_ITEM都是varchar(4000),that is why i also asked REGEXP_
LIKE() 与 REGEXP_INSTR() 那个快？

B*g2008-04-08 07:04

7 楼

晕，1M的要做query的table没有index，赞一个。
另一个问题不会。

REGEXP_

【在 d*******8 的大作中提到】

: tables 只有 key，没有index，我没权限搞这些。也不想麻烦那帮大爷，反正一个
: qurey即使运行一天我也刷网等结果。只不过等的过程有点烦。
: id 是number(9)，B_ITEM和C_ITEM都是varchar(4000),that is why i also asked REGEXP_
: LIKE() 与 REGEXP_INSTR() 那个快？

B*g2008-04-08 07:04

8 楼

另外，难道他们把key 的index强行拿掉了？

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

: 晕，1M的要做query的table没有index，赞一个。
: 另一个问题不会。
:
: REGEXP_

d*82008-04-08 07:04

9 楼

no，i mean only id is the key

j*n2008-04-08 07:04

10 楼

I bet 1 is slightly better than 2.
In common sense, usually ID key has default clustered index and is arranged
by order.
1 does narrow down the searching range then do the table scan for character
match.
2 is doing two table scans and then starting join with table A.
both REGEXP_LIKE() and REGEXP_INSTR() are doing heavy load pattern matching,
I would say they are similar performance.
try Like and Instr if possible, they are simple matching so that are lighter
load.

j*n2008-04-08 07:04

11 楼

btw, in M$SQL, inner join is better performance than IN.

c*t2008-04-08 07:04

12 楼

The way database resolves query starts from actual data,
and then apply filters, does joins etc. Not the other way
around. This is how fundamentally databases work.
In this case, 1st query is resolved by applying predicates
on table B and table C (i.e. B.ITEM_B = 123). From here,
generates the necessary B.ID, and C.ID which can then be
compared with A.ID. If B.ITEM_B = 123 generates NULL set,
there won't even be a join operation.
Also, prior join / set operations, usually there are internal

B*g2008-04-08 07:04

13 楼

我不信sql server不是case by case

【在 j*****n 的大作中提到】

: btw, in M$SQL, inner join is better performance than IN.

B*g2008-04-08 07:04

14 楼

顶一个。周末咱试试，呵呵

【在 c*****t 的大作中提到】

: The way database resolves query starts from actual data,
: and then apply filters, does joins etc. Not the other way
: around. This is how fundamentally databases work.
: In this case, 1st query is resolved by applying predicates
: on table B and table C (i.e. B.ITEM_B = 123). From here,
: generates the necessary B.ID, and C.ID which can then be
: compared with A.ID. If B.ITEM_B = 123 generates NULL set,
: there won't even be a join operation.
: Also, prior join / set operations, usually there are internal

c*e2008-04-08 07:04

15 楼

1M rows is nothing here, I deal with tables like 10/M a day X 2 years, hehe

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

: 晕，1M的要做query的table没有index，赞一个。
: 另一个问题不会。
:
: REGEXP_

B*g2008-04-08 07:04

16 楼

也没有index？呵呵。

hehe

【在 c*******e 的大作中提到】

: 1M rows is nothing here, I deal with tables like 10/M a day X 2 years, hehe

d*82008-04-08 07:04

17 楼

OK, I already wrote a script and will test them tonight and this weekend (2
times):
SQL1 like:
select * from A where ID in (select ID from B where B_ITEM=123 union
all select ID form C where C_ITEM=456 )
SQL2 like:
select * from A
inner join B on ...
inner join C on ...
where B_ITEM = 123 and C_ITEM =456
SQL3 like:
select a.ID
from A a, B b, C c
where (b.ITEM_B = 123 AND b.ID = A.ID) OR
(c.ITEM_C = 456 AND c.ID = A.ID);
是同时用3个连接测还是1 by 1测精确？
any suggestion?

B*g2008-04-08 07:04

18 楼

I doubt you can trace the sql.
http://youngcow.net/doc/oracle9i/server.920/a96533/sqltrace.htm#8760

2

【在 d*******8 的大作中提到】

: OK, I already wrote a script and will test them tonight and this weekend (2
: times):
: SQL1 like:
: select * from A where ID in (select ID from B where B_ITEM=123 union
: all select ID form C where C_ITEM=456 )
: SQL2 like:
: select * from A
: inner join B on ...
: inner join C on ...
: where B_ITEM = 123 and C_ITEM =456

k*e2008-04-08 07:04

19 楼

DBA们，SQL1和SQL2根本求的不是一个结果吧
apple vs. orange 比什么比？

c*d2008-04-08 07:04

20 楼

为啥不是一个结果？

【在 k********e 的大作中提到】

: DBA们，SQL1和SQL2根本求的不是一个结果吧
: apple vs. orange 比什么比？

c*d2008-04-08 07:04

21 楼

我也见过10M条记录没索引，令人叫绝的是，该表有800多个字段
当时佩服的是五体投地

1M rows is nothing here, I deal with tables like 10/M a day X 2 years, hehe

【在 c*******e 的大作中提到】

: 1M rows is nothing here, I deal with tables like 10/M a day X 2 years, hehe

c*d2008-04-08 07:04

22 楼

one by one is OK

2

【在 d*******8 的大作中提到】

B*g2008-04-08 07:04

23 楼

Only below one got what "I" want. haha
select * from A where ID in (select ID from B where B_ITEM=123 union
all select ID form C where C_ITEM=456 )
A.
ID
1
2
3
4
B.
ID B_ITEM
1 123
2 123
3 456
C.
ID C_ITEM
1 456
3 456
4 123

【在 c*****d 的大作中提到】

: 为啥不是一个结果？

c*e2008-04-08 07:04

24 楼

我这些table都是有stats的,太大了,10M * 365 * 2 = 7.3 billion了

hehe

【在 c*****d 的大作中提到】

: 我也见过10M条记录没索引，令人叫绝的是，该表有800多个字段
: 当时佩服的是五体投地
:
: 1M rows is nothing here, I deal with tables like 10/M a day X 2 years, hehe

B*g2008-04-08 07:04

25 楼

干嘛要搞那么大？

【在 c*******e 的大作中提到】

: 我这些table都是有stats的,太大了,10M * 365 * 2 = 7.3 billion了
:
: hehe

c*e2008-04-08 07:04

26 楼

都是交易记录啊

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

: 干嘛要搞那么大？

B*g2008-04-08 07:04

27 楼

还要人吗？哈哈。
不过话说回来了老交易干嘛要都放在一个database里

【在 c*******e 的大作中提到】

: 都是交易记录啊

c*e2008-04-08 07:04

28 楼

时不时要查一下查的,呵呵.

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

: 还要人吗？哈哈。
: 不过话说回来了老交易干嘛要都放在一个database里

j*n2008-04-08 07:04

29 楼

history data should not be in OLTP, usually put in another Server, populated
by night batch feed. OLTP only keep few months data. Otherwise I doubt what
kind of server can support such heavy load for both OLTP and OLAP.
specially for 2m/day.
and for such large amount of data, partitioning usually should be considered.

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

: 还要人吗？哈哈。
: 不过话说回来了老交易干嘛要都放在一个database里

d*82008-04-08 07:04

30 楼

04/11/2008
SQL1 Total 2165.72694492
SQL2 Total 2200.25103188
SQL3 Total 2922.04847908
04/12/2008
SQL1 Total 2316.902812
SQL2 Total 2361.45646501
SQL3 Total 3111.09219217
04/13/2008
SQL1 Total 2231.486696
SQL2 Total 2277.06693292
SQL3 Total 2951.74333119

c*t2008-04-08 07:04

31 楼

Interesting, the OR operator is slow, close to 1/3 slower. I
guess this server could have performed implicit inner joins of all
3 and then perform evaluation. In this case, OR didn't get optimized
into UNION, so manual UNION is needed.

【在 d*******8 的大作中提到】

: 04/11/2008
: SQL1 Total 2165.72694492
: SQL2 Total 2200.25103188
: SQL3 Total 2922.04847908
: 04/12/2008
: SQL1 Total 2316.902812
: SQL2 Total 2361.45646501
: SQL3 Total 3111.09219217
: 04/13/2008
: SQL1 Total 2231.486696

B*g2008-04-08 07:04

32 楼

do you get same result for sql1 ,sql2?

【在 d*******8 的大作中提到】

c*t2008-04-08 07:04

33 楼

Don't be fooled. It depends on the settings. Database might be tuned by
changing some default parameters to better satify applications.

【在 c*****t 的大作中提到】

: Interesting, the OR operator is slow, close to 1/3 slower. I
: guess this server could have performed implicit inner joins of all
: 3 and then perform evaluation. In this case, OR didn't get optimized
: into UNION, so manual UNION is needed.

d*82008-04-08 07:04

34 楼

04/14/2008
SQL1 Total 2386.87175417
SQL2 Total 2836.06475782
SQL3 Total 4909.92693806
Results1 is as same as Results2.
Results3 is as same as Results2.
Results1 is as same as Results3.

B*g2008-04-08 07:04

35 楼

so the data will not have this situation:
A.
ID
1
2
3
4
B.
ID B_ITEM
1 123
2 123
3 456
C.
ID C_ITEM
1 456
3 456
4 123

【在 d*******8 的大作中提到】

: 04/14/2008
: SQL1 Total 2386.87175417
: SQL2 Total 2836.06475782
: SQL3 Total 4909.92693806
: Results1 is as same as Results2.
: Results3 is as same as Results2.
: Results1 is as same as Results3.

c*d2008-04-08 07:04

36 楼

有点意思，能不能把exec plan贴出来看看

【在 d*******8 的大作中提到】

d*82008-04-08 07:04

37 楼

To Beijing:
这里的SQL1，SQL2，SQL3 只是简化的用以表述的例子，真正的SQL后面还有〉100Char
的条件的呢。所以归根结底的结果应该是相同的。与你用以举例的3个简单table不同。
To coolbid:
不懂什么是exec plan，就简单写下我的script吧：
ls = [sql1, sql2, sql3]
t = []
r = []
for l in ls:
....start = time.time()
....r.append(getResultSetFromDB(l))
....end = time.time()
....t.append(end-start)

B*g2008-04-08 07:04

38 楼

ok.
How do you test sql 1-3? always in order sql1, sql 2, sql3? if yes, can you
try sql3, sql2 ,sq1.

100Char

【在 d*******8 的大作中提到】

: To Beijing:
: 这里的SQL1，SQL2，SQL3 只是简化的用以表述的例子，真正的SQL后面还有〉100Char
: 的条件的呢。所以归根结底的结果应该是相同的。与你用以举例的3个简单table不同。
: To coolbid:
: 不懂什么是exec plan，就简单写下我的script吧：
: ls = [sql1, sql2, sql3]
: t = []
: r = []
: for l in ls:
: ....start = time.time()

d*82008-04-08 07:04

39 楼

yes, i tried 3, 2, 1 on 04/14/2008

you

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

: ok.
: How do you test sql 1-3? always in order sql1, sql 2, sql3? if yes, can you
: try sql3, sql2 ,sq1.
:
: 100Char

c*d2008-04-08 07:04

40 楼

beijing，顺序不会对这个结果会有影响的吧？

you

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

: ok.
: How do you test sql 1-3? always in order sql1, sql 2, sql3? if yes, can you
: try sql3, sql2 ,sq1.
:
: 100Char

w*e2008-04-08 07:04

41 楼

恩, 根据结果看, 次序没影响
如果有影响, 时间应该越来越短的

【在 c*****d 的大作中提到】

: beijing，顺序不会对这个结果会有影响的吧？
:
: you

B*g2008-04-08 07:04

42 楼

要是DBA在run SQL3（例如每天2am），进行大规模的bk，analyssis。。。。

【在 c*****d 的大作中提到】

: beijing，顺序不会对这个结果会有影响的吧？
:
: you

d*82008-04-08 07:04

43 楼

嗯，我也考虑到这方面的因素，所以在最后一次比较结果的时候顺便将次序换了一下。

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

: 要是DBA在run SQL3（例如每天2am），进行大规模的bk，analyssis。。。。

w*r2008-04-08 07:04

44 楼

不乖，上次问你的问题你怎么没有反应地！
你那个数据库那么巨大啊?每天10M一年就是3B,两年的事6B...，你什么时候跑到ATT/
SBC工作的？

hehe

【在 c*******e 的大作中提到】

: 1M rows is nothing here, I deal with tables like 10/M a day X 2 years, hehe

c*e2008-04-08 07:04

45 楼

没注意看啊,去找找

【在 w*r 的大作中提到】

: 不乖，上次问你的问题你怎么没有反应地！
: 你那个数据库那么巨大啊?每天10M一年就是3B,两年的事6B...，你什么时候跑到ATT/
: SBC工作的？
:
: hehe

z*y2008-04-08 07:04

46 楼

union all 会导致merge scan, 不必要的使用tempdb tablespace.
1 might be better.

ITEM,
all

【在 d*******8 的大作中提到】

c*d2008-04-08 07:04

47 楼

你说反了吧
union all不会导致merge scan

【在 z***y 的大作中提到】

: union all 会导致merge scan, 不必要的使用tempdb tablespace.
: 1 might be better.
:
: ITEM,
: all

B*g2008-04-08 07:04

48 楼

ATT用啥数据库

ATT/

【在 c*******e 的大作中提到】

: 没注意看啊,去找找

c*e2008-04-08 07:04

49 楼

not sure about att, we use teradata + oracle

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

: ATT用啥数据库
:
: ATT/

w*r2008-04-08 07:04

50 楼

ATT is so far the largest Teradata implementation across the world. Walmart
used to be the No.1, now
walmart is moving toward neoview.

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

: ATT用啥数据库
:
: ATT/

w*r2008-04-08 07:04

51 楼

yi?
你不在ATT...哪个公司有一天10M以上的transaction啊？walmart...

【在 c*******e 的大作中提到】

: not sure about att, we use teradata + oracle

B*g2008-04-08 07:04

52 楼

NASA

【在 w*r 的大作中提到】

: yi?
: 你不在ATT...哪个公司有一天10M以上的transaction啊？walmart...

B*g2008-04-08 07:04

53 楼

你那有oracle不推荐我？

【在 c*******e 的大作中提到】