Оптимизация (Вопрос к Java and DBA специалистам)

ilthw · Post by **ilthw** » 18 Oct 2001 16:13

Есть вопрос по оптимизации. Так как вопрос стоит на грани Java and Database design, задаю здесь а не в ньюсах. (Здесь все таки больше спецов [img:0c33d243a5]images/smiles/icon_smile.gif[/img:0c33d243a5] )

Имеем.

MS Sql Server 7.0

2 Table (таблиц конечно больше, но в данном контексте интересуют только две) :

Table 1
--------
CREATE TABLE [dbo].[Certificate_tbl] (
[certId] [int] IDENTITY (1, 1) NOT NULL ,
[caCertId] [int] NULL ,
[caId] [int] NOT NULL ,
[certVersion] [tinyint] NULL ,
[certValidFrom] [datetime] NULL ,
[certValidTo] [datetime] NULL ,
[certSerialNumber] [varbinary] (100) NOT NULL ,
[certDn] [varchar] (500) NULL ,
[certBinary] [image] NULL ,
[isCACert] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Certificate_tbl] WITH NOCHECK ADD
CONSTRAINT [PK_Certificate_tbl] PRIMARY KEY NONCLUSTERED
(
[certId]
) ON [PRIMARY]

CREATE INDEX [IX_Certificate_tbl] ON [dbo].[Certificate_tbl]([caId]) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_Certificate_tbl_1] ON [dbo].[Certificate_tbl]([caId], [certSerialNumber]) ON [PRIMARY]

Table 2
--------
CREATE TABLE [dbo].[certStatus_tbl] (
[certStatusId] [int] IDENTITY (1, 1) NOT NULL ,
[certId] [int] NOT NULL ,
[eventDate] [smalldatetime] NOT NULL ,
[certStatus] [int] NOT NULL ,
[value] [int] NULL ,
[revokeTime] [smalldatetime] NULL ,
[informationId] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[certStatus_tbl] WITH NOCHECK ADD
CONSTRAINT [PK_certStatus_tbl] PRIMARY KEY NONCLUSTERED
(
[certStatusId]
) ON [PRIMARY]

CREATE INDEX [IX_certStatus_tbl_1] ON [dbo].[certStatus_tbl]([certId]) ON [PRIMARY]

CREATE INDEX [IX_certStatus_tbl_2] ON [dbo].[certStatus_tbl]([revokeTime], [certId]) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_certStatus_tbl_3] ON [dbo].[certStatus_tbl]([certId], [revokeTime]) ON [PRIMARY]

ALTER TABLE [dbo].[certStatus_tbl] ADD
CONSTRAINT [FK_certStatus_tbl_Certificate_tbl] FOREIGN KEY
(
[certId]
) REFERENCES [dbo].[Certificate_tbl] (
[certId]
)

Java / Сценарий
----------
Java 1.3/ JRun 3.0/ WebLogic JDriver (JDBC V4 Driver) / Not EJB

Имеем Iterator 20000 элементов, который надо обработать.

Обрабатываем в цикле с одним Connection :

1. Ищем соответсвенную запись в 1 таблице:
(PrepareStatement)

"SELECT ce.certId, ce.certDn, ce.certBinary, ca.caId FROM Certificate_tbl ce, CA_tbl ca WHERE ca.caDN = ? AND ca.caId = ce.caId AND ce.certSerialNumber = ?"

2. Если не находим всавляем в 1 таблицу:

INSERT INTO Certificate_tbl(caId, certSerialNumber) VALUES(?,?)

3. Вставляем запись во вторую таблицу:
а. Проверяем есть ли такой тип записи:
"SELECT certStatusId FROM certStatus_tbl WHERE certId = ? AND revokeTime=?"

б. Вставляем запись:
"INSERT INTO certStatus_tbl(certId,
eventDate, certStatus, value, revokeTime,
informationId) VALUES(?,?,?,?,?,?)"

-- Конец цикла.

То есть имеем 2 query и 2 insert через PrepareStatement (40000 query and 40000 insert).

На "пустой" базе performanse: ~1000 elements per 1.5 min.
Нормально ли это ?
Прошелся Optimize It: основное время (99%)тратится на работу JDriver executeQuery etc...

Интересно что если создаю PrepareStatements в начале и использую их для каждого элемента (а не создаю новый), то performanse: 1000 elements per 4.5 min !
Вот странно.

Есть ди возможно увеличить производительность ? Может нарезать elements по порциям и запустить в разных потоках со своим коннектом ? Или что-то не правильно в схеме ? Я не DBA [img:0c33d243a5]images/smiles/icon_sad.gif[/img:0c33d243a5]

Cпасибо за любой ответ [img:0c33d243a5]images/smiles/icon_smile.gif[/img:0c33d243a5]

[ 18-10-2001: Message edited by: ilthw ]

AK70 · Post by **AK70** » 18 Oct 2001 18:34

make a check contraint in the database, then don't make "select" query to check the existence of the record, but simply try to insert it. the check constraint will not allow duplicate records.

also, to make 20k insert calls is quite expensive, especially if you do it often. in that case I'd think of bulk load utilities.

sav_me · Post by **sav_me** » 18 Oct 2001 22:53

Лучше использовать по своему connection/prepared statement на каждую таблицу. Обратите внимаение на batch size для prepared statement - по умолчанию он мал.

Если есть возможность - дропнуть индексы и констрейны перед началом процедуры, а потом создать по-новой. Подходит для создания больших таблиц.

Несколько тредов - это хорошо. Только надо придумать как поделить данные на порции. Если они в памяти, тогда просто прекрасно. Количество тредов определите опытным путем - зависит от многих факторов.

[ 18-10-2001: Message edited by: sav_me ]

AK70 · Post by **AK70** » 18 Oct 2001 23:04

<BLOCKQUOTE>quote:<HR>Originally posted by ilthw:


Типа хранимой процедуры ?

А что все таки с попыткой разбить на threads ? А то не нравится мне 100% загруз системы...<HR></BLOCKQUOTE>

bulk load maybe done with special utilities. It's not the most OO methods though [img:a356fc9531]images/smiles/icon_smile.gif[/img:a356fc9531]

threads? good idea. with Oracle I'd definitely recommend it. you would create 10 threads, wach with 2000 records to load, for example.

tengiz · Post by **tengiz** » 18 Oct 2001 23:30

Проблема с Вашим сценарием в том, что такая последовательность действий:

1) вызывает лишние пересылки с данных с сервера на клиент и обратно
2) возможно, вызывает лишние перекомпиляции;
3) заставляет диск, на котором находится журнальное устройство, дёргаться чаще, чем это реально нужно.

Многопотоковый клиент, который здесь уже упоминался, может реально помочь только справиться с третьей проблемой, да и то не всегда. Так что я бы этим даже не марал руки.

Чтобы избавиться от всех трёх проблем можно сделать следующее:

Вариант 1. Создаёте две хранимые процедуры:
<BLOCKQUOTE>code:<HR><pre>
CREATE PROCEDURE #1 (@caID INT, @certSerialNumber VARBINARY(100)) AS
BEGIN TRAN
INSERT INTO Certificate_tbl(caId, certSerialNumber)
SELECT @caId, @certSerialNumber
WHERE NOT EXISTS
(
SELECT *
FROM Certificate_tbl ce JOIN CA_tbl ON ca.caId = ce.caId
WHERE ca.caDN = @caId AND ce.certSerialNumber = @certSerialNumber
)
COMMIT TRAN

или

CREATE PROCEDURE #1 (@caID INT, @certSerialNumber VARBINARY(100)) AS
BEGIN TRAN
IF NOT EXISTS
(
SELECT *
FROM Certificate_tbl ce JOIN CA_tbl ON ca.caId = ce.caId
WHERE ca.caDN = @caId AND ce.certSerialNumber = @certSerialNumber
)
INSERT INTO Certificate_tbl(caId, certSerialNumber) VALUES (@caId, @certSerialNumber)
COMMIT TRAN

CREATE PROCEDURE #2 (@certId INT, @eventDate SMALLDATETIME, ...) AS
BEGIN TRAN
INSERT INTO certStatus_tbl(certId, eventDate, certStatus, value, revokeTime, informationId)
SELECT @certId, @eventDate, @certStatus, @value, @revokeTime, @informationId
WHERE NOT EXISTS
(
SELECT *
FROM certStatus_tbl WHERE certId = @certId AND revokeTime = @certId
)
COMMIT TRAN
</pre><HR></BLOCKQUOTE>
И вызываете их внутри транзаукций группами, размер которых определяется опытным путём, начниет с групп, скажем в 10 вызовов и поэкспериментируйте - опримальный, с точки зрения производительности, зависит о Ваше конкретной аппаратуры.

Вариант 2. Создаёте две временные таблицы:
<BLOCKQUOTE>code:<HR><pre>
CREATE TABLE #Certificate_tbl (caID INT, certSerialNumber VARBINARY(100))
CREATE TABLE #certStatus_tbl(certId INT, eventDate SMALLDATETIME, ...)
</pre><HR></BLOCKQUOTE>
и загружаете туда все строки (опять же группами внутри транзакций, размер групп нужно подбирать, для начала возьмите те же 10 строк в группе), в том числе и при помощи bulk load, в этом случае Вам нужно будет создавать либо нормальные таблицы, либо глобальные временные. После чего, одним запросом помещаете только нужные строки в целевые таблицы, при этом вы опять же выигрываете за счёт отсутствия лишних пересылок и можете получить большой выйгрыш из-за групповой вставки:
<BLOCKQUOTE>code:<HR><pre>
INSERT INTO Certificate_tbl(caId, certSerialNumber)
SELECT caId, certSerialNumber
FROM #Certificate_tbl
WHERE NOT EXISTS
(
SELECT *
FROM Certificate_tbl ce JOIN CA_tbl ON ca.caId = ce.caId
WHERE ca.caDN = #Certificate_tbl.caId
AND
ce.certSerialNumber = #Certificate_tbl.certSerialNumber
)

INSERT INTO certStatus_tbl(certId, eventDate, certStatus, value, revokeTime, informationId)
SELECT certId, eventDate, certStatus, value, revokeTime, informationId
FROM #certStatus_tbl
WHERE NOT EXISTS
(
SELECT *
FROM certStatus_tbl
WHERE certId = #certStatus_tbl.certId
AND
revokeTime = #certStatus_tbl.certId
)
</pre><HR></BLOCKQUOTE>
или второй вариант, сначала удаляете из временных таблиц все ненужные строки, а затем вставляете оставшиеся:
<BLOCKQUOTE>code:<HR><pre>
DELETE #Certificate_tbl
WHERE EXISTS
(
SELECT *
FROM Certificate_tbl ce JOIN CA_tbl ON ca.caId = ce.caId
WHERE ca.caDN = #Certificate_tbl.caId
AND
ce.certSerialNumber = #Certificate_tbl.certSerialNumber
)

INSERT INTO Certificate_tbl(caId, certSerialNumber)
SELECT caId, certSerialNumber
FROM #Certificate_tbl
</pre><HR></BLOCKQUOTE>
со второй таблицей аналогично. Вам, возможно, понадобятся композитные индексы на этих временных таблицах, так что поэкспериментируйте, однако создавать их нужно после того, как вы вставите в них данные - это будет быстрее, чем вставка в таблицы с индексом.

[ 18-10-2001: Message edited by: tengiz ]

AK70 · Post by **AK70** » 19 Oct 2001 00:16

<BLOCKQUOTE>quote:<HR>Originally posted by ilthw:


Я думал насчет этого. В принципе констрейн есть, но боюсь, что если просто ловить SQLException и ничего не делать, то можно потерять данные при другой проблеме. Там же будет что-то типа: "java.sql.SQLException: Cannot insert duplicate key row in object..."
<HR></BLOCKQUOTE>

you may parse SQLException, to know what exactly happened. if you know that mostly your data will be inserted, and only ocasionally rejected, then this is the best way.

if only small part of 20k records are new, then this maybe slow, because of many exceptions.
then you can do this: write a stored procedure, it takes your new record and tries to insert into the table. if it fails then it simply stores it in the temp table. at the end of the transaction you simply look into that temp table and know what records were rejected. or you may process these records on the server further

Masterok · Post by **Masterok** » 19 Oct 2001 02:00

Полностью согласен с теми, кто утверждает, что основной путь повысить производительность - это перенести логику в хранимые процедуры.
Негоже это - производить bulk operations на middle tier or client, гонять данные туды-сюды.

Если только продукт не должен работать на всех базах данных. Тогда может быть Java stored procedures? У MS SQL есть Java stored procedures?

ilthw · Post by **ilthw** » 19 Oct 2001 08:49

<BLOCKQUOTE>quote:<HR>Originally posted by AK70:
make a check contraint in the database, then don't make "select" query to check the existence of the record, but simply try to insert it. the check constraint will not allow duplicate records.
<HR></BLOCKQUOTE>

Я думал насчет этого. В принципе констрейн есть, но боюсь, что если просто ловить SQLException и ничего не делать, то можно потерять данные при другой проблеме. Там же будет что-то типа: "java.sql.SQLException: Cannot insert duplicate key row in object..."

Можно конечно анализировать эту строку...

<BLOCKQUOTE>quote:<HR>
also, to make 20k insert calls is quite expensive, especially if you do it often. in that case I'd think of bulk load utilities.<HR></BLOCKQUOTE>

Типа хранимой процедуры ?

А что все таки с попыткой разбить на threads ? А то не нравится мне 100% загруз системы...

Seryi · Post by **Seryi** » 19 Oct 2001 12:03

На то он и MS, что Java там отсутствует как класс.

ilthw · Post by **ilthw** » 19 Oct 2001 15:21

Спасибо всем за помощь!

Буду эксперементировать.
Продукт должен работать на всех базах по этому не хотелось бы свяхываться с хранимыми процедурами.

<BLOCKQUOTE>quote:<HR>Originally posted by Masterok:
Полностью согласен с теми, кто утверждает, что основной путь повысить производительность - это перенести логику в хранимые процедуры.
Негоже это - производить bulk operations на middle tier or client, гонять данные туды-сюды.

Если только продукт не должен работать на всех базах данных. Тогда может быть Java stored procedures? У MS SQL есть Java stored procedures?<HR></BLOCKQUOTE>

AK70 · Post by **AK70** » 19 Oct 2001 16:18

<BLOCKQUOTE>quote:<HR>Originally posted by Masterok:
Если только продукт не должен работать на всех базах данных. <HR></BLOCKQUOTE>

it's not a valid reason to avoid stored procedrues [img:3d6fbc5231]images/smiles/icon_smile.gif[/img:3d6fbc5231] unless you don't care about performance. Once I had to deal with the product whcih works on any database. it used very very basic SQL. It was terribly slow. It didn't use any keys or constraints, e.g. before inserting the record it was checking the duplicate records itself. It was slow, but developers didn't have to support different databases.

Miksa · Post by **Miksa** » 20 Oct 2001 02:30

<BLOCKQUOTE>quote:<HR>Originally posted by ilthw:
То есть имеем 2 query и 2 insert через PrepareStatement (40000 query and 40000 insert).
<HR></BLOCKQUOTE>

Как уже предложили, стоит попробовать заменить эти 4 запроса на 2 INSERT /SELECT.

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

Проблема с 4-мя запросами может быть в количестве записей, всталяемых за одну транзакцию.

Если я не ошибаюсь, то по умолчанию SQL Server делает одну транзакцию на каждый INSERT.

Подобрав число записей в одной транзакции (50-100-200-500), время работы можно существенно сократить.

Masterok · Post by **Masterok** » 20 Oct 2001 02:50

<BLOCKQUOTE>quote:<HR>Originally posted by ilthw:
Спасибо всем за помощь!

Буду эксперементировать.
Продукт должен работать на всех базах по этому не хотелось бы свяхываться с хранимыми процедурами.

<HR></BLOCKQUOTE>

Тогда, похоже, только threads спасут отца русской демократии. И еще autocommit отключить на уровне jdbc connection.

Masterok · Post by **Masterok** » 20 Oct 2001 02:56

<BLOCKQUOTE>quote:<HR>Originally posted by Miksa:


Как уже предложили, стоит попробовать заменить эти 4 запроса на 2 INSERT /SELECT.
<HR></BLOCKQUOTE>

Боюсь, что синтаксис INSERT-SELECT у разных вендоров баз данных разный. Кажется, что он до какого-то времени даже не был (а может быть и сейчас не является) частью ANSI SQL.

machineHead · Post by **machineHead** » 23 Oct 2001 00:38

Не знаю, что у Вас за железо, но что-то всё очень медленно...
<BLOCKQUOTE>quote:<HR>Originally posted by ilthw:

...
На "пустой" базе performanse: ~1000 elements per 1.5 min.
...
Интересно что если создаю PrepareStatements в начале и использую их для каждого элемента (а не создаю новый), то performanse: 1000 elements per 4.5 min !
Вот странно.

...

[ 18-10-2001: Message edited by: ilthw ]<HR></BLOCKQUOTE>

Т.е. Вы делаете PrepareStatements внутри цикла? Это, конечно, существенно снижает производительность.

Почему может получиться худший результат, если PrepareStatements делать один раз? Если делать на пустой базе, то оптимизатор, не зная, что там будет, может наворотить черте-чего. Поэтому надо либо Optimizer Hints использовать (не знаю, есть ли они в SQL Server), либо что-то положить в базy, а потом уже делать Prepare.

ilthw · Post by **ilthw** » 23 Oct 2001 14:38

<BLOCKQUOTE>quote:<HR>Originally posted by machineHead:
Не знаю, что у Вас за железо, но что-то всё очень медленно...

<HR></BLOCKQUOTE>

Ну все пока на моем компьютере крутится:
SQL Server + JRun (Apache) - Memory 392MB

<BLOCKQUOTE>quote:<HR>
Т.е. Вы делаете PrepareStatements внутри цикла? Это, конечно, существенно снижает производительность.
<HR></BLOCKQUOTE>

Я пробовал и так и так. Разница в 3 раза.

<BLOCKQUOTE>quote:<HR>
Почему может получиться худший результат, если PrepareStatements делать один раз? Если делать на пустой базе, то оптимизатор, не зная, что там будет, может наворотить черте-чего. Поэтому надо либо Optimizer Hints использовать (не знаю, есть ли они в SQL Server), либо что-то положить в базy, а потом уже делать Prepare.<HR></BLOCKQUOTE>

Что положить ? [img:d0f519f6dc]images/smiles/icon_smile.gif[/img:d0f519f6dc]

machineHead · Post by **machineHead** » 23 Oct 2001 17:03

<BLOCKQUOTE>quote:<HR>Originally posted by ilthw:


Что положить ? [img:21a3ee5960]images/smiles/icon_smile.gif[/img:21a3ee5960]<HR></BLOCKQUOTE>

Несколько сот записей в каждую таблицу, чтобу оптимизатор мог понять, что от него хотят [img:21a3ee5960]images/smiles/icon_smile.gif[/img:21a3ee5960]