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

Откуда:
Сообщений: 56
Требуется, чтобы при приходе новых записей в БД, БД уведомляла второе приложение. Второе приложение на .Net, подключается к БД через DataContext. Можно ли это сделать, и как?

Теоритически так сделать, вроде, можно, но пишут что это сложно и нестабильно, и лучше просто из второго приложения периодически делать запросы к БД. Но у меня проблема в том, что уже даже при пяти запросах в секунду, и в итоге sqlsrevr.exe очень сильно грузит проц.

Алгоритм такой, что приложение хранит последнее время обновления и делает по таймеру пять запросов к БД в секунду (о новых данных), и в случае, если время последней записи в БД больше чем сохраненное приложением последнее время, считается, что в БД есть новые данные и приложение делает уже сам рабочий запрос, после которого переменной времени последнего обновления в приложении присвается = время последней записи, только что полученной из БД в рабочем запросе.

Формально работает, но проц грузится очень сильно. Может, я просто неправильно работаю с соединениями? Или сам запрос написан неоптимально? Ведь не должно же так грузить (стабильно пол ядра коре2дуо Е8200)? Грузит именно нижеприведенный запрос, который я делаю, чтобы узнать время последней записи в таблице. При каждом запросе я открываю новое соединение через DataContext:

using (var dc = new MyDataBaseDataContext())
                    {
                        var queryLastTime = (from p in dc.All
                                                  where p.Код.Contains(_code)
                                                  where p.Дата.Date == _date
                                                  select p.Время).Max();

                    }
29 июн 11, 17:06    [10894688]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Questq2, service broker
29 июн 11, 17:10    [10894727]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Questq2
Member

Откуда:
Сообщений: 56
kDnZP, попробую разобраться....

А пять приведенных запросов в секунду могут так сильно грузить проц?
29 июн 11, 17:37    [10895047]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Questq2, то что вы привели, это не SQL запрос
запрос, который ваша программа отправляет на сервер, можно посмотреть профайлером
кстати, для больших таблиц надо убедиться что есть индексы по полям, используемым в where
ну и если запрашиваете несколько раз в секунду, почему бы не держать одно соединение открытым вместо создания нового каждый раз
29 июн 11, 17:53    [10895211]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Questq2
Member

Откуда:
Сообщений: 56
Shakill,

1. Запрос выглядит так:

SELECT [t0].[Дата], [t0].[Время], [t0].[времяизвнешнегоПО], [t0].[Код], [t0].[Номер], [t0].[Цена], [t0].[Кол_во],
FROM [dbo].[All] AS [t0]
WHERE (([t0].[Время]) <= @p0) AND (([t0].[Время]) > @p1) AND (CONVERT(DATE, [t0].[Дата]) = @p2) AND ([t0].[Код] LIKE @p3)

Поясню - внешнееПО не может понять формата БД time, поэтому по рекомендации разработчика внешнего ПО я вывожу время в поле БД с форматом varchar(8), а затем использую вычислямый столбец для преобразования в time. Поэтому у меня два поля про время: [t0].[Время] и [t0].[времяизвнешнегоПО]. Возможно, это влияет на скорость.

2. Индекс кластеризированный, вот такой:

CREATE CLUSTERED INDEX [IX_All] ON [dbo].[All] 
(
	[Дата] ASC,
	[времяизвнешнегоПО] ASC,
	[Кол_во] ASC,
	[Цена] ASC
)

3. С единственным соединением разобрался, но на производительность это не повлияло. Высокая загрузка проца осталась и при одном соединении DataContext.
29 июн 11, 23:40    [10896299]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Questq2
Высокая загрузка проца осталась и при одном соединении DataContext.
Ну так у вас скан таблицы, без индексов. Конечно, будет медленно.
29 июн 11, 23:45    [10896307]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
ЕвгенийВ
Member

Откуда: Москва
Сообщений: 4994
При таком запросе
using (var dc = new MyDataBaseDataContext())
                    {
                        var queryLastTime = (from p in dc.All
                                                  where p.Код.Contains(_code)
                                                  where p.Дата.Date == _date
                                                  select p.Время).Max();

                    }
Такого никогда не получиться
Questq2
Shakill,

1. Запрос выглядит так:

SELECT [t0].[Дата], [t0].[Время], [t0].[времяизвнешнегоПО], [t0].[Код], [t0].[Номер], [t0].[Цена], [t0].[Кол_во],
FROM [dbo].[All] AS [t0]
WHERE (([t0].[Время]) <= @p0) AND (([t0].[Время]) > @p1) AND (CONVERT(DATE, [t0].[Дата]) = @p2) AND ([t0].[Код] LIKE @p3)

