Помогите оптимизировать запрос или БД

gandalf-the-grey

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7600: ) - это тестовый сервак, на боевом с SP3 производительность такая же.Дано:CREATE TABLE [dbo].[People]( [id] [int] IDENTITY(1,1) NOT NULL, ... [cardnumber] [varchar](19) NULL, ...)CREATE TABLE [dbo].[Cards]( [card] [varchar](19) NOT NULL ...)в первой таблице cardnumber - номер карты (например 300111222)во второй card - маска вида '300______'делаю некий с кучей лефт джойновselect ...from data..people pleft join ...left join ......left join data..cards on p.cardnumber like cards.cardЕсли значения cardnumber не превышают в длину 9-10 символов, для 5000 записей запрос выполняется менее секунды. Если же cardnumber длина = 19, то писец, 5000 записей - 35 секундИндексы на оба поля в таблицах ессно есть.Пошёл по другому пути, сделал в первой таблице дубликат поля carnumber: [card_i] [bigint], туда по триггеру пишу cast as bigint, во второй табле сделал такCREATE TABLE [dbo].[Cards]( [card] [bigint] NOT NULL [card_e] [bigint] NOT NULL ...)где теперь не маска, а диапазонтеперь на 19-значных поляхselect ...from data..people pleft join ...left join ......left join data..cards on p.card_i between cards.card and cards.card_eвыполняется 9 секунд, но это тоже неприемлемо. На 9-10 значных значениях по прежнему менее секунды.Куда ковырять? Ума не приложу...
24 ответа

gandalf-the-grey

Если значения cardnumber не превышают в длину 9-10 символов, для 5000 записей запрос выполняется менее секунды.Если же cardnumber длина = 19, то писец, 5000 записей - 35 секунд
А план при этом как меняется?


gandalf-the-grey

никак.89% занимает TableSpool


gandalf-the-grey

никак.89% занимает TableSpool
Наверное меняется число записей попадающих в этот TableSpool


gandalf-the-grey

Glory, в тестовой базе в первой таблице есть 7000 записей, где len(cardnumber)=9 и 5000 записей, где len(cardnumber)=19, разделённых неким другим полем-признаком.НО! во второй таблице, где диапазоны сейчас, для 9-значных номеров 5 записей (5 диапазонов), а для 19-значных - для каждой свой диапазон из 1 номера.Но неужели за счёт лишних 5000 записей во второй таблице такое падение производительности? В боевой БД десятки-сотни тысяч записей, что же будет там?))Убрать отдельные диапазоны для 19-значных номеров нельзя, ибо номера не по порядку, уникальные.


gandalf-the-grey

Кстати, прошу прощения, ошибся немного в исходных данных:менее 1 секунды и 35 секунд - это времена выполнения запросов уже на bigint'епри like на varchar'е 1:12 на 9-значных и ооочень долго на 19-значных


gandalf-the-grey

При чем тут bigint к like?Ведь like для строк же...


gandalf-the-grey

При чем тут bigint к like?Ведь like для строк же...
Есть такая штука, как неявное приведение типов.Так что LIKE с BIGINT работает, конечно...


gandalf-the-grey

Паганель,изначально в таблицах были varchar'ы, в одной НОМЕР в другой МАСКА. Это написано в заглавном посте.Но на like и varchar всё выолнялось ваще около 2 минут (1:12 и 1:51 для 9- и 19-значных номеров)После перехода на bigint стало выполняться 0:00 и 0:09. Но 0:09 - неприемлемо долго.


gandalf-the-grey

iap,я не использую like с bigint. Для bigint я использую between, прочтите первый пост, пожалуйста, ещё раз.


gandalf-the-grey

никак.89% занимает TableSpool
А откуда этот Table Spool вообще берется? Покажите план.


gandalf-the-grey

iljy,если запрос упростить доselect p.cardnumber,cards.card,cards.card_efrom data..people pleft join data..cards on p.card_i between cards.card and cards.card_ewhere p.place=1то TableSpool в плане нет, но выполняется он всё равно 9 секунд.Как выложить план сюда? Я вижу егов графике, могу сохранить в xml... есть ещё варианты?


gandalf-the-grey

для упрощенного запросаselect p.cardnumber,cards.cardfrom data..people pleft join data..cards on p.card_i between cards.card and cards.card_e where p.place=1
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[card_i]))
 |--Clustered Index Scan(OBJECT:([Data].[dbo].[People].[PK_People] AS [p]), WHERE:([Data].[dbo].[People].[place] as [p].[place]=(1)))
 |--Index Seek(OBJECT:([Data].[dbo].[Cards].[IX_Cards_unique]), SEEK:([Data].[dbo].[Cards].[card] <= [Data].[dbo].[People].[card_i] as [p].[card_i]), WHERE:([Data].[dbo].[People].[card_i] as [p].[card_i]<=[Data].[dbo].[Cards].[card_e]) ORDERED FORWARD)


gandalf-the-grey

Дано:CREATE TABLE [dbo].[People]( [id] [int] IDENTITY(1,1) NOT NULL, ... [cardnumber] [varchar](19) NULL, ...)CREATE TABLE [dbo].[Cards]( [card] [varchar](19) NOT NULL ...)в первой таблице cardnumber - номер карты (например 300111222)во второй card - маска вида '300______'делаю некий с кучей лефт джойновselect ...from data..people pleft join ...left join ......left join data..cards on p.cardnumber like cards.cardКуда ковырять? Ума не приложу...
Хе-хе... Ваша "проблема" в "куче лефт джойнов", которые вы скрываете.Стандартный обход - использование времянки для
select ...
from data..people p
left join data..cards on p.cardnumber like cards.card
а затем "куча лефт джойнов" с этой времянкой...


gandalf-the-grey

План реального запроса. Время выполнения ОБОИХ - 9 секундselect p.id, p.cardnumber, p.printed,p.lastname+' '+isnull(p.firstname,'?')+' '+isnull(p.middlename,'?') fio,case when p.gender=0 then 'М' else 'Ж' end gender,p.birthday, p.zip,c.name_country,r.name_region+' '+p1.rn_point name_region,d.name_district+case when p2.id_point=0 then '' else ' ' end+p2.rn_point name_district,s.name_site + case when p3.id_point=0 then '' else ' '+p3.rn_point end + case when p.id_site>1000 and cast(right(p.id_site,3) as int)<>0then ' ('+(select n1.name_site+' '+n2.rn_point from data..sites n1,data..points n2 where n1.id_country=p.id_country and n1.id_region=p.id_region and n1.id_district=p.id_district and n1.id_site=p.id_site-cast(right(p.id_site,3) as int) and n2.id_point=n1.idp_sc)+')'else '' end name_site,st.name_street+' '+p4.rn_point name_street,p.house, p.house_bldg, p.house_str, p.flat, p.phone1, p.phone2, p.passport, sg.name, p.date_in, t.id id_type, t.name name_type,status.name status, p.email, p.comment, isnull(cards.card,' ') card, p.create_date, p.create_who, p.change_date, p.change_whofrom data..people pleft join data..countries c on c.id_country=p.id_countryleft join data..regions r on r.id_country=p.id_country and r.id_region=p.id_regionleft join data..districts d on d.id_country=p.id_country and d.id_region=p.id_region and d.id_district=p.id_districtleft join data..sites s on s.id_country=p.id_country and s.id_region=p.id_region and s.id_district=p.id_district and s.id_site=p.id_siteleft join data..streets st on st.id_country=p.id_country and st.id_region=p.id_region and st.id_district=p.id_district and st.id_site=p.id_site and st.id_street=p.id_streetleft join data..socialgrp sg on sg.id=p.social_grp left join data..points p1 on p1.id_point=r.idp_rleft join data..points p2 on p2.id_point=d.idp_dleft join data..points p3 on p3.id_point=s.idp_scleft join data..points p4 on p4.id_point=st.idp_stleft join data..type t on t.id=p.typeleft join data..status on status.id=p.statusleft join data..cards on p.card_i between cards.card and cards.card_ewhere p.place=1|--Compute Scalar(DEFINE:([Expr1033]=[Expr1049]+[Data].[dbo].[Points].[rn_point] as [p1].[rn_point], [Expr1034]=([Data].[dbo].[Districts].[name_district] as [d].[name_district]+[Expr1050])+[Data].[dbo].[Points].[rn_point] as [p2].[rn_point], [Expr1040]=([Data].[dbo].[Sites].[name_site] as [s].[name_site]+[Expr1051])+CASE WHEN [Data].[dbo].[People].[id_site] as [p].[id_site]>(1000) AND [Expr1052]<>(0) THEN (' ('+[Expr1044])+')' ELSE '' END, [Expr1041]=[Expr1053]+[Data].[dbo].[Points].[rn_point] as [p4].[rn_point])) |--Nested Loops(Left Outer Join, PASSTHRU:(IsFalseOrNull [Data].[dbo].[People].[id_site] as [p].[id_site]>(1000) AND [Expr1052]<>(0)), OUTER REFERENCES:([p].[id_country], [p].[id_region], [p].[id_district], [p].[id_site])) |--Compute Scalar(DEFINE:([Expr1042]=isnull([Data].[dbo].[Cards].[card],(0)))) | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[card_i])) | |--Sort(ORDER BY:([p].[id_country] ASC, [p].[id_region] ASC, [p].[id_district] ASC, [p].[id_site] ASC)) | | |--Hash Match(Right Outer Join, HASH:([Data].[dbo].[Status].[id])=([p].[status]), RESIDUAL:([Data].[dbo].[Status].[id]=[Data].[dbo].[People].[status] as [p].[status])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Status].[PK_Status])) | | |--Hash Match(Right Outer Join, HASH:([t].[id])=([p].[type]), RESIDUAL:([Data].[dbo].[Type].[id] as [t].[id]=[Data].[dbo].[People].[type] as [p].[type])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Type].[PK_Type] AS [t])) | | |--Hash Match(Right Outer Join, HASH:([p4].[id_point])=([st].[idp_st]), RESIDUAL:([Data].[dbo].[Points].[id_point] as [p4].[id_point]=[Data].[dbo].[Streets].[idp_st] as [st].[idp_st])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Points].[PK_Points] AS [p4])) | | |--Compute Scalar(DEFINE:([Expr1051]=CASE WHEN [Data].[dbo].[Points].[id_point] as [p3].[id_point]=(0) THEN '' ELSE ' '+[Data].[dbo].[Points].[rn_point] as [p3].[rn_point] END)) | | |--Hash Match(Right Outer Join, HASH:([p3].[id_point])=([s].[idp_sc]), RESIDUAL:([Data].[dbo].[Points].[id_point] as [p3].[id_point]=[Data].[dbo].[Sites].[idp_sc] as [s].[idp_sc])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Points].[PK_Points] AS [p3])) | | |--Compute Scalar(DEFINE:([Expr1050]=CASE WHEN [Data].[dbo].[Points].[id_point] as [p2].[id_point]=(0) THEN '' ELSE ' ' END)) | | |--Hash Match(Right Outer Join, HASH:([p2].[id_point])=([d].[idp_d]), RESIDUAL:([Data].[dbo].[Points].[id_point] as [p2].[id_point]=[Data].[dbo].[Districts].[idp_d] as [d].[idp_d])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Points].[PK_Points] AS [p2])) | | |--Hash Match(Right Outer Join, HASH:([p1].[id_point])=([r].[idp_r]), RESIDUAL:([Data].[dbo].[Points].[id_point] as [p1].[id_point]=[Data].[dbo].[Regions].[idp_r] as [r].[idp_r])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Points].[PK_Points] AS [p1])) | | |--Hash Match(Right Outer Join, HASH:([sg].[id])=([p].[social_grp]), RESIDUAL:([Data].[dbo].[SocialGrp].[id] as [sg].[id]=[Data].[dbo].[People].[social_grp] as [p].[social_grp])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[SocialGrp].[PK_SocialGrp] AS [sg])) | | |--Compute Scalar(DEFINE:([Expr1053]=[Data].[dbo].[Streets].[name_street] as [st].[name_street]+' ')) | | |--Merge Join(Right Outer Join, MERGE:([s].[id_country], [s].[id_region], [s].[id_district], [s].[id_site])=([p].[id_country], [p].[id_region], [p].[id_district], [p].[id_site]), RESIDUAL:([Data].[dbo].[Sites].[id_country] as [s].[id_country]=[Data].[dbo].[People].[id_country] as [p].[id_country] AND [Data].[dbo].[Sites].[id_region] as [s].[id_region]=[Data].[dbo].[People].[id_region] as [p].[id_region] AND [Data].[dbo].[Sites].[id_district] as [s].[id_district]=[Data].[dbo].[People].[id_district] as [p].[id_district] AND [Data].[dbo].[Sites].[id_site] as [s].[id_site]=[Data].[dbo].[People].[id_site] as [p].[id_site])) | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Sites].[PK_Sites] AS [s]), ORDERED FORWARD) | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[id_country], [p].[id_region], [p].[id_district], [p].[id_site], [p].[id_street], [Expr1061]) WITH ORDERED PREFETCH) | | |--Sort(ORDER BY:([p].[id_country] ASC, [p].[id_region] ASC, [p].[id_district] ASC, [p].[id_site] ASC)) | | | |--Merge Join(Right Outer Join, MERGE:([d].[id_country], [d].[id_region], [d].[id_district])=([p].[id_country], [p].[id_region], [p].[id_district]), RESIDUAL:([Data].[dbo].[Districts].[id_country] as [d].[id_country]=[Data].[dbo].[People].[id_country] as [p].[id_country] AND [Data].[dbo].[Districts].[id_region] as [d].[id_region]=[Data].[dbo].[People].[id_region] as [p].[id_region] AND [Data].[dbo].[Districts].[id_district] as [d].[id_district]=[Data].[dbo].[People].[id_district] as [p].[id_district])) | | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Districts].[PK_Districts] AS [d]), ORDERED FORWARD) | | | |--Sort(ORDER BY:([p].[id_country] ASC, [p].[id_region] ASC, [p].[id_district] ASC)) | | | |--Compute Scalar(DEFINE:([Expr1049]=[Data].[dbo].[Regions].[name_region] as [r].[name_region]+' ')) | | | |--Hash Match(Right Outer Join, HASH:([r].[id_country], [r].[id_region])=([p].[id_country], [p].[id_region]), RESIDUAL:([Data].[dbo].[Regions].[id_country] as [r].[id_country]=[Data].[dbo].[People].[id_country] as [p].[id_country] AND [Data].[dbo].[Regions].[id_region] as [r].[id_region]=[Data].[dbo].[People].[id_region] as [p].[id_region])) | | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Regions].[PK_Regions] AS [r])) | | | |--Hash Match(Right Outer Join, HASH:([c].[id_country])=([p].[id_country]), RESIDUAL:([Data].[dbo].[Countries].[id_country] as [c].[id_country]=[Data].[dbo].[People].[id_country] as [p].[id_country])) | | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[Countries].[PK_Countries] AS [c])) | | | |--Compute Scalar(DEFINE:([Expr1031]=((([Data].[dbo].[People].[lastname] as [p].[lastname]+' ')+isnull([Data].[dbo].[People].[firstname] as [p].[firstname],'?'))+' ')+isnull([Data].[dbo].[People].[middlename] as [p].[middlename],'?'), [Expr1032]=CASE WHEN [Data].[dbo].[People].[gender] as [p].[gender]=(0) THEN 'М' ELSE 'Ж' END, [Expr1052]=CONVERT(int,right(CONVERT_IMPLICIT(varchar(12),[Data].[dbo].[People].[id_site] as [p].[id_site],0),(3)),0))) | | | |--Clustered Index Scan(OBJECT:([Data].[dbo].[People].[PK_People] AS [p]), WHERE:([Data].[dbo].[People].[place] as [p].[place]=(1))) | | |--Clustered Index Seek(OBJECT:([Data].[dbo].[Streets].[PK_Streets] AS [st]), SEEK:([st].[id_country]=[Data].[dbo].[People].[id_country] as [p].[id_country] AND [st].[id_region]=[Data].[dbo].[People].[id_region] as [p].[id_region] AND [st].[id_district]=[Data].[dbo].[People].[id_district] as [p].[id_district] AND [st].[id_site]=[Data].[dbo].[People].[id_site] as [p].[id_site] AND [st].[id_street]=[Data].[dbo].[People].[id_street] as [p].[id_street]) ORDERED FORWARD) | |--Index Seek(OBJECT:([Data].[dbo].[Cards].[IX_Cards_unique]), SEEK:([Data].[dbo].[Cards].[card] <= [Data].[dbo].[People].[card_i] as [p].[card_i]), WHERE:([Data].[dbo].[People].[card_i] as [p].[card_i]<=[Data].[dbo].[Cards].[card_e]) ORDERED FORWARD) |--Table Spool |--Assert(WHERE:(CASE WHEN [Expr1043]>(1) THEN (0) ELSE NULL END)) |--Stream Aggregate(DEFINE:([Expr1043]=Count(*), [Expr1044]=ANY([Expr1054]+[Data].[dbo].[Points].[rn_point] as [n2].[rn_point]))) |--Nested Loops(Inner Join, OUTER REFERENCES:([n1].[idp_sc])) |--Compute Scalar(DEFINE:([Expr1054]=[Data].[dbo].[Sites].[name_site] as [n1].[name_site]+' ')) | |--Clustered Index Seek(OBJECT:([Data].[dbo].[Sites].[PK_Sites] AS [n1]), SEEK:([n1].[id_country]=[Data].[dbo].[People].[id_country] as [p].[id_country] AND [n1].[id_region]=[Data].[dbo].[People].[id_region] as [p].[id_region] AND [n1].[id_district]=[Data].[dbo].[People].[id_district] as [p].[id_district] AND [n1].[id_site]=[Data].[dbo].[People].[id_site] as [p].[id_site]-CONVERT(int,right(CONVERT_IMPLICIT(varchar(12),[Data].[dbo].[People].[id_site] as [p].[id_site],0),(3)),0)) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([Data].[dbo].[Points].[PK_Points] AS [n2]), SEEK:([n2].[id_point]=[Data].[dbo].[Sites].[idp_sc] as [n1].[idp_sc]) ORDERED FORWARD)


gandalf-the-grey

для упрощенного запросаselect p.cardnumber,cards.cardfrom data..people pleft join data..cards on p.card_i between cards.card and cards.card_e where p.place=1
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[card_i]))
 |--Clustered Index Scan(OBJECT:([Data].[dbo].[People].[PK_People] AS [p]), WHERE:([Data].[dbo].[People].[place] as [p].[place]=(1)))
 |--Index Seek(OBJECT:([Data].[dbo].[Cards].[IX_Cards_unique]), SEEK:([Data].[dbo].[Cards].[card] <= [Data].[dbo].[People].[card_i] as [p].[card_i]), WHERE:([Data].[dbo].[People].[card_i] as [p].[card_i]<=[Data].[dbo].[Cards].[card_e]) ORDERED FORWARD)
И где тут обещанный TableSpool ?


gandalf-the-grey

для упрощенного запросаwhere p.place=1
Я ж говорил: скрывает важную информацию!


gandalf-the-grey

Хе-хе... Ваша "проблема" в "куче лефт джойнов", которые вы скрываете.
нет, упрощенный запрос также выолняется 9 секунд.если в запросе стоитwhere p.place=1 (это для 9-значных номеров) - выполняется менее секунды


gandalf-the-grey

Запрос то тривиальщина. Тупо нету нужных индексов.Начнем с place- нужен кластерный или покрывающий, так как вы тянете ВСЮ таблицу people.


gandalf-the-grey

Далее на[dbo].[Cards]нужон UNIQUE CLUSTERED ([card])... далее по списку.


gandalf-the-grey

В People на place и card_i индексы есть. Есть place+card_iВ Card на card+card_e и по отдельности тоже есть индексы. Пробовал уже в разных сочетаниях.


gandalf-the-grey

Glory,
И где тут обещанный TableSpool ?
он в реальном запросе.Но время выполнения реального и упрощенного совпадает - 9 секунд. В упрощенном67% NestedLoops и 32% IndexSeek


gandalf-the-grey

aleks2,и повторю, для place=2 (9-значные card_i) запрос выполняется менее 1 секунды.Тут дело, видимо, в том, что для 9-значных card_i в Card всего несколько диапазонов, а для 19-значных card_i в Card свой диапазон для каждой. Вот как ЭТО ускорить?


gandalf-the-grey

aleks2,и повторю, для place=2 (9-значные card_i) запрос выполняется менее 1 секунды.Тут дело, видимо, в том, что для 9-значных card_i в Card всего несколько диапазонов, а для 19-значных card_i в Card свой диапазон для каждой. Вот как ЭТО ускорить?
Блин, мало ли чо тебе КАЖЕТСЯ?Итак запрос:
select p.cardnumber, cards.card
from data..people p
left join data..cards с on p.card Like c.card 
where p.place=<b>1</b>
сделай индексы(Place, cardnumber) на peopleиunique(card) на cardsпотом покажи план.А потом будет казаться...