Запрос к большой таблице / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Запрос к большой таблице

10 сообщений из 10, страница 1 из 1

Запрос к большой таблице

#39924129

_human

Участник

Сообщения: 525

Рейтинг: 0 / 0

Приветствую

Есть большая таблица
120 патриций по +- 20М записей в каждой...

Есть набор записей id1 - date_field в кол-е 600к-1М которьіе вьібирают из
разньіх партиций по +-500 записей

схема

Код: sql

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.

create large_table
	(
	   datetime_field datetime not null -- full date and time
	  ,id1 bigint not null
	  ,id2 bigint not null
	  ,my_target_field nvarchar(100)
	  -- rest of the fields
	) -- ON <partitioned by a calendar day>

	create unique clustered index cix_large_table_pk on large_table (  
	   datetime_field 
	  ,id1 
	  ,id2 
	)

	create index ix_large_table_id1 on large_table (  
	  id1 
	)

	create table random_subset_of_large_table
	(
	   id1 bigint not null
	  ,date_field datetime not null -- only date without time fraction
	)

  create clustered index 
    cix_random_subset_of_large_table_id1 on random_subset_of_large_table (
      id1 )

вещи вроде такого "не возвращаются"

Код: sql

1.
2.
3.
4.
5.
6.
7.

-- date_field = convert(date, datetime_field)
select lt.my_target_field
from large_table as lt
join random_subset_of_large_table as rs on 
    lt.datetime_field >= rs.date_field
    and lt.date_field < dateadd(day, 1, rs.date_field)
    and lt.id1 = rs.id1

возможно самьій лучший план:
nested loops
index scan cix_random_subset_of_large_table_id1
+index seek cix_large_table_pk

вопрос1 - будет ли лучше работать решение с merge join ?
вопрос2 - будет ли лучше написать курсор-цикл и "целиться" в 1-у
партицию в итериции

буду признателен за рекомендации по решению подобньіх задач
пс. индекс по my_target_field не предлагать

...

Рейтинг:

0 / 0

08.02.2020, 04:12

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39924141

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

1. Минимум

Код: sql

1.
2.
3.
4.
5.
6.

	create table random_subset_of_large_table
	(
	   id1 bigint not null
	  , date_field date not null -- only date without time fraction
          , unique clustered index (  datetime_field , id1 )
	)

2. lt.id1 = rs.id1 значения для точного равенства следует ставить в индексах ПЕРВЫМИ.

Код: sql

1.
2.
3.
4.
5.
6.

	create table random_subset_of_large_table
	(
	   id1 bigint not null
	  , date_field date not null -- only date without time fraction
          , unique clustered index (  id1, datetime_field  )
	)

НО! это надо сделать и в большой таблице.

Код: sql

1.
2.
3.

create UNIQUE index ix_large_table_id1 on large_table (  
	  id1, date_field, id2
	)

...

Рейтинг:

0 / 0

08.02.2020, 09:12

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39925975

Александр Бердышев

Участник

Откуда: Санкт-Петербург

Сообщения: 201

Рейтинг: 0 / 0

В 1 таблице кластеризованный индекс сначала по дате, потом по id1, во второй таблице - по id1.
Во-первых, join лучше делать по условию что T1.id1 = T2.id1, а потом уже другие условия.
Во вторых: поскольку в 1 таблице в кластеризованном индексе сортировка в 1 очередь по дате - физически на страницах памяти в базе будет всё упорядочено по дате, а потом уже по id1.
Если пересоздать кластеризованный индекс 1 таблицы так, чтобы упорядочить в 1 очередь по id1 - то можно будет сделать merge join - он должен быстрее всего работать.

Ещё рекомендую посмотреть, как сделать чтобы записи джоинились только в рамках своей партиции - рекомендую во второй таблице на этот случай завести ключ к партиции из первой таблицы и в первую очередь джоиниться по нему, а уже во вторую - по индексу.
Конечно при условии, что у вас индекс тоже партиционированный. При этом условие по партиции должно быть строго равно, без всяких больше или равно.

...

Рейтинг:

0 / 0

12.02.2020, 19:50

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39925981

msLex

Участник

Сообщения: 8 523

Рейтинг: 0 / 0

Александр Бердышев

и в первую очередь джоиниться по нему, а уже во вторую - по индексу.

т.е.

Код: sql

on a.id = b.id  and a.partition_key = b.partition_key

хуже чем

Код: sql

on a.partition_key = b.partition_key and a.id = b.id

...

Рейтинг:

0 / 0

12.02.2020, 20:04

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39925991

invm

Участник

Откуда: Москва

Сообщения: 9 956

Рейтинг: 0 / 0

_human,

Попробуйте так

Код: sql

1.
2.
3.
4.
5.

select
 lt.my_target_field
from 
 random_subset_of_large_table as rs inner loop join
 large_table as lt on $partition.MyPartitionFunction(lt.datetime_field) = $partition.MyPartitionFunction(rs.date_field) and lt.id1 = rs.id1

...

Рейтинг:

0 / 0

12.02.2020, 20:41

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39940139

_human

Участник

Сообщения: 525

Рейтинг: 0 / 0

Всем спасибо
Задача бьіла решена путем уменьшения обьема данньіх.

...

Рейтинг:

0 / 0

23.03.2020, 07:32

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39940142

_human

Участник

Сообщения: 525

Рейтинг: 0 / 0

Однако есть другая немного схема

Код: sql

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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.

    use tempdb
    go

    drop function if exists dbo.nums
    go
    create function dbo.nums (@num int)
    returns table
    as
    return (
        select 
            top (@num)
            row_number() over(order by (select null)) as n
        from master..spt_values as v1
        cross apply master..spt_values as v2
    )
    go

    drop table if exists dbo.large_table
    go
    create table dbo.large_table (
         datetime_field datetime not null
         ,sub_id bigint not null
         ,target_field as 'bingo'
     ) on [primary]
    go

    if exists (select top 1 1 from sys.partition_schemes where name = 'ps_day')
        drop partition scheme ps_day
    go
    if exists (select top 1 1 from sys.partition_functions where name ='pf_day')
        drop partition function pf_day
    go

    declare @part_num int = 6*30;
    declare @parts nvarchar(max) = N'';
    
    select 
        @parts += 
            concat(
                quotename(
                    convert(varchar, 
                        convert(datetime, 
                            convert(varchar, 
                                dateadd(day, -1*(n-1), getdate()), 112)), 121), ''''), ',')
        from dbo.nums(@part_num)
    
    set @parts = left(@parts, len(@parts)-1)

    exec(
        N' CREATE PARTITION FUNCTION pf_day (datetime)  
            AS RANGE RIGHT FOR VALUES ('+@parts+') ;  
        ')
    GO

    CREATE PARTITION SCHEME ps_day  
        AS PARTITION pf_day  
       ALL TO ([primary]) ;  
    GO  

    --select  24*60*60*1000
    
    set nocount on;

    drop table if exists #days
    go
    select 
        --top 1 
        convert(datetime, r.value) as target_date
    into #days
    from sys.partition_schemes AS s   
    JOIN sys.partition_functions AS f   
        ON s.function_id = f.function_id  
    LEFT JOIN sys.partition_range_values AS r   
        ON f.function_id = r.function_id
    where f.name = 'pf_day'

    truncate table dbo.large_table

    declare @target_date datetime;
    declare @top int
    declare @part_size int = power(10, 5)
    declare @gen_number int = 0
    declare @current_time datetime
    declare @current_sub_id bigint

    while exists (select top 1 1 from #days)
    begin
        set @target_date = (select top 1 target_date from #days)
        set @gen_number = 0

        while(1=1)
        begin
            set @top = convert(tinyint, right(convert(varchar, checksum(newid())), 1))
            if @top = 0
                set @top = 1;      

            set @current_time = dateadd(millisecond
                    --fit into day
                    ,abs(convert(int, left(convert(varchar, abs(checksum(newid()))), 8))-(13599999-2)) 
                    ,@target_date)
            set @current_sub_id = abs(checksum(newid()))

            insert into dbo.large_table(datetime_field, sub_id)
            select 
                @current_time
                ,@current_sub_id
            from dbo.nums(@top)

            set @gen_number += @@ROWCOUNT

            if @gen_number > @part_size
                break;
        end 

        delete #days
        where target_date = @target_date
        print concat(@target_date, ' completed')
    end

    alter table dbo.large_table 
        add id bigint identity(1,1)

    alter table dbo.large_table 
        add constraint pk_large_table PRIMARY KEY (datetime_field, id) on ps_day(datetime_field)
    go
    create index ix_large_table_sub_id on dbo.large_table (sub_id) on ps_day(datetime_field)
    go

    drop table if exists #days
    go
    select 
        convert(datetime, r.value) as target_date
        ,$partition.pf_day(convert(datetime, r.value)) as part
    into #days
    from sys.partition_schemes AS s   
    JOIN sys.partition_functions AS f   
        ON s.function_id = f.function_id  
    LEFT JOIN sys.partition_range_values AS r   
        ON f.function_id = r.function_id
    where f.name = 'pf_day'    

    drop table if exists dbo.sub_set
    select distinct
        d.target_date as datetime_field
        ,A.sub_id
    into dbo.sub_set
    from #days as d
    cross apply (
        select
            top 1 percent
                lt.sub_id
        from dbo.large_table as lt
        where 
            d.part = $partition.pf_day(lt.datetime_field)
    ) as A


    -- counts 
    select
        convert(date, datetime_field)
        ,count(*)
    from dbo.large_table
    group by convert(date, datetime_field)

    select
         datetime_field
        ,count(*)
    from dbo.sub_set
    group by datetime_field

/*
    -- sample
    select top 100 *
    from dbo.large_table
    where 
        $partition.pf_day(datetime_field) = $partition.pf_day(getdate())

    select top 100 *
    from dbo.sub_set
    where datetime_field = convert(date, getdate())
*/

Код: plaintext

1.
2.
3.
4.
5.

    -- время генерации семпла на моем слабом железе с кучей локальной виртуализации... хотя дисочек ssd
    -- 180 parts
        -- part size 100 recs ~ 3 seconds
        -- part size 1`000 recs ~ 14 seconds
        -- part size 10`000 recs ~ 2 minutes
        -- part size 100`000 recs ~ 16 minutes

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

    -- #1, part 100`000 recs
    drop index ix_sub_set_sub_id on dbo.sub_set
    create index ix_sub_set_sub_id on dbo.sub_set (sub_id)

    dbcc dropcleanbuffers
    checkpoint;
    set statistics time on
    set statistics io on
    set statistics profile on     
    select
        lt.target_field
    from dbo.large_table as lt
    join dbo.sub_set as ss on
        lt.sub_id = ss.sub_id

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

Table 'sub_set'. Scan count 5, logical reads 322, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'large_table'. Scan count 181, logical reads 67680, physical reads 224, page server reads 0, read-ahead reads 67265, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(9 rows affected)

 SQL Server Execution Times:
   CPU time = 2096 ms,  elapsed time = 3237 ms.
Total execution time: 00:00:03.623

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

    -- #2, part 100`000 recs
    drop index uix_sub_set_datetime_field_sub_id on dbo.sub_set 
    create unique index uix_sub_set_datetime_field_sub_id on dbo.sub_set (datetime_field, sub_id)

    dbcc dropcleanbuffers
    checkpoint;

    set statistics time on
    set statistics io on
    set statistics profile on 
    select
        lt.target_field
    from dbo.large_table as lt
    join dbo.sub_set as ss on
        $partition.pf_day(lt.datetime_field) = $partition.pf_day(ss.datetime_field)
        and lt.sub_id = ss.sub_id

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

Table 'sub_set'. Scan count 5, logical reads 125, physical reads 0, page server reads 0, read-ahead reads 136, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'large_table'. Scan count 181, logical reads 67680, physical reads 224, page server reads 0, read-ahead reads 67263, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(11 rows affected)

 SQL Server Execution Times:
   CPU time = 7517 ms,  elapsed time = 5520 ms.
Total execution time: 00:00:05.749

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

    -- #3, part 100`000 recs
    drop index uix_sub_set_datetime_field_sub_id on dbo.sub_set 
    create unique index uix_sub_set_datetime_field_sub_id on dbo.sub_set (datetime_field, sub_id)

    dbcc dropcleanbuffers
    checkpoint;

    set statistics time on
    set statistics io on
    set statistics profile on 
    
    SELECT
        ss.sub_id
        ,A.target_field
    from dbo.sub_set as ss
    cross apply(
        select lt.target_field
        from dbo.large_table as lt
        where 
            lt.datetime_field >= ss.datetime_field
            and lt.datetime_field < dateadd(day, 1, ss.datetime_field)
            and lt.sub_id = ss.sub_id 
    ) as A

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

Table 'sub_set'. Scan count 5, logical reads 125, physical reads 0, page server reads 0, read-ahead reads 136, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'large_table'. Scan count 181, logical reads 67680, physical reads 224, page server reads 0, read-ahead reads 67264, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(9 rows affected)

 SQL Server Execution Times:
   CPU time = 12215 ms,  elapsed time = 9768 ms.
Total execution time: 00:00:10.025

...

Рейтинг:

0 / 0

23.03.2020, 08:04

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39940145

_human

Участник

Сообщения: 525

Рейтинг: 0 / 0

в большой таблице ничго менять нельзя
индексьі на dbo.sub_set можно создавать

в реальности большая таблица огромна
секционирование, в отличии от семпла на большой таблице сделано правильно - файловьіе группьі
размер секций 10-20М записей
кол-во секций больше

Все решения не удовлетворяют по скорости на реальньіх данньіх
На реальньіх данньіх решение #1 использует seek по ix_large_table_sub_id давая наилучший результат.

Решения с курсорами/циклами не возвращаются

Посоветуйте)

...

Рейтинг:

0 / 0

23.03.2020, 08:16

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39940149

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Код: sql

1.
2.

    -- #1, part 100`000 recs
    create UNIQUE index ix_sub_set_sub_id on dbo.sub_set (sub_id)

...

Рейтинг:

0 / 0

23.03.2020, 08:48

| Ответить | Цитировать | Написать

Запрос к большой таблице

#39940325

_human

Участник

Сообщения: 525

Рейтинг: 0 / 0

aleks222,

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

    -- #1, part 100`000 recs
    --drop index ix_sub_set_sub_id on dbo.sub_set
    --create index ix_sub_set_sub_id on dbo.sub_set (sub_id)

    dbcc dropcleanbuffers
    checkpoint;
    set statistics time on
    set statistics io on
    set statistics profile on     
    select
        lt.target_field
    from dbo.large_table as lt
    join dbo.sub_set as ss on
        lt.sub_id = ss.sub_id

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

Table 'sub_set'. Scan count 5, logical reads 325, physical reads 1, page server reads 0, read-ahead reads 108, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'large_table'. Scan count 181, logical reads 67680, physical reads 223, page server reads 0, read-ahead reads 67261, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(9 rows affected)

 SQL Server Execution Times:
   CPU time = 1593 ms,  elapsed time = 2308 ms.
Total execution time: 00:00:02.545

#1 exec plan

RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions1809291"select lt.target_field from dbo.large_table as lt join dbo.sub_set as ss on lt.sub_id = ss.sub_id"110NULLNULLNULLNULL222101.8NULLNULLNULL145.6149NULLNULLSELECT0NULL00" |--Compute Scalar(DEFINE:([lt].[target_field]='bingo'))"121Compute ScalarCompute ScalarDEFINE:([lt].[target_field]='bingo')[lt].[target_field]='bingo'222101.800.0222101813145.6149[lt].[target_field]NULLPLAN_ROW011809291" |--Parallelism(Gather Streams)"132ParallelismGather StreamsNULLNULL222101.800.16759139145.5927NULLNULLPLAN_ROW111809294" |--Hash Match(Inner Join HASH:([ss].[sub_id])=([lt].[sub_id]) RESIDUAL:([tempdb].[dbo].[sub_set].[sub_id] as [ss].[sub_id]=[tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id]))"143Hash MatchInner Join"HASH:([ss].[sub_id])=([lt].[sub_id]) RESIDUAL:([tempdb].[dbo].[sub_set].[sub_id] as [ss].[sub_id]=[tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id])"NULL222101.8059.413559145.4251NULLNULLPLAN_ROW11396244" |--Bitmap(HASH:([ss].[sub_id]) DEFINE:([Bitmap1004]))"154BitmapBitmap CreateHASH:([ss].[sub_id])[Bitmap1004]3962400.08536539150.1896214[ss].[sub_id]NULLPLAN_ROW11396244" | |--Parallelism(Repartition Streams Hash Partitioning PARTITION COLUMNS:([ss].[sub_id]))"165ParallelismRepartition StreamsPARTITION COLUMNS:([ss].[sub_id])NULL3962400.08536539150.1896214[ss].[sub_id]NULLPLAN_ROW11396244" | |--Index Scan(OBJECT:([tempdb].[dbo].[sub_set].[ix_sub_set_sub_id] AS [ss]))"176Index ScanIndex ScanOBJECT:([tempdb].[dbo].[sub_set].[ix_sub_set_sub_id] AS [ss])[ss].[sub_id]396240.082384260.0218717150.104256[ss].[sub_id]NULLPLAN_ROW112714454" |--Parallelism(Repartition Streams Hash Partitioning PARTITION COLUMNS:([lt].[sub_id]))"184ParallelismRepartition StreamsPARTITION COLUMNS:([lt].[sub_id])NULL1.800066E+07025.86171585.82195[lt].[sub_id]NULLPLAN_ROW112714454" |--Index Scan(OBJECT:([tempdb].[dbo].[large_table].[ix_large_table_sub_id] AS [lt]) WHERE:(PROBE([Bitmap1004][tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id]N'[IN ROW]')))"198Index ScanIndex Scan"OBJECT:([tempdb].[dbo].[large_table].[ix_large_table_sub_id] AS [lt]) WHERE:(PROBE([Bitmap1004][tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id]N'[IN ROW]'))"[lt].[sub_id]1.800066E+0750.031559.9287021559.96025[lt].[sub_id]NULLPLAN_ROW11

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

       -- #4, part 100`000 recs
    go
    --select
    --    distinct
    --    sub_id
    --into dbo.sub_set_unq
    --from dbo.sub_set 

    --drop index uix_sub_set_unq_sub_id on dbo.sub_set_unq
    --create index uix_sub_set_unq_sub_id on dbo.sub_set_unq (sub_id)

    dbcc dropcleanbuffers
    checkpoint;
    set statistics time on
    set statistics io on
    set statistics profile on     
    select
        lt.target_field
    from dbo.large_table as lt
    join dbo.sub_set_unq as ss on
        lt.sub_id = ss.sub_id

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

Table 'sub_set_unq'. Scan count 5, logical reads 84, physical reads 0, page server reads 0, read-ahead reads 88, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'large_table'. Scan count 181, logical reads 67680, physical reads 223, page server reads 0, read-ahead reads 67261, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(9 rows affected)

 SQL Server Execution Times:
   CPU time = 1679 ms,  elapsed time = 2501 ms.
Total execution time: 00:00:02.898

#4 exec plan

RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions1806851"select lt.target_field from dbo.large_table as lt join dbo.sub_set_unq as ss on lt.sub_id = ss.sub_id"110NULLNULLNULLNULL221950.4NULLNULLNULL145.5966NULLNULLSELECT0NULL00" |--Compute Scalar(DEFINE:([lt].[target_field]='bingo'))"121Compute ScalarCompute ScalarDEFINE:([lt].[target_field]='bingo')[lt].[target_field]='bingo'221950.400.0221950413145.5966[lt].[target_field]NULLPLAN_ROW011806851" |--Parallelism(Gather Streams)"132ParallelismGather StreamsNULLNULL221950.400.16749649145.5744NULLNULLPLAN_ROW111806854" |--Hash Match(Inner Join HASH:([ss].[sub_id])=([lt].[sub_id]) RESIDUAL:([tempdb].[dbo].[sub_set_unq].[sub_id] as [ss].[sub_id]=[tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id]))"143Hash MatchInner Join"HASH:([ss].[sub_id])=([lt].[sub_id]) RESIDUAL:([tempdb].[dbo].[sub_set_unq].[sub_id] as [ss].[sub_id]=[tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id])"NULL221950.4059.413169145.4069NULLNULLPLAN_ROW11395974" |--Bitmap(HASH:([ss].[sub_id]) DEFINE:([Bitmap1004]))"154BitmapBitmap CreateHASH:([ss].[sub_id])[Bitmap1004]3959700.08532664150.1718292[ss].[sub_id]NULLPLAN_ROW11395974" | |--Parallelism(Repartition Streams Hash Partitioning PARTITION COLUMNS:([ss].[sub_id]))"165ParallelismRepartition StreamsPARTITION COLUMNS:([ss].[sub_id])NULL3959700.08532664150.1718292[ss].[sub_id]NULLPLAN_ROW11395974" | |--Table Scan(OBJECT:([tempdb].[dbo].[sub_set_unq] AS [ss]))"176Table ScanTable ScanOBJECT:([tempdb].[dbo].[sub_set_unq] AS [ss])[ss].[sub_id]395970.064684980.0218176150.08650258[ss].[sub_id]NULLPLAN_ROW112714454" |--Parallelism(Repartition Streams Hash Partitioning PARTITION COLUMNS:([lt].[sub_id]))"184ParallelismRepartition StreamsPARTITION COLUMNS:([lt].[sub_id])NULL1.800066E+07025.86171585.82195[lt].[sub_id]NULLPLAN_ROW112714454" |--Index Scan(OBJECT:([tempdb].[dbo].[large_table].[ix_large_table_sub_id] AS [lt]) WHERE:(PROBE([Bitmap1004][tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id]N'[IN ROW]')))"198Index ScanIndex Scan"OBJECT:([tempdb].[dbo].[large_table].[ix_large_table_sub_id] AS [lt]) WHERE:(PROBE([Bitmap1004][tempdb].[dbo].[large_table].[sub_id] as [lt].[sub_id]N'[IN ROW]'))"[lt].[sub_id]1.800066E+0750.031559.9287021559.96025[lt].[sub_id]NULLPLAN_ROW11

Наглядного резульатата на сампле нет, но вполне может бьіть на большших данньіх
Скоро проверю

...

Рейтинг:

0 / 0

23.03.2020, 17:59

| Ответить | Цитировать | Написать

10 сообщений из 10, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Запрос к большой таблице

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=39925975&tid=1686303]:	0ms
get settings:	11ms
get forum list:	10ms
check forum access:	3ms
check topic access:	3ms
track hit:	38ms
get topic data:	9ms
get forum data:	2ms
get page messages:	45ms
get tp. blocked users:	1ms
others:	324ms

total:	446ms