MS SQL Server 2000, вопросы производительности

kokoc88

Имеются следующие объекты на сервере. (Они генерируются Hibernate.) Таблица описания карты клиента, размер таблицы для тестирования - 100.000 строк, clustered index по столбцу id.


CREATE TABLE [Card] (

	[id] [uniqueidentifier] NOT NULL ,

	[deleted] [tinyint] NOT NULL ,

	[created] [datetime] NULL ,

	[modified] [datetime] NULL ,

	[registeredinfo_date] [datetime] NULL ,

	[registeredinfo_user] [uniqueidentifier] NULL ,

	[activatedinfo_date] [datetime] NULL ,

	[activatedinfo_user] [uniqueidentifier] NULL ,

	[modifiedinfo_date] [datetime] NULL ,

	[modifiedinfo_user] [uniqueidentifier] NULL ,

	[blockedinfo_date] [datetime] NULL ,

	[blockedinfo_user] [uniqueidentifier] NULL ,

	[datesold] [datetime] NULL ,

	[restaurantsold] [uniqueidentifier] NULL ,

	[employeesold] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[expirationdate] [datetime] NULL ,

	[number] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[firstusedate] [datetime] NULL ,

	[status] [int] NULL ,

	[blocked] [tinyint] NOT NULL ,

	[blockedreason] [text] COLLATE Cyrillic_General_CI_AS NULL ,

	[surname] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[name] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[patronymic] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[gender] [int] NULL ,

	[address] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[scopeofactivity] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[smoking] [tinyint] NULL ,

	[phone] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[cellphone] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[email] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[codeword] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[birthdate] [datetime] NULL ,

	[wishsubscribeforbesmagazine] [tinyint] NULL ,

	 PRIMARY KEY  CLUSTERED 

	(

		[id]

	)  ON [PRIMARY] 

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Таблица описания транзакции по карте, размер таблицы для тестирования - 20.000.000 строк.
CREATE TABLE [CardEvent] (

	[DTYPE] [varchar] (8) COLLATE Cyrillic_General_CI_AS NOT NULL ,

	[id] [uniqueidentifier] NOT NULL ,

	[date] [datetime] NOT NULL ,

	[eventtype] [varchar] (5) COLLATE Cyrillic_General_CI_AS NOT NULL ,

	[card] [uniqueidentifier] NULL ,

	[user] [uniqueidentifier] NULL ,

	[comment] [varchar] (4096) COLLATE Cyrillic_General_CI_AS NULL ,

	[restaurant] [uniqueidentifier] NULL ,

	[sum] [numeric](19, 9) NULL ,

	[cashnumber] [int] NULL ,

	[chequenum] [int] NULL 

) ON [PRIMARY]

GO

Индекс для таблицы CardEvent по date, card, eventtype, sum.


 CREATE  INDEX [CardEvent1] ON [dbo].[CardEvent]([date], [card], [eventtype], [sum]) ON [PRIMARY]

GO

Стлобец eventtype бывает либо 'pac', дибо 'dap'. Задача - написать запрос, который подсчитает количество 'pac', сумму столбца sum отдельно для 'pac' и 'dap', выберет наибольшую date у всех 'pac' с группировкой по id карты клиента. В том же запросе надо выбрать ф.и.о. клиента и номер карты, всё это за некоторый период времени, определённый date в таблице транзакций. Собственно, вопрос в следующем. Если писать запрос вот так:


select trans_join.*, crd_join.number, crd_join.surname, crd_join.[name], crd_join.patronymic

from 

(

select	sum(case when eventtype='pac' then 1 else 0 end) as visits,

	sum(case when eventtype='pac' then [sum] else 0 end) as payments,

	sum(case when eventtype='dap' then [sum] else 0 end) as discounts,

	max(case when eventtype='pac' then [date] else 0 end) as lastVisit,

	crd.[id] as card

from CardEvent ce, Card crd

where [date] between '01.01.2005' and '03.01.2005' and ce.card=crd.[id]

group by crd.[id]

) trans_join



inner join Card crd_join



on crd_join.[id]=trans_join.card

Запрос будет выполняться примерно в пять раз быстрее, чем вот так:


select trans_join.*, crd_join.number, crd_join.surname, crd_join.[name], crd_join.patronymic

from 

(

select	sum(case when eventtype='pac' then 1 else 0 end) as visits,

	sum(case when eventtype='pac' then [sum] else 0 end) as payments,

	sum(case when eventtype='dap' then [sum] else 0 end) as discounts,

	max(case when eventtype='pac' then [date] else 0 end) as lastVisit,

	ce.card

from CardEvent ce

where [date] between '01.01.2005' and '03.01.2005'

group by ce.card

) trans_join



inner join Card crd_join



on crd_join.[id]=trans_join.card

Eugenia_2005

Так а вопрос-то в чем?

6yrop

Покажи планы запросов. Возможно, придется поставить хинт на последний джоин.

kokoc88

Так а вопрос-то в чем?
Найди слова "Собственно, вопрос в следующем."

Eugenia_2005

То есть ты хочешь узать почему один запрос быстрее другого в пять раз?
Тогда давай план запроса, без него будут лишь догадки

bastii

ага, а лучше скрипт, чтобы у себя твою тестовую ситуацию погонять

kokoc88

Тогда давай план запроса, без него будут лишь догадки
Блин, как бы его ещё в удобном виде получить... Как придумаю, напишу. В общем-то, по плану всё не так должно быть. Пока что приведу статистику.
Вот запрос с join внутри:
(99962 row(s) affected)
Table 'Card'. Scan count 2, logical reads 4538, physical reads 0, read-ahead reads 0.
Table 'CardEvent'. Scan count 1, logical reads 11023, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 14781 ms, elapsed time = 20781 ms.
Вот запрос без join внутри:
(99962 row(s) affected)
Table 'Card'. Scan count 1, logical reads 4034, physical reads 0, read-ahead reads 0.
Table 'CardEvent'. Scan count 1, logical reads 11023, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 20125 ms, elapsed time = 132753 ms.

bastii

сделай команду SET SHOWPLAN_TEXT ON

kokoc88

сделай команду SET SHOWPLAN_TEXT ON
А толку только от текста-то?
Edit Хотя, мб и есть... Ща запостю.

kokoc88

Запрос с джойном внутри:
  |--Hash Match(Inner Join, HASH:([crd].[id])=([crd_join].[id] RESIDUAL:([crd].[id]=[crd_join].[id]

       |--Hash Match(Inner Join, HASH:([ce].[card])=([crd].[id] RESIDUAL:([crd].[id]=[ce].[card]

       |    |--Compute Scalar(DEFINE:([Expr1003]=If ([Expr1029]=0) then NULL else [Expr1030], [Expr1004]=If ([Expr1031]=0) 

then NULL else [Expr1032]

       |    |    |--Hash Match(Aggregate, HASH:([ce].[card] RESIDUAL:([ce].[card]=[ce].[card]) DEFINE:([Expr1002]=SUM(If ([ce].

[eventtype]='pac') then 1 else 0 [Expr1029]=COUNT_BIG(If ([ce].[eventtype]='pac') then [ce].[sum] else 0.000000000 

[Expr103

       |    |         |--Index Seek(OBJECT:([RestoCRM].[dbo].[CardEvent].[CardEventIndex] AS [ce] SEEK:([ce].[date] >= 'Jan  1 

2005 12:00AM' AND [ce].[date] <= 'Mar  1 2005 12:00AM') ORDERED FORWARD)

       |    |--Index Scan(OBJECT:([RestoCRM].[dbo].[Card].[Card_number] AS [crd]

       |--Clustered Index Scan(OBJECT:([RestoCRM].[dbo].[Card].[PK__Card__76CBA758] AS [crd_join]

И без джойна, который выполняется в разы медленнее:
  |--Hash Match(Inner Join, HASH:([ce].[card])=([crd_join].[id] RESIDUAL:([ce].[card]=[crd_join].[id]

       |--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1020]=0) then NULL else [Expr1021], [Expr1003]=If ([Expr1022]=0) then

 NULL else [Expr1023]

       |    |--Hash Match(Aggregate, HASH:([ce].[card] RESIDUAL:([ce].[card]=[ce].[card]) DEFINE:([Expr1001]=SUM(If ([ce].

[eventtype]='pac') then 1 else 0 [Expr1020]=COUNT_BIG(If ([ce].[eventtype]='pac') then [ce].[sum] else 0.000000000 [Expr1021]=SU

       |         |--Index Seek(OBJECT:([RestoCRM].[dbo].[CardEvent].[CardEventIndex] AS [ce] SEEK:([ce].[date] >= 'Jan  1 2005 

12:00AM' AND [ce].[date] <= 'Mar  1 2005 12:00AM') ORDERED FORWARD)

       |--Clustered Index Scan(OBJECT:([RestoCRM].[dbo].[Card].[PK__Card__76CBA758] AS [crd_join]
Оставить комментарий
Имя или ник:
Комментарий: