Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация ХП  [new]
Andrew_256
Guest
Привет!

Исполнение следующей процедуры согласно SQL Profiler (RPC::Completed) занимает от 100 до 400 ms. Не могу поверить, что вставка 2 записей в таблицы так сильно напрягает MS SQL 2000!

CREATE PROCEDURE sp_RecordWrite
@ID int,
@Field1 varchar (128),
@Field2 varchar (10),
@Field3 varchar (256),
@Field4 varchar (256),
@Field5 float,
@Field6 varchar (256),
@WriteTwice smallint,
@outval int OUTPUT

AS
DECLARE @Field2Int int
BEGIN
SET @outval = 0
BEGIN TRANSACTION
SET @Field2Int = CAST(@Field2 AS int)
IF @WriteTwice > 0 BEGIN
INSERT INTO DB1..Records (ID, Field2, Field3, Field4, Field5)
VALUES (@ID, @Field2Int, @Field3, @Field4, @Field5)
END

SET @Field2Int = CAST(((@ID / 10000) % 10000) AS int)
INSERT INTO DB2..Records (ID, Field1, Field2, Field3, Field4, Field5, Field6)
VALUES (@Field2Int, @Field1, @Field2, @Field3, @Field4, @Field5, @Field6)
SET @outval = @@ROWCOUNT

COMMIT TRANSACTION
END

Сама процедура объявлена в DB2. DB1 и DB2 находятся на одном сервере (файлы в одном каталоге). Тригеров нет, индексы - по два обычных индекса на таблицу.

Есть подозрение на CAST или запись в разные БД в одной транзакции.

Вопросы:
1. Что конркетно здесь можно оптимизировать?
2. Где можно почитать про общие приниципы оптиимзации работы ХП?

Спасибо.
18 июл 03, 07:06    [266664]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31598
От 100 до 400 ms - это очень много.
Вы включите в профайлере SP:StmtCompleted и Performance:Execution Plan.
И сразу всё будет понятно.
18 июл 03, 11:01    [266878]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Попробуй еще отказаться от выходного параметра.

CREATE PROCEDURE sp_RecordWrite 

@ID int,
@Field1 varchar (128),
@Field2 varchar (10),
@Field3 varchar (256),
@Field4 varchar (256),
@Field5 float,
@Field6 varchar (256),
@WriteTwice smallint

AS
DECLARE @Field2Int int, @outval int
SET XACT_ABORT ON
BEGIN TRAN
...

SET @outval = @@ROWCOUNT

COMMIT TRANSACTION

RETURN(@outval)
18 июл 03, 11:08    [266892]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Tracer
Member

Откуда:
Сообщений: 728
Какая машина ? Триггеры есть ? Каков размер таблицы, какие кластерные и некластерные индексы? Кстати CAST там явно лишний, результат операции и так будет int.
18 июл 03, 11:09    [266893]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31598
2pkarklin
Думаете, присвоение параметра занимает 400 ms времени? :-)

Я думаю, тут скорее всего триггеры (хоть автор и говорит, что их нет), но может-быть и сложные индексы, констрэйны, блокировки...

Даже перекомпиляции быть не может (если приведён весь текст процедуры).
18 июл 03, 11:13    [266906]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Tracer
Member

Откуда:
Сообщений: 728
О черт, не заметил про индексы и триггеры, sorry. А какая авторизация используется - WinNT или SQL ?
18 июл 03, 11:16    [266913]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
2 alexeyvg

Думаете, присвоение параметра занимает 400 ms времени? :-)

Добавление выходного параметра в данном случаи считаю лишним. Хотя согласен, что он коренным образом не может влиять на быстродействие. А потом говорить о временных интервалах можно тока в сравнении с чем нибудь. Например, с теми же инсертами, но не в хп. А так, мож по жизни сервер такую производительность имеет. Железо, например, слабое. :-)
18 июл 03, 11:26    [266929]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Andrew_256
Guest
Спасибо за ответы.