where p.Код.Contains(_code) перейдет в ([t0].[Код] LIKE @p3), лучше использовать равенство и индекс по "Код" и всем полям, которые учавствуют в условиях на выборку.
30 июн 11, 00:01    [10896357]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Questq2
Member

Откуда:
Сообщений: 56
ЕвгенийВ, да, я тут перепутал, второй код SQL этой мой запрос данных, а первый код LINQ , это запрос, чтобы узнать, надо ли делать запрос данных.

alexeyvg, а как сделать, чтобы было быстро? Я неправильно создал индексы?

Вот сама таблица:

CREATE TABLE [dbo].[All](
	[Дата] [date] NOT NULL,
	[Время]  AS (CONVERT([time](0),[времяивнешнегоПО],(0))),
	[времяивнешнегоПО] [varchar](15) NOT NULL,
	[Код] [varchar](12) NOT NULL,
	[Номер] [decimal](15, 0) NOT NULL,
	[Цена] [decimal](15, 6) NOT NULL,
	[Количество] [decimal](15, 2) NOT NULL,
 CONSTRAINT [PK_All] PRIMARY KEY NONCLUSTERED 
(
	[Номер] ASC


То есть праймари кей по уникальному полю (номер), и кластеризированный индекс по полям, используемым в запросе.
30 июн 11, 04:55    [10896674]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Questq2
ЕвгенийВ, да, я тут перепутал, второй код SQL этой мой запрос данных, а первый код LINQ , это запрос, чтобы узнать, надо ли делать запрос данных.

alexeyvg, а как сделать, чтобы было быстро? Я неправильно создал индексы?

Вот сама таблица:

CREATE TABLE [dbo].[All](
	[Дата] [date] NOT NULL,
	[Время]  AS (CONVERT([time](0),[времяивнешнегоПО],(0))),
	[времяивнешнегоПО] [varchar](15) NOT NULL,
	[Код] [varchar](12) NOT NULL,
	[Номер] [decimal](15, 0) NOT NULL,
	[Цена] [decimal](15, 6) NOT NULL,
	[Количество] [decimal](15, 2) NOT NULL,
 CONSTRAINT [PK_All] PRIMARY KEY NONCLUSTERED 
(
	[Номер] ASC

То есть праймари кей по уникальному полю (номер), и кластеризированный индекс по полям, используемым в запросе.

Первый вариант:
создаёте индекс по [Дата], меняете условие CONVERT(DATE, [t0].[Дата]) = @p2 на [t0].[Дата] = @p2
Если данных по одной дате немного, то этого достаточно.

Если данных много, то нужно по возможности использовать индексацию по [Время] и [Код]

Первое можно сделать, пометив вычисляемую колонку как PERSISTED

Второе можно сделать, если поиск идёт не с середины строки (т.е. в начале @p3 нету %)

Соответственно, эти поля нужно включить в индекс после поля [Дата]
30 июн 11, 08:24    [10896841]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Может, стоит, всё-таки, кластерный индекс сделать?
30 июн 11, 09:40    [10897094]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
iap
Может, стоит, всё-таки, кластерный индекс сделать?
У Questq2 он как раз кластерный. Только из неправильных полей (кроме даты), и не используется, даже по дате :-)
30 июн 11, 09:49    [10897150]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
-=DiM@n=-
Member

Откуда: Москва
Сообщений: 1564
А если писать айдишники новых записей в отдельную таблицу. Второе приложение смотрит на эту таблу, и копируя данные по этим айдишникам, стирая скопированные айдишники
30 июн 11, 10:09    [10897277]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
-=DiM@n=-
Member

Откуда: Москва
Сообщений: 1564
а, не увидел, в таблице нет уникального поля. Может добавить его.
30 июн 11, 10:10    [10897292]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
-=DiM@n=-
а, не увидел, в таблице нет уникального поля. Может добавить его.
Как нет???
А это:
CONSTRAINT [PK_All] PRIMARY KEY NONCLUSTERED 
(
	[Номер] ASC
)
30 июн 11, 11:04    [10897705]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Как вариант
"Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value and, therefore, changes the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read. To return the current timestamp value for a database, use @@DBTS.

"
30 июн 11, 11:11    [10897787]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Questq2
Member

Откуда:
Сообщений: 56
alexeyvg, спасибо. В поле "Код" примерно 15 кодов. Каждый день (каждую [Дата] ) по каждому коду добавляется примерно 30 тысяч записей (то есть суммарно за одну дату по всем кодам в БД находится примерно 500 тыс записей), значения [Время] при этом постепенно и последовательно увеличиваются. Думаю, надо делать индексацию по всем используемым в запросах полям.

1. Поиск по [Код] идет не с середины строки (в начале @p3 нету %). И по совету ЕвгенийВ переделал его в равенство (where p.Код == _code). И убрал конвертирование даты.

Теперь запросы выглядят так:

Первый, через который я узнаю последнее время записи по [Дата] и [Код]:

SELECT MAX([t0].[Время]) AS [value]
FROM [dbo].[All] AS [t0]
WHERE ([t0].[Дата] = @p0) AND ([t0].[Код] = @p1)

Если последнее время записи, полученное из БД, больше, чем прошлое (сохраненное при прошлом запросе данных), то делается второй запрос, который запрашивает сами данные:

SELECT [t0].[Дата], [t0].[Время], [t0].[времяизвнешнегоПО], [t0].[Код], [t0].[Номер], [t0].[Цена], [t0].[Кол_во]
FROM [dbo].[All] AS [t0]
WHERE ([t0].[Время] <= @p0) AND ([t0].[Время] > @p1) AND ([t0].[Дата] = @p2) AND ([t0].[Код] = @p3)

Стало работать намного быстрее, загрузка проца упала раза в три, и теперь составляет 15-20%. По соотношению запросов, профайлер показывает, что второй запрос выполняется примерно в десять раз реже, чем первый, то есть, раз в две секунды. По времени второй запрос выполняется в четыре раза дольше, чем первый.

Думаю, если разобраться с индексами, то станет еще существенно быстрее.

2. Ну вот я и не смог сразу сделать индекс по полю [Время], так как студия написала, что это невозможно. Как пометить вычисляемое поле, как PERSISTED? У меня русская версия, как называется это свойство поля по русски? "Материализованный"? Я попробовал, пишет "Невозможно материализовать вычисляемый столбец "Время" в таблице "Tmp_All", так как он является недетерминированным." Вот код вычисляемого поля:

(CONVERT([time](0),[времяизвнешнегоПО],0))

Пока я только сделал кластеризированный индекс по полям [Код] и [Дата].

3. У меня поле [Номер] сделано первичным некластеризированным ключем. Правильно ли это? Поле [Номер] уникально и полностью соответствует порядку добавления записей в БД. То есть, чем больше дата и время, тем больше будет номер. Номер постоянно увеличивается и никогда не обнуляется. Но при этом, поле [Номер] никогда не используется в запросах. В запросах используется только [Код], [Дата] и [Время].
30 июн 11, 18:09    [10902231]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Questq2
Ну вот я и не смог сразу сделать индекс по полю [Время], так как студия написала, что это невозможно. Как пометить вычисляемое поле, как PERSISTED? У меня русская версия, как называется это свойство поля по русски? "Материализованный"? Я попробовал, пишет "Невозможно материализовать вычисляемый столбец "Время" в таблице "Tmp_All", так как он является недетерминированным."
Да, не получается... Ругается сиквел, хоть это преобразование реально детерминированное...

А можете в запросе использовать для поиска поле времяивнешнегоПО?
Можно даже преобразовывать, чтоб приложение не менять:

SELECT CONVERT(time, MAX([t0].[времяизвнешнегоПО]), 0) AS [value]
FROM [dbo].[All] AS [t0]
WHERE ([t0].[Дата] = @p0) AND ([t0].[Код] = @p1)

SELECT [t0].[Дата], [t0].[Время], [t0].[времяизвнешнегоПО], [t0].[Код], [t0].[Номер], [t0].[Цена], [t0].[Кол_во]
FROM [dbo].[All] AS [t0]
WHERE ([t0].[Время] <= CONVERT(time, @p0, 0)) AND ([t0].[Время] > CONVERT(time, @p1, 0)) AND ([t0].[Дата] = @p2) AND ([t0].[Код] = @p3)

И тогда в индекс третьим полем времяивнешнегоПО
30 июн 11, 18:24    [10902331]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
alexeyvg
А можете в запросе использовать для поиска поле времяивнешнегоПО?
Можно даже преобразовывать, чтоб приложение не менять:
Да, ошибся, так не получится, если время в формате AM/PM :-(
30 июн 11, 18:38    [10902394]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Questq2
Member

Откуда:
Сообщений: 56
alexeyvg, наверное могу, тока не знаю.

Я это вычисляемое поле ввел только для того, чтобы было удобно работать С БД. То есть, в своем .Net приложении, я оперирую типами DateTime для даты и TimeSpan для времени, просто подставляя их в LINQ-запрос:

DateTime _date;
TimeSpan _time;

                       var queryLastTime = (from p in dc.All
                                                  where p.Код == code
                                                  where p.Дата == _date
                                                  select p.Время).Max();

                        var query = from p in dc.All
                                    where p.Код == _code
                                    where p.Дата == _date
                                    where p.Время > _time.Subtract(Period)
                                    where p.Время <= _time
                                    select p;
30 июн 11, 18:40    [10902404]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Questq2
alexeyvg, наверное могу, тока не знаю.

Я это вычисляемое поле ввел только для того, чтобы было удобно работать С БД. То есть, в своем .Net приложении, я оперирую типами DateTime для даты и TimeSpan для времени, просто подставляя их в LINQ-запрос:
К сожалению, по полю в формате AM/PM искать нельзя :-(

Если скорость критична, то остаётся только для поиска сделать вычисляемое поле в другом формате (не time) или вообще сделать не-вычисляемое поле и сохранять его при сохраенении основного.
30 июн 11, 18:42    [10902414]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Questq2
Member

Откуда:
Сообщений: 56
alexeyvg,

То есть, моя задача найти способ засунуть поле [Время] в индекс и я добьюсь максимально возможной скорости? У меня время в формате 24 часа (не 12, как при AM/PM)
30 июн 11, 19:41    [10902615]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Questq2
alexeyvg,

То есть, моя задача найти способ засунуть поле [Время] в индекс и я добьюсь максимально возможной скорости?
Да, именно так. У вас маленькая селективность фильтрации по полям дата и код, поэтому и не очень быстро. Если добавить и время, то скорость сильно возрастёт.

Questq2
У меня время в формате 24 часа (не 12, как при AM/PM)
Ну, формат 0 в функции CONVERT говорит об обратном: (CONVERT([time](0),[времяизвнешнегоПО],0))

Если у вас формат 24 часа для поля [времяизвнешнегоПО], тода всё решается так, как я писал выше - поиск по полю [времяизвнешнегоПО] (и включить его в индекс), при этом из приложения можно передавать как строку в формате как у [времяизвнешнегоПО], либо [time] и преобразовывать его в запросе.
30 июн 11, 21:37    [10902926]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
Questq2
Member

Откуда:
Сообщений: 56
alexeyvg,

1. Помоему, для моей задачи, это как-то неоптимально - заниматься двойной конвертацией - сначала время в строку, потом строку во время. И неважно, кто ее будет выполнять, приложение или SQL сервер. Надо что-то побыстрее.

2. А точно никак нельзя побороть вычисляемое поле? Я нашел ссылку http://msdn.microsoft.com/ru-ru/library/ms187928.aspx, но там то-ли криво переведено, то-ли в оригинале так написано, и я не пойму, могу ли я сделать так (изменить стиль?), чтобы вычислемое поле стало детерминированным и подверженным индексации? У меня SQL Server2008 R2.

В SQL Server 2005 и более поздних версиях некоторые преобразования типа данных datetime являются недетерминированными.

В SQL Server 2000 преобразования строковых значений в значения date и time помечаются как детерминированные. Однако это неверно для стилей, перечисленных в следующей таблице. Преобразования для этих стилей зависят от настроек языка. В SQL Server 2005 и последующих версиях эти преобразования помечаются как недетерминированные.

Следующая таблица содержит стили, для которых преобразование строк в тип datetime недетерминировано.

3. Как альтернативу вычисляемому полю рассматриваю сделать промежуточную таблицу и триггер. В промежуточную таблицу время из внешнегоПО идет в формате строки, а триггер пишет в результатирующую таблицу сразу в формате time. В итоге я работаю только с результатирующей таблицей индексированной в тч по времени.
1 июл 11, 19:54    [10909093]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Questq2
1. Помоему, для моей задачи, это как-то неоптимально - заниматься двойной конвертацией - сначала время в строку, потом строку во время. И неважно, кто ее будет выполнять, приложение или SQL сервер. Надо что-то побыстрее.
Конечно неоптимально, вот я и предлагаю хранить сразу в 2-х вариантах. У вас то сейчас пересчёты при любом селекте делаются, ну и индекс до кучи сделать нельзя. А это совсем плохо.

Questq2
А точно никак нельзя побороть вычисляемое поле?
Неточно, но я не могу найти...
Questq2
Как альтернативу вычисляемому полю рассматриваю сделать промежуточную таблицу и триггер.
Можно и так, только я не вижу преимуществ в создании второй таблицы со связью 1:1
Лучьше просто поле. И тот же, например, триггер. Или просто писать 2 поля (вообще обычно это проще, чем триггер, но триггер многие считают круче :-) )
1 июл 11, 21:14    [10909411]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше из посторонненго приложения узнать, что БД получила новые записи в таблицы?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Questq2
А точно никак нельзя побороть вычисляемое поле?
Нет, нельзя. Вот если бы вы не использовали формат AM/PM...
1 июл 11, 21:28    [10909447]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить