Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сетчик посещений - долго отрабатывается запрос / 14 сообщений из 14, страница 1 из 1
16.09.2002, 10:06:12
    #32050635
SiBear
SiBear
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Доброго времени! Наверняка кто-нибудь делал счетчик посещений сайта на MSSQL и знаком с этой тематикой.
Исходные данные:
есть таблицы:
1)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE [Hits] (
	[NN] [bigint] IDENTITY ( 1 ,  1 ) NOT NULL ,
	[HitIp] [varchar] ( 15 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[HitPage] [varchar] ( 5000 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[RefPage] [varchar] ( 5000 ) COLLATE Cyrillic_General_CI_AS NULL ,
	CONSTRAINT [PK_Hits] PRIMARY KEY  NONCLUSTERED 
	(
		[NN]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


2)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE [pageviews] (
	UnicStr] [varchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[PageName] [varchar] ( 100 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[PageUrl] [varchar] ( 50 ) COLLATE Cyrillic_General_CI_AS NULL ,
	CONSTRAINT [PK_mapsviews] PRIMARY KEY  NONCLUSTERED 
	(
		UnicStr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Первая таблица служит для запоминания каждого хита, в том числи полный URL страницы, которую просматривают (HitPage) и ссылающуюся страницу (RefPage)
Интересуюет поле HitPage, которое заполняется подобными значениями:
http://www.domen.ru/folder1/script.cgi?bla-bla-bla

http://www.domen.ru/folder2/script.cgi?bla-bla-bla

http://www.domen.ru/folder3/script.cgi?bla-bla-bla

http://www.domen.ru/folder4/script.cgi?bla-bla-bla
и т.д. Около 30000 строк

Вторая таблица содержит что-то вроде:
"%/folder2/%", "Страница 2", "http://www.domen.ru/folder2/"
Всего около 50 строк

Наконец-то суть вопроса:
Надо определить какие страницы сколько раз показывались и сколько пользователей (DISTINCT HitIP) их просмотрели, т.е. запрос типа:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select
  PageName,
  PageUrl,
  COUNT (HitIP) AS Hits,
  COUNT (DISTINCT HitIP) AS Hosts
from
  PageViews,
  Hits
where
  HitPage LIKE UrlUnicStr
GROUP BY PageName, PageUrl
ORDER BY Hosts DESC


Все работает правильно, но очень долго (более 3-х минут, P-III-800, памяти хватает, MSSQL 2000). Самое главное, что такой-же запрос с подобными условиями в MySQL (3.46.хх под Windows) выполнялся за 3-5 секунд.

Может быть кто-то сможет помочь оптимизировать запрос (таблицы) и сократить время обработки до приемлимых показаний? Заранее спасибо
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 10:49:00
    #32050641
Lexis
Lexis
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Выложи плиз результат:
dbcc showcontig ('Hits')
и, если можно, план запроса
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 11:37:05
    #32050655
SiBear
SiBear
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
dbcc showcontig ('Hits'):
DBCC SHOWCONTIG scanning 'Hits' table...
Table: 'Hits' (850102069); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 2210
- Extents Scanned..............................: 278
- Extent Switches..............................: 277
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.64% [277:278]
- Extent Scan Fragmentation ...................: 1.80%
- Avg. Bytes Free per Page.....................: 277.7
- Avg. Page Density (full).....................: 96.57%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

А план запроса как я в форум могу выложить????
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 11:40:44
    #32050657
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Лучше всего посмотреть и скопировать план из профайлера. Но наверное, всё-таки идёт скан по Hits/HitPage.
У вас на HitPage есть идекс-то?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 11:40:49
    #32050658
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
А план запроса как я в форум могу выложить????

use pubs
go
set showplan_text on
go
select * from authors
go
set showplan_text off
go
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 12:46:14
    #32050684
SiBear
SiBear
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Спасибо, Glory
Вот план запроса:

Код: plaintext
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.
  | --Compute Scalar(DEFINE:([pageviews].[PageName]=[pageviews].[PageName], [pageviews].[PageUrl]=[pageviews].[PageUrl]))
 
       | --Nested Loops(Inner Join, WHERE:([pageviews].[PageName]=[pageviews].[PageName] AND [pageviews].[PageUrl]=[pageviews].[PageUrl]))
 
            | --Compute Scalar(DEFINE:([pageviews].[PageName]=[pageviews].[PageName], [pageviews].[PageUrl]=[pageviews].[PageUrl]))
 
            |    | --Sort(ORDER BY:([Expr1005] DESC))
 
            |         | --Compute Scalar(DEFINE:([Expr1005]=Convert([Expr1014])))
 
            |              | --Stream Aggregate(GROUP BY:([pageviews].[PageName], [pageviews].[PageUrl]) DEFINE:([Expr1014]=Count(*)))
 
            |                   | --Sort(ORDER BY:([pageviews].[PageName] ASC, [pageviews].[PageUrl] ASC))
 
            |                        | --Hash Match(Aggregate, HASH:([pageviews].[PageName], [pageviews].[PageUrl], [Hits].[HitIp]), RESIDUAL:(([pageviews].[PageName]=[pageviews].[PageName] AND [pageviews].[PageUrl]=[pageviews].[PageUrl]) AND [Hits].[HitIp]
 
            |                             | --Table Spool
 
            |                                  | --Nested Loops(Inner Join, WHERE:(like([Hits].[HitPage], [pageviews].UnicStr], NULL)))
 
            |                                       | --Table Scan(OBJECT:([Counters].[dbo].[Hits]))
 
            |                                       | --Table Spool
 
            |                                            | --Table Scan(OBJECT:([Counters].[dbo].[pageviews]))
 
            | --Table Spool
 
                 | --Compute Scalar(DEFINE:([pageviews].[PageName]=[pageviews].[PageName], [pageviews].[PageUrl]=[pageviews].[PageUrl]))
 
                      | --Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1015])))
 
                           | --Hash Match(Aggregate, HASH:([pageviews].[PageName], [pageviews].[PageUrl]), RESIDUAL:([pageviews].[PageName]=[pageviews].[PageName] AND [pageviews].[PageUrl]=[pageviews].[PageUrl]) DEFINE:([Expr1015]=COUNT(*)))
 
                                | --Table Spool
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 12:48:19
    #32050685
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Индекс для [Hits].[HitPage] сделайте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 15:09:48
    #32050753
SiBear
SiBear
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Нельзя, к сожалению, для HitPage сделать индекс, т.к. существует ограничение :
Total size of an index or primarykey cannot exceed 900 bytes...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.09.2002, 16:54:15
    #32050797
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
2 SiBear
Да, я что-то не обратил внимания... Кстати,
HitPage varchar(5000) и RefPage varchar(5000) тоже будут плохо сосуществовать в одной таблице...

По ускорению, собственно, 2 варианта.
1. Сделать поле HitPageShort - укороченный вариант HitPage для поиска. В запросе тогда будет
Код: plaintext
1.
where HitPageShort LIKE UrlUnicStr and HitPage LIKE UrlUnicStr

2. Пересмотреть структуру. Сделать таблицу, где будут храниться пересчитанные записи - и пересчитывать время от времени новые записи или менять при событии.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2002, 09:41:28
    #32050866
АлексейК
АлексейК
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Может попробовать полнотекстовый индкс ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2002, 17:52:16
    #32051115
SiBear
SiBear
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
to alexeyvg
Ты уверен, что запрос типа
Код: plaintext
where HitPageShort LIKE UrlUnicStr and HitPage LIKE UrlUnicStr

будет отрабатываться быстро? Я так понимаю, что HitPage остается изначальной длины (т.е. varchar(5000)). Значит и инструкция where будет обращаться сначала к "короткому" URL. и сразу же к "длинному". Или я не правильно читаю код???

По поводу урезания URL - хорошая идея. В любом случае, уникальные строки, по которым идентифицируются страницы где-то в начале URL.

В общем, вышел я из положения способом, который, увы мне не очень нравится. Сначала создается временная табличка с
HitPage varchar (200),
HitIP varchar (15)

в которую копируется таблица Hits, урезая HitPage до 200 и потом вся работа ведется с ней. Удалось с 3-х минут уменьшить время отработки до 25 секунд. Но все таки не понятно, почему же mySQL справлялся с этим без временных таблиц за 3-5 секунд!!!!!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2002, 18:35:53
    #32051131
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
2 SiBear
Уверен. В запросе используются индексы, насколько это возможно.
При запросе
where HitPageShort LIKE UrlUnicStr and HitPage LIKE UrlUnicStr
сервер сначала достанет поднабор HitPageShort LIKE UrlUnicStr (по индексу) а потом из него HitPage LIKE UrlUnicStr.
А при запросе
where [NN] = @nn and HitPageShort LIKE UrlUnicStr and HitPage LIKE UrlUnicStr
сервер сначала достанет запись по [NN] ( т.к. там - ПК и уникальный индекс) а потом проверит её на HitPageShort LIKE UrlUnicStr and HitPage LIKE UrlUnicStr.

А уж если вы сделаете индекс по UrlUnicStr кластерным... То возможно быстродействие будет приближаться даже к MySQL (3.46.хх под Windows) :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2002, 18:37:52
    #32051132
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
А уж если вы сделаете индекс по UrlUnicStr кластерным...
читать как
А уж если вы сделаете индекс по HitPageShort кластерным...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2002, 12:05:58
    #32051245
SiBear
SiBear
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сетчик посещений - долго отрабатывается запрос
Попробовал в тесте как Вы посоветовали. И индекс сделал кластеным - увы, те же 20-25 секунд на обработку запроса уходит... :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сетчик посещений - долго отрабатывается запрос / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 394ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1820266]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 48ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 66ms
get tp. blocked users:
 2ms
others: 231ms
total:  394ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]