ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сетчик посещений - долго отрабатывается запрос
14 сообщений из 14, страница 1 из 1
Сетчик посещений - долго отрабатывается запрос
    #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:06:12
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #32050641
Фотография Lexis
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lexis
Участник
Выложи плиз результат:
dbcc showcontig ('Hits')
и, если можно, план запроса
...
Рейтинг: 0 / 0
16.09.2002, 10:49:00
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #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:37:05
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #32050657
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Лучше всего посмотреть и скопировать план из профайлера. Но наверное, всё-таки идёт скан по Hits/HitPage.
У вас на HitPage есть идекс-то?
...
Рейтинг: 0 / 0
16.09.2002, 11:40:44
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #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, 11:40:49
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #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:46:14
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #32050685
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Индекс для [Hits].[HitPage] сделайте.
...
Рейтинг: 0 / 0
16.09.2002, 12:48:19
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #32050753
SiBear
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SiBear
Гость
Нельзя, к сожалению, для HitPage сделать индекс, т.к. существует ограничение :
Total size of an index or primarykey cannot exceed 900 bytes...
...
Рейтинг: 0 / 0
16.09.2002, 15:09:48
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #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
16.09.2002, 16:54:15
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #32050866
Фотография АлексейК
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
АлексейК
Участник
Может попробовать полнотекстовый индкс ?
...
Рейтинг: 0 / 0
17.09.2002, 09:41:28
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #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, 17:52:16
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #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:35:53
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #32051132
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
А уж если вы сделаете индекс по UrlUnicStr кластерным...
читать как
А уж если вы сделаете индекс по HitPageShort кластерным...
...
Рейтинг: 0 / 0
17.09.2002, 18:37:52
| Ответить | Цитировать | Написать  
Сетчик посещений - долго отрабатывается запрос
    #32051245
SiBear
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SiBear
Гость
Попробовал в тесте как Вы посоветовали. И индекс сделал кластеным - увы, те же 20-25 секунд на обработку запроса уходит... :(
...
Рейтинг: 0 / 0
18.09.2002, 12:05:58
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сетчик посещений - долго отрабатывается запрос
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=32050685&tid=1820266]:
 0ms
get settings:
 9ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 43ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 227ms
total:  342ms
созд. / загр.: 342ms
x
x
Закрыть


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