1. Текст процедуры приведен весь.
2. Тригеров нет.
3. Индексов два, каждый по одному полю. Clustered нет (хотя планируется и насколько я понимаю может только замедлить вставку).
4. Железо не супер - PIII-750, 512Mb, SCSI диск. Недавно на нем делал тест по вставке записей (на таблице без индексов с 2-мя полями), вставлял 500 записей за 3 секунды. Т.е. с учетом транзакции и индексов я бы ожидал увидеть 50ms, но никак не 300 :(
5. SP:StmtCompleted выдает такое же время.
6. Авторизация NT, клиент работает через ADO.
7. Ставил в Profiler все объекты по локам и другим классам - никаких длинных операций не видно.
8. Таблица на реальном сервере может быть большая (300 000), но у меня на тесте явно меньше ста тысяч, я думаю, тысяч десять.

Кстати вопросы по SQL Profiler:
1. Почему он для CPU всегда показывает 0? Ни на одном классе он мне не показал число больше нуля.
2. Есть ли возможность померять время выполнения отдельных INSERT внутри одной процедуры?

Спасибо.
18 июл 03, 11:56    [267013]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Tracer
Member

Откуда:
Сообщений: 728
Проверить время отдельных частей можно так :
DECLARE @ms INT

DECLARE @dtStart DATETIME
DECLARE @dtFinish DATETIME

SET @dtStart = GETDATE()

-- some code


SET @dtFinish = GETDATE()

SET @ms = DATEDIFF( ms, @dtFinish, @dtStart )

PRINT 'Execution time in ms ' + CAST ( @ms AS VARCHAR(10) )


Хотя я думаю, что дело в авторизации. Возможно эта задержка - время доступа к PDC (Primary Domain Controller)
18 июл 03, 12:30    [267105]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31598
2Andrew_256
По поводу 5. SP:StmtCompleted выдает такое же время.
Я имел в виду установку события Stored Procedures - SP:StmtCompleted - это заставляет сервер трэйсить стэйтменты внутри ХП. Посмотрите их - вам будет видно конкретное место замедления. (это относится и к вопросу №2)

По вопросу № 1 - Почему он для CPU всегда показывает 0?
Видимо, у вас стоит SP3. После его установки трэйс не показывает CPU для RPC-вызовов, только для батчей.
18 июл 03, 16:56    [267751]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Andrew_256
Guest
2Tracer: А как можно ускорить авторизацию?

Спасибо.
18 июл 03, 19:56    [267986]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Andrew_256
Guest
Посмотрел на SP:stmtCompleted - все время уходит на COMMIT. Даже не пойму, что в таком случае можно оптимизировать :(((
18 июл 03, 21:10    [268054]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
а блокировки есть на таблицах?
18 июл 03, 21:31    [268077]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Andrew_256
Guest
У меня в Profiler стоят трейсы для всех блокировок,но по времени ничего долгого не выводится.

Или могут быть где-то еще?
18 июл 03, 21:44    [268095]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
кто-нибудь еще работает с этими таблицами? кстати, какого размера таблицы, сколько на них индексов и т.п?.

кстати, отмести подозрения от CAST можно просто передав внутрь в тестовую процедуру уже рассчитанное значение

затем можно попробовать выполнить процедуру без транзакций
19 июл 03, 10:23    [268275]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
SiDen
Member

Откуда:
Сообщений: 518
Попробуйте вынести все SET-ы за транзакцию

CREATE PROCEDURE sp_RecordWrite
@ID int,
@Field1 varchar (128),
@Field2 varchar (10),
@Field3 varchar (256),
@Field4 varchar (256),
@Field5 float,
@Field6 varchar (256),
@WriteTwice smallint,
@outval int OUTPUT

AS
DECLARE @Field2Int int
BEGIN
SET @outval = 0
SET @Field2Int = CAST(@Field2 AS int)
SET @Field2Int = CAST(((@ID / 10000) % 10000) AS int)
BEGIN TRANSACTION
IF @WriteTwice > 0 BEGIN
INSERT INTO DB1..Records (ID, Field2, Field3, Field4, Field5)
VALUES (@ID, @Field2Int, @Field3, @Field4, @Field5)
END
INSERT INTO DB2..Records (ID, Field1, Field2, Field3, Field4, Field5, Field6)
VALUES (@Field2Int, @Field1, @Field2, @Field3, @Field4, @Field5, @Field6)
COMMIT TRANSACTION
SET @outval = @@ROWCOUNT
END
19 июл 03, 11:02    [268284]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
А если вообще убираешь транзакцию - работает быстрее?
20 июл 03, 02:16    [268493]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Andrew_256
Guest
Спасибо всем за помощь.
Без транзакции стало в 2 раза быстрее.
Написал простого клиента, который запускает данную процедуру - выполняется от 30 до 100 ms. Причем "плавает" достаточно часто. Несколько пугает разброс времени. Я понимаю, что MSSQL - не real-time, но все же разброс в 3 раза недопустим.
21 июл 03, 23:34    [270316]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31598
Всё равно непонятно.
Время от 30 до 100 ms - это всё равно очень много для вставки двух записей, к тому-же коротких.
Такие процедуры обычно показывают нули в профайлере.
22 июл 03, 11:34    [270637]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
Andrew_256
Guest
Создал таблицу:

CREATE TABLE [dbo].[Records] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Field1] [int] NOT NULL ,
[Field2] [varchar] (128) NOT NULL ,
[Field3] [varchar] (10) NOT NULL ,
[Field4] [varchar] (256) NOT NULL ,
[Field5] [varchar] (256) NOT NULL ,
[Field6] [float] NOT NULL ,
[Field7] [varchar] (256) NOT NULL
) ON [PRIMARY]
GO

Создал процедуру:

CREATE PROCEDURE sp_RecordWrite
@ID int,
@Field1 varchar (128),
@Field2 varchar (10),
@Field3 varchar (256),
@Field4 varchar (256),
@Field5 varchar (256),
@Field6 float,
@Field7 varchar (256),
@WriteTwice smallint,
@outval int OUTPUT

AS
DECLARE @Field2Int int
BEGIN
SET @outval = 0
BEGIN TRANSACTION
SET @Field2Int = CAST(@Field2 AS int)

SET @Field2Int = CAST(((@ID / 10000) % 10000) AS int)
INSERT INTO AMATTest..Records (ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7)
VALUES (@Field2Int, @Field2Int, @Field2, @Field3, @Field4, @Field5, @Field6, @Field7)
SET @outval = @@ROWCOUNT

COMMIT TRANSACTION
END
GO

Запускаю на выполнение:
SET STATISTICS TIME ON
SET IDENTITY_INSERT dbo.Records ON
DECLARE @OutputInt int
EXEC sp_RecordWrite 6,'Field1','123789','Field3','Field4','Field5',0.001,'Field7',0,@OutputInt OUT

Получаю результат (время среднее - может быть и 15ms, несколько раз было 0):
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.

При этом SQL Profiler SP:StmtCompleted упорно показывает 16ms на строке -- sp_RecordWrite
COMMIT TRANSACTION.

Может у меня какие-то настройки не так стоят или Profiler врет? Куда смотреть?

Спасибо.
22 июл 03, 21:50    [271579]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
CREATE PROCEDURE sp_RecordWrite 

@ID int,
@Field1 varchar (128),
@Field2 varchar (10),
@Field3 varchar (256),
@Field4 varchar (256),
@Field5 varchar (256),
@Field6 float,
@Field7 varchar (256),
@WriteTwice smallint

AS
DECLARE @Field2Int int
BEGIN
SET @outval = 0
if exists(SELECT TOP 1 1 FROM AMATTest..Records WHERE [ID]=@ID)
UPDATE
AMATTest..Records
Set
Field1=@Field1,
Field2=@Field1,
Field3=@Field1,
Field4=@Field1,
Field5=@Field1,
Field6=@Field1,
Field7=@Field1
Else
INSERT INTO AMATTest..Records (Field1, Field2, Field3, Field4, Field5, Field6, Field7)
VALUES (@Field1, @Field2, @Field3, @Field4, @Field5, @Field6, @Field7)
SET @outval = @@ROWCOUNT

SELECT @@ROWCOUNT
END

Zapusti v takom vide.
23 июл 03, 11:53    [272101]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить