1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
-- тестовая среда Adaptive Server Enterprise/15.5/EBF 20636 SMP ESD#5.2/P/x86_64/Enterprise Linux/asear155/2602/64-bit/FBO/Sun Dec 9 15:11:04 2012
create table tablename (
id unsigned int identity not null,
f1 varchar(32),
f2 varchar(40) NULL,
f3 char(1)
)
set nocount on
go
declare @val unsigned int
select @val=convert(unsigned int, next_identity('tablename'))
insert into tablename values (
hash(@val,'md5'),
hash(@val,'sha1'),
convert(char(1),convert(int,floor(5*rand() )))
)
go 5000
set nocount off
go
create unique nonclustered index IND on tablename ( f1 ASC, f2 ASC, f3 ASC )
go
есть необходимость проверять существование (или несуществование) записи с определенными условиями:
1.
if exists ( select 1 from tablename where f1=@f1 and f2=@f2 and f3='0')
и также аналогично проверять существование условий но если запись есть - также получить её id
1.
2.
select @ruleid=id from tablename where f1=@f1 and f2=@f2 and f3='1'
if @@rowcount >0
но вот стоимости планов меня настораживают:
1.
2.
3.
4.
5.
6.
7.
8.
/* у меня есть записи
3361 04488aeba7ce50882aa3668496408543 7f8d12ea4c143eed43e968aa87c02a97fc6a8256 0
3479 f587eed3302aca2fc07f425054424225 b9f101f13da4facf1c4bb3e44d740d10929e1748 1
3490 ec6c3ff393d7b8cde23e2aa57a1e1154 70d4e0f7388dee71993a3e85c6a6ef25d78c7995 1
*/
if exists ( select 1 from tablename where f1='04488aeba7ce50882aa366849640854' and f2='7f8d12ea4c143eed43e968aa87c02a97fc6a8256' and f3='0') print "FOUND 0"
go
план:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode
STEP 1
The type of query is COND.
4 operator(s) under root
|ROOT:EMIT Operator (VA = 4)
|
| |SQFILTER Operator (VA = 3) has 2 children.
| |
| | |SCALAR Operator (VA = 0)
| |
| | Run subquery 1 (at nesting level 1).
| |
| | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 1).
| |
| | Non-correlated Subquery.
| | Subquery under an EXISTS predicate.
| |
| | |SCALAR AGGREGATE Operator (VA = 2)
| | | Evaluate Ungrouped ANY AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | tablename
| | | | Index : IND
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Index contains all needed columns. Base table will not be read.
| | | | Keys are:
| | | | f1 ASC
| | | | f2 ASC
| | | | f3 ASC
| | | | Using I/O Size 8 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| |
| | END OF QUERY PLAN FOR SUBQUERY 1.
Server Message: Number 3630, Severity 10
Server 'Meridian', Line 0:
Total estimated I/O cost for statement 1 (at line 1): 19149.
19тыс попугаев.
поиск записи по индексу - 81 попугай и одно попугайское крылышко.
1.
2.
3.
4.
declare @id unsigned int
select @id=id from tablename where f1='ec6c3ff393d7b8cde23e2aa57a1e1154' and f2='70d4e0f7388dee71993a3e85c6a6ef25d78c7995' and f3='1'
if @@rowcount >0 print "FOUND: %1!", @id
go
план:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
QUERY PLAN FOR STATEMENT 2 (at line 2).
Optimized using Serial Mode
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCAN Operator (VA = 0)
| | FROM TABLE
| | tablename
| | Index : IND
| | Forward Scan.
| | Positioning by key.
| | Keys are:
| | f1 ASC
| | f2 ASC
| | f3 ASC
| | Using I/O Size 8 Kbytes for index leaf pages.
| | With LRU Buffer Replacement Strategy for index leaf pages.
| | Using I/O Size 8 Kbytes for data pages.
| | With LRU Buffer Replacement Strategy for data pages.
Server Message: Number 3630, Severity 10
Server 'Meridian', Line 0:
Total estimated I/O cost for statement 2 (at line 2): 81.
как-то странно, что exists - лишь поиск по индексу, в то время как поиск по индексу требует также доступа в таблицу, но оценка лучше.
