Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
Здравствуйте. Ситуация такова.
Жил был скрипт, скрипт делает отчеты за день, в скрипте есть SQL запрос в виде
SELECT TOP 350000 table1.*, table2.8 FROM table1 JOIN table2 on table1.deviceid = table2.deviceid
WHERE clientid = '123';
Количество записей 350000 - примерно соответствует общему количеству записей за день(немногим больше).
Время отклика скрипта примерно 3 минуты.
Появилась задача сделать модуль который будет строить отчет за неделю, прежним количеством записей уже не обойтись, если исходить из логики то нужно увеличить количество записей с 350 ти тыщ 2.5 миллионов, пробовал и такое, но запрос отрабатывает почти час, вопрос следующий, как выбрать необходимое количество записей без потери производительности?
26 фев 12, 19:51    [12153607]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
без потерь вообще не выдет, т.к. вы такой объём "тащите", а вообще
1) Секционировать таблицу
2) Построить индексы
26 фев 12, 20:25    [12153667]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
Секционировать таблицу - разложить данные по разным таблицам ? те что постарее в одну те что посвежей в другую?
26 фев 12, 20:31    [12153679]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
turbocharged
Секционировать таблицу - разложить данные по разным таблицам ? те что постарее в одну те что посвежей в другую?


Можно и так, но я про http://msdn.microsoft.com/ru-ru/library/ms188730.aspx
26 фев 12, 20:34    [12153691]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
sti
Member

Откуда:
Сообщений: 769
А у вас там так и есть на самом деле table1.*,table2.* ?
Тогда перечислите только нужные поля хотя бы, уже размер датасета поменьше будет.

Ну а так, решение может быть в том, чтобы данные уже в запросе как-то группировать, а не выдавать сырой набор.
26 фев 12, 20:35    [12153695]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
А у вас там так и есть на самом деле table1.*,table2.* ?
Тогда перечислите только нужные поля хотя бы, уже размер датасета поменьше будет.
Да так и есть выбирает все поля, пробовал выбирать только необходимые мне пять полей из всего 20-ти, по почему то возрастает время выборки, с 4 минут до почти получаса. Так же пробовал делать условие WHERE convert(varchar(12),TimeStamp,111) BETWEEN '$monthago' AND '$today' получаю примерно 2 миллиона записей с скоростью выборки примерно 20 минут
Ну а так, решение может быть в том, чтобы данные уже в запросе как-то группировать, а не выдавать сырой набор. Можно делать Либо выдирая из базы сырой набор либо в цикле выдирать из базы по несколько маленьких запросов, на практике куда медленней(до двух часов).
26 фев 12, 20:42    [12153730]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
sti
Member

Откуда:
Сообщений: 769
turbocharged
пробовал выбирать только необходимые мне пять полей из всего 20-ти, по почему то возрастает время выборки

Ой, не верю! У вас может в одном случае данные в кеше были, а в другом нет.
26 фев 12, 20:50    [12153767]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
sti
Member

Откуда:
Сообщений: 769
turbocharged
Можно делать Либо выдирая из базы сырой набор либо в цикле выдирать из базы по несколько маленьких запросов, на практике куда медленней(до двух часов).

Я говорил совсем не об этом. Например, если у вас 100 записей о продаже 1 штуки товара, то для статистики эквивалентной может быть одна запись с количеством 100.
26 фев 12, 20:55    [12153788]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
sti
turbocharged
пробовал выбирать только необходимые мне пять полей из всего 20-ти, по почему то возрастает время выборки

Ой, не верю! У вас может в одном случае данные в кеше были, а в другом нет.

А. ну да ну да.
26 фев 12, 21:22    [12153867]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
aleks2
Guest
Knyazev Alexey
без потерь вообще не выдет, т.к. вы такой объём "тащите", а вообще
1) Секционировать таблицу
2) Построить индексы


И с какого бодуна секционирование ускорит выборку "2.5 миллионов" записей?
Даже сама MS такого никогда не сулила.

turbocharged
как выбрать необходимое количество записей без потери производительности?

1. Никак. Ибо MS SQL чудес творить не способен.
2. Надо отчеты по длительным периодам - считайте и храните. При нужде - пересчитывайте.
3. Т.е. надо недельные - считаем и храним ежедневные. По ним строим за неделю. Бонус - за день будет выдаваться не за три минуты, а за три секунды.
27 фев 12, 05:57    [12154621]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
turbocharged
Здравствуйте. Ситуация такова.
Жил был скрипт, скрипт делает отчеты за день, в скрипте есть SQL запрос в виде
SELECT TOP 350000 table1.*, table2.8 FROM table1 JOIN table2 on table1.deviceid = table2.deviceid
WHERE clientid = '123';
Количество записей 350000 - примерно соответствует общему количеству записей за день(немногим больше).
Время отклика скрипта примерно 3 минуты.
Появилась задача сделать модуль который будет строить отчет за неделю, прежним количеством записей уже не обойтись, если исходить из логики то нужно увеличить количество записей с 350 ти тыщ 2.5 миллионов, пробовал и такое, но запрос отрабатывает почти час, вопрос следующий, как выбрать необходимое количество записей без потери производительности?
Какой пользователь в здравом уме будет просматривать отчет из 2.5 миллионов записей??
27 фев 12, 09:04    [12154825]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Титунин Петр
Member

Откуда:
Сообщений: 25
У меня больше опыт по MySql. Тоже вызывает подозрение - зачем может понадобиться такой обьем. Отчет 1-3 стр, чтобы человеч сознание принимало. А как вариант - использовать просмотрщики (View) которые по мере жизни этих таблиц, будет представлять требуюмую информацию.
27 фев 12, 09:50    [12154990]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
Ок. 2 миллиона записей не нужны, есть таблица в таблице есть дата и признак события(0,1,2,3) Нужно считать от одного события до другого(История движения автомобиля к примеру из точки а в точку б)
27 фев 12, 11:43    [12155607]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
Так же в этой же таблице есть координаты, в одном отчете нужно считать расстояние(маршрут)
27 фев 12, 11:44    [12155619]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
aleks2
Knyazev Alexey
без потерь вообще не выдет, т.к. вы такой объём "тащите", а вообще
1) Секционировать таблицу
2) Построить индексы


И с какого бодуна секционирование ускорит выборку "2.5 миллионов" записей?
Даже сама MS такого никогда не сулила.

turbocharged
как выбрать необходимое количество записей без потери производительности?

1. Никак. Ибо MS SQL чудес творить не способен.
2. Надо отчеты по длительным периодам - считайте и храните. При нужде - пересчитывайте.
3. Т.е. надо недельные - считаем и храним ежедневные. По ним строим за неделю. Бонус - за день будет выдаваться не за три минуты, а за три секунды.


Мысль безусловно верная. Но скрипт писан на пхп а пхп как известно работает по запросу, да и суть скрипта такова, работать по запросу, пхп конечно умеет работать и из консоли по расписанию к примеру из крона, а то о чем вы говорите лично я бы лучше писал на си, хотябы исходя из соображения производительности. Ну а это уже совсем другая история.
27 фев 12, 12:05    [12155785]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Glory
Member

Откуда:
Сообщений: 104751
turbocharged
Ок. 2 миллиона записей не нужны, есть таблица в таблице есть дата и признак события(0,1,2,3) Нужно считать от одного события до другого(История движения автомобиля к примеру из точки а в точку б)

Ну так посчитайте и верните клиенту 1 запись, а не 350тыс.
27 фев 12, 12:13    [12155849]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
Glory
turbocharged
Ок. 2 миллиона записей не нужны, есть таблица в таблице есть дата и признак события(0,1,2,3) Нужно считать от одного события до другого(История движения автомобиля к примеру из точки а в точку б)

Ну так посчитайте и верните клиенту 1 запись, а не 350тыс.

Я и не возвращаю 350 тысяч.
Вот пример мы считаем маршрут от сейчас до даты по времени указанной пользователем. Для этого нам надо выбрать все точки от сейчас до указанной даты и между ними посчитать расстояния а затем сложить. Можно конечно считать через точку или через тр точки но точность тогда теряется. Есть другие предложения как это реализовать?
27 фев 12, 12:17    [12155889]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Glory
Member

Откуда:
Сообщений: 104751
turbocharged
Я и не возвращаю 350 тысяч.

Да что вы говорите !
А что тогда по- вашему делает запрос

SELECT TOP 350000 table1.*, table2.8 FROM table1 JOIN table2 on table1.deviceid = table2.deviceid
WHERE clientid = '123';

?

turbocharged
Вот пример мы считаем маршрут от сейчас до даты по времени указанной пользователем. Для этого нам надо выбрать все точки от сейчас до указанной даты и между ними посчитать расстояния а затем сложить. Можно конечно считать через точку или через тр точки но точность тогда теряется. Есть другие предложения как это реализовать?

- Реализовать суммирования без сложения нельзя
- Точность рассчетов определяется Техническим заданием
27 фев 12, 12:20    [12155918]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
turbocharged
...
WHERE convert(varchar(12),TimeStamp,111) BETWEEN '$monthago' AND '$today'
...

никогда так не делайте, так как в этом случае нет никаких шансов, что будет использоваться индекс по TimeStamp.
сформируйте нормальные $monthago и $today в пхп.
hint:
$monthago = date('Ymd H:i:s', strtotime('last month'));
$today = date('Ymd H:i:s');
27 фев 12, 13:12    [12156415]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
edyaN
turbocharged
...
WHERE convert(varchar(12),TimeStamp,111) BETWEEN '$monthago' AND '$today'
...

никогда так не делайте, так как в этом случае нет никаких шансов, что будет использоваться индекс по TimeStamp.
сформируйте нормальные $monthago и $today в пхп.
hint:
$monthago = date('Ymd H:i:s', strtotime('last month'));
$today = date('Ymd H:i:s');

Спасибо за совет, давно догадывался что конверт вредит производительности. Но на практике ничем не подтверждалось
27 фев 12, 14:16    [12157059]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
aleks2
Knyazev Alexey
без потерь вообще не выдет, т.к. вы такой объём "тащите", а вообще
1) Секционировать таблицу
2) Построить индексы


И с какого бодуна секционирование ускорит выборку "2.5 миллионов" записей?
Даже сама MS такого никогда не сулила.

а почему бы и нет...секционировать по N-часов, секции разложить по разным дискам
27 фев 12, 15:46    [12157917]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
turbocharged
Спасибо за совет, давно догадывался что конверт вредит производительности. Но на практике ничем не подтверждалось
Это касается любого выражения по значению поля, а не только CONVERT.
Ведь значения выражений в индексе не хранятся, а значит, и поиск в индексе невозможен.
Вот если сделать выражение вычисляемым полем, а по этому полю построить индекс...
Но тогда в запросе вместо выражения надо будет использовать имя вычисляемого поля.

Лучше предикаты стараться применять непосредственно к полям таблицы.
27 фев 12, 15:55    [12158018]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
И все же. Почему скорость упала после укзания определенных полей? Потому что этих записей нет в кеше? как сделать так чтобы они кешировались?
27 фев 12, 20:09    [12160197]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
turbocharged
И все же. Почему скорость упала после укзания определенных полей? Потому что этих записей нет в кеше? как сделать так чтобы они кешировались?

Вы не пробовали смотреть планы запросов? Тут не экстрасенсы сидят. Нам нужны планы, скрипты таблиц, индексов, запрос который вы хотите оптимизировать. Тогда можно будет о чем то говорить.
27 фев 12, 20:23    [12160255]     Ответить | Цитировать Сообщить модератору
 Re: SELECT TOP 350000 как ускорить?  [new]
turbocharged
Member

Откуда:
Сообщений: 22
Скрипт создания таблиц

1)

USE [tavl2]
GO

/****** Object: Table [tavl].[Message] Script Date: 02/27/2012 18:25:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [tavl].[Message](
[MessageId] [bigint] IDENTITY(1,1) NOT NULL,
[ObjectId] [int] NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[VectorAngle] [int] NOT NULL,
[VectorSpeed] [int] NOT NULL,
[Altitude] [int] NOT NULL,
[GpsTime] [datetime] NOT NULL,
[VisibleSatelites] [int] NOT NULL,
[X] [float] NOT NULL,
[Y] [float] NOT NULL,
[Valid] [bit] NOT NULL,
[Canal] [bit] NOT NULL,
CONSTRAINT [Message_PK] PRIMARY KEY NONCLUSTERED
(
[MessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [tavl].[Message] WITH CHECK ADD CONSTRAINT [Object_Message_FK1] FOREIGN KEY([ObjectId])
REFERENCES [tavl].[Object] ([ObjectId])
GO

ALTER TABLE [tavl].[Message] CHECK CONSTRAINT [Object_Message_FK1]
GO

ALTER TABLE [tavl].[Message] ADD CONSTRAINT [DF__Message__TimeSta__3B75D760] DEFAULT (getdate()) FOR [TimeStamp]
GO

ALTER TABLE [tavl].[Message] ADD CONSTRAINT [DF_Message_Valid] DEFAULT ((1)) FOR [Valid]
GO

ALTER TABLE [tavl].[Message] ADD CONSTRAINT [DF__Message__Canal__5090EFD7] DEFAULT ((1)) FOR [Canal]
GO

2)

USE [tavl2]
GO

/****** Object: Table [tavl].[Object] Script Date: 02/27/2012 18:27:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [tavl].[Object](
[ObjectId] [int] IDENTITY(1,1) NOT NULL,
[ClientId] [int] NOT NULL,
[Number] [nvarchar](20) NULL,
[Comment] [nvarchar](200) NULL,
[MobileProfileId] [int] NULL,
[Deleted] [bit] NOT NULL,
[DeleteTime] [datetime] NULL,
[Enabled] [bit] NOT NULL,
[ObjectCode] [varchar](10) NULL,
CONSTRAINT [Object_PK] PRIMARY KEY NONCLUSTERED
(
[ObjectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [tavl].[Object] WITH CHECK ADD CONSTRAINT [Client_Object_FK1] FOREIGN KEY([ClientId])
REFERENCES [tavl].[Client] ([ClientId])
GO

ALTER TABLE [tavl].[Object] CHECK CONSTRAINT [Client_Object_FK1]
GO

ALTER TABLE [tavl].[Object] WITH CHECK ADD CONSTRAINT [Object_MobileProfile_FK] FOREIGN KEY([MobileProfileId])
REFERENCES [tavl].[MobileProfile] ([MobileProfileId])
GO

ALTER TABLE [tavl].[Object] CHECK CONSTRAINT [Object_MobileProfile_FK]
GO

ALTER TABLE [tavl].[Object] ADD CONSTRAINT [DF__Object__Comment__36B12243] DEFAULT ('') FOR [Comment]
GO

ALTER TABLE [tavl].[Object] ADD CONSTRAINT [DF__Object__Deleted__52793849] DEFAULT ((0)) FOR [Deleted]
GO

ALTER TABLE [tavl].[Object] ADD DEFAULT ((1)) FOR [Enabled]
GO




Скрипт запроса


SELECT TOP 20000 [tavl2].[tavl].[Message].MessageId, [tavl2].[tavl].[Message].X, [tavl2].[tavl].[Message].Y, [tavl2].[tavl].[Message].VectorSpeed, [tavl2].[tavl].[Message].GpsTime, [tavl2].[tavl].[Object].ObjectId, [tavl2].[tavl].[Object].Number FROM [tavl2].[tavl].[Object] JOIN [tavl2].[tavl].[Message] ON [tavl2].[tavl].[Object].ObjectId = [tavl2].[tavl].[Message].ObjectId WHERE ClientId = '182'

Данный запрос отрабатывает пол часа. если вместо столбцов делаю * то отрабатывает за пару секунд
27 фев 12, 20:29    [12160266]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить