Помогите с ADO/ODBC/MySQL

Программирование на Visual Basic, главный форум. Обсуждение тем программирования на VB 1—6.
Даже если вы плохо разбираетесь в VB и программировании вообще — тут вам помогут. В разумных пределах, конечно.
Правила форума
Темы, в которых будет сначала написано «что нужно сделать», а затем просьба «помогите», будут закрыты.
Читайте требования к создаваемым темам.
CoffeeManager
Начинающий
Начинающий
 
Сообщения: 1
Зарегистрирован: 12.02.2010 (Пт) 15:50

Помогите с ADO/ODBC/MySQL

Сообщение CoffeeManager » 12.02.2010 (Пт) 16:23

Господа!
Ценю Ваше время, и буду очень признателен если кто-нибудь из профессионалов даст совет.
Моя программа на VB работает с удаленной базой данных MySQL через MyODBC драйвер, в VB работаю через ADOшное подключение.
В целом все работает, но т.к. база удаленная очень мучает скорость работы. Оптимизировал все запросы на более сложные, чтобы получать все требуемое одним запросом, многое ускорил, но основная проблема осталась:
Когда получаю список и его необходимо обработать полностью - шагаю по нему через MoveNext. Проблема заключается в том, что каждый MoveNext фактически "дергает" удаленную базу данных, и соответственно каждая итерация при удаленной работе создает очень большую задержку, а т.к. списки не маленькие - получение информации очень тормозит работу.
Есть ли возможность получить как бы Snapshot этого рекордсета, т.е. сразу полностью его весь выкачать и затем в циклах обработки работать уже с локальными данными?
Возился с курсорами, параметрами соединения, мне кажется все перебрал - результат один и тот же.
Есть, на мой взгляд бредовая, идея - получить результат этого запроса единым блоком, объеденив все ряды через CONCAT, затем в программе пропарсить и разделить ряды - но это Криво...

Может есть у кого какие-нибудь мысли на этот счет?
Как можно получить сразу все данные?

Пример получения/Обработки данных
Код: Выделить всё
Dim rs As ADODB.Recordset
mysql.SelectQuery "SELECT `service_table`.*, DATE_FORMAT(`datetime`, '%d.%m.%Y' ) as `date`, `users`.`name` AS `opername` FROM `service_table` LEFT JOIN `users` ON `service_table`.`person` = `users`.`id` WHERE `automat` = '" & CStr(selaut) & "' ORDER BY `datetime` DESC, `id` DESC LIMIT 30;", rs

(...Проверка на правильность открытия...)
rs.MoveFirst

Do While Not rs.EOF
   If IsInitiated = False Then DoEvents
   If r >= FGrid.rows Then FGrid.rows = FGrid.rows + 1
   (...заносим данны в Грид и прочее...)
     FGrid.TextMatrix(r, 1) = CStr(rs("date"))
     FGrid.TextMatrix(r, 2) = CStr(rs("gs_count"))
     FGrid.TextMatrix(r, 3) = fbalance(rs("gs_total")
  r = r + 1
  rs.MoveNext
Loop
(...Закрываем рекордсет...)

В модуле работы с базой
Коннекшен открываем так
With ADO_Connect
     .mode = adModeReadWrite
     .ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=XX.XX.XX.XX;PORT=3306;DATABASE=XXXXXX;USER=XXX;PASSWORD=XXX;OPTION=3;"
     .Open 'mMain.DSNConnection
End With

Рекордсет открываем с такими параметрами по умолчанию
With ADO_Recordset
    Set .ActiveConnection = ADO_Connect
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Source = sql
    .Open
End With

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Re: Помогите с ADO/ODBC/MySQL

Сообщение alibek » 12.02.2010 (Пт) 16:51

Курсор и так указан клиентский, так что это видимо глючный драйвер виноват.
Но можно еще сделать следующее:
1. Тип курсора указать forward-only
2. Блокировку указать read-only
3. Отвязать рекордсет (после открытия рекордсета выполнить Set rs.ActiveConnection = Nothing)
Lasciate ogni speranza, voi ch'entrate.

FFOX
Новичок
Новичок
 
Сообщения: 44
Зарегистрирован: 04.02.2010 (Чт) 12:42

Re: Помогите с ADO/ODBC/MySQL

Сообщение FFOX » 16.02.2010 (Вт) 15:47

CoffeeManager писал(а):Господа!

В целом все работает, но т.к. база удаленная очень мучает скорость работы. Оптимизировал все запросы на более сложные, чтобы получать все требуемое одним запросом, многое ускорил, но основная проблема осталась:
Когда получаю список и его необходимо обработать полностью - шагаю по нему через MoveNext. Проблема заключается в том, что каждый MoveNext фактически "дергает" удаленную базу данных, и соответственно каждая итерация при удаленной работе создает очень большую задержку, а т.к. списки

Более сложный запрос будет дольше обрабатываться удаленным сервером, так что не факт.
Поскольку доступ удаленный, надо минимизировать как время обработки запроса сервером, минимизировать
количество передаваемых данных(все таки не ЛВС, может быть и в сети проблемы и на удаленном сервере большая загрузка MySQL)
Нужно смотреть запросы, нужно смотреть индексы на таблицах.
Начать с оптимизации таблиц и запросов(ну, если это твои таблицы).
потом выполнить рекомендации, что написал alibek.
Если не планируется бегать по рекордсету взад-вперед точно надо ставить тип курсора Forward-only(они значительно быстрее)
Отвязывать рекордсет от коннекшина тоже полезно,
Можно вообще после получения рекордсета делать его клон на клиенте
set mrs_cln = rs.Clone
и работать с ним.

djalex777
Постоялец
Постоялец
 
Сообщения: 461
Зарегистрирован: 23.03.2006 (Чт) 16:02

Re: Помогите с ADO/ODBC/MySQL

Сообщение djalex777 » 16.02.2010 (Вт) 16:37

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

Это ты с чего взял? Более сложный запрос (правильно построенный, на оптимизированной базе) будет всегда обрабатываться быстрее, чем несколько простых запросов.
Про индексы и т.д. всё правильно - первое что нужно сделать - это построить правильные индексы и оптимизировать структуру если возможно.
FFOX писал(а):Можно вообще после получения рекордсета делать его клон на клиенте
set mrs_cln = rs.Clone
и работать с ним.

А это зачем? Клиентский курсор означает, что данные перед возможностью с ними работать будут закачаны на локальную машину.

FFOX
Новичок
Новичок
 
Сообщения: 44
Зарегистрирован: 04.02.2010 (Чт) 12:42

Re: Помогите с ADO/ODBC/MySQL

Сообщение FFOX » 16.02.2010 (Вт) 16:59

djalex777 писал(а):Это ты с чего взял? Более сложный запрос (правильно построенный, на оптимизированной базе) будет всегда обрабатываться быстрее, чем несколько простых запросов.
FFOX писал(а):Можно вообще после получения рекордсета делать его клон на клиенте
set mrs_cln = rs.Clone
и работать с ним.

А это зачем? Клиентский курсор означает, что данные перед возможностью с ними работать будут закачаны на локальную машину.

1. Не факт. Иногда да, иногда нет.
Начнем с того, что сервер для начала будет компилировать запрос, потом строить план выполнения, а уж потом осуществлять выборку согласно этого
плана. Кроме того не скажу как MySql, а MS SQL-Server собирает статистику, и держит в кеше наиболее часто используемые процедуры, и шанс попасть в кеш у простого запроса выше, чем у сложного. Далее, на загруженном сервере время компиляции запроса и построение плана может быть в разы больше, чем время выборки. Так что не факт. В большинстве случаев приходится анализировать результат и искать компромисс.
2 Чтобы не отключать коннекшен, это один из вариантов, чтобы не обновлялся рекордсет при мувах(если топик-стартер не вводит нас в заблуждение и рекордсет действительно лезет обновляться на сервер)

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

Re: Помогите с ADO/ODBC/MySQL

Сообщение iGrok » 16.02.2010 (Вт) 19:41

FFOX писал(а):1. Не факт. Иногда да, иногда нет.
...

Ты, похоже, напрочь упустил вот эту часть фразы:
djalex777 писал(а):правильно построенный, на оптимизированной базе

А она там была самой важной.

FFOX писал(а):2 Чтобы не отключать коннекшен, это один из вариантов, чтобы не обновлялся рекордсет при мувах(если топик-стартер не вводит нас в заблуждение и рекордсет действительно лезет обновляться на сервер)

Смысл? Если в данном драйвере не работает клиентский курсор, значит нужно искать нормальный драйвер, а не обходные пути.
label:
cli
jmp label

FFOX
Новичок
Новичок
 
Сообщения: 44
Зарегистрирован: 04.02.2010 (Чт) 12:42

Re: Помогите с ADO/ODBC/MySQL

Сообщение FFOX » 17.02.2010 (Ср) 9:06

iGrok писал(а):
FFOX писал(а):
djalex777 писал(а):правильно построенный, на оптимизированной базе

А она там была самой важной.

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

1.Что значит "Правильно построенный на оптимизированной базе" ?
"Правильность" построения отменяет компиляцию запроса? Или построение плана выполнения?
Да, несомненно оптимизация индексов и оптимизация запроса играют значительную роль, не спорю.
Но, все зависит от данных. И эффективность индексов, и эффективность соединений
Иногда проще просканировать таблицу, чем искать по индексу. Хотя в большинстве случаев, и по теории, наоборот.
Иногда клястерный индекс выгоднее, иногда простой, хотя по теории вроде как клястерный должен давать преимущество, поскольку содержит в себе и данные сразу.
Надо смотреть конкретную ситуацию на конкретных данных.
Поскольку для одного и того-же супер - бупер оптимизированного запроса для разного состояния данных могут быть абсолютно разные планы
выполнения.Иногда сидишь в профайлере и прозреваешь
2. У сложного запроса больше вероятность поймать блокировку(deadlock), особенно если SQL- server поддерживает параллелизм
3. Ты еще предложи написать собственный ODBC - драйвер :), человек пользуется тем, что имеет.
и пришел с проблемой, которую надо решить.
и мой вариант, возможно не самый красивый, позволяет ему решить свою проблему.

ЗЫ
Я нисколько не ставлю под сомнение твою квалификацию :),
но я 15 лет работаю с реляционными базами данных и насмотрелся всякого.
В теории все хорошо, на то она и теория, но конкретную реализацию SQL - server-a
пишут простые люди, такие же как и мы с тобой :), и не всегда следуют теории,
не всегда заморачиваются оптимизацией и.т.д. и.т.п.
И вообще, споры по SQL не для этого форума, сейчас Хакер прийдет и надает по шее :)

djalex777
Постоялец
Постоялец
 
Сообщения: 461
Зарегистрирован: 23.03.2006 (Чт) 16:02

Re: Помогите с ADO/ODBC/MySQL

Сообщение djalex777 » 17.02.2010 (Ср) 13:09

FFOX писал(а):1.Что значит "Правильно построенный на оптимизированной базе" ?
"Правильность" построения отменяет компиляцию запроса? Или построение плана выполнения?

Это означает ровно то, что означают все эти слова в русском языке. Правильно построенный - это значит построенный правильно (следуя правилам построения) запрос относительно используемого сервера базы данных. Оптимизированная база - любая база данных начинается с построения структуры данных, а потом уже добавляются индексы и прочее. И поэтому следующее утверждение не верно:
FFOX писал(а):Но, все зависит от данных. И эффективность индексов, и эффективность соединений

Если всё зависит от данных - оптимизируй структуру данных (если всё так плохо - добавь (удали, измени) поля, таблицы и т.д.).

FFOX писал(а):2. У сложного запроса больше вероятность поймать блокировку(deadlock), особенно если SQL- server поддерживает параллелизм

И что? Это никак не уменьшает его скорости. Если "ловишь" блокировку - выполняешь ряд действий, чтобы она не возникала и только одно (желательно чтобы оно было одним из последних, если ничего не помогает) из целого списка дейсвий будет - разбиение запроса на более простые или (и) изменения на уровне шлюзов.

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

Re: Помогите с ADO/ODBC/MySQL

Сообщение iGrok » 17.02.2010 (Ср) 16:05

FFOX писал(а):ЗЫ
Я нисколько не ставлю под сомнение твою квалификацию :),
но я 15 лет работаю с реляционными базами данных и насмотрелся всякого.
В теории все хорошо, на то она и теория, но конкретную реализацию SQL - server-a
пишут простые люди, такие же как и мы с тобой :), и не всегда следуют теории,
не всегда заморачиваются оптимизацией и.т.д. и.т.п.
И вообще, споры по SQL не для этого форума, сейчас Хакер прийдет и надает по шее :)

Ты, видимо, 15 лет с чем-то не тем работал. =)
Нет. Бывают, конечно, ситуации, когда приходится работать с "чужой" базой, к которой сам толком не имеешь доступа. Тогда об оптимизации БД речи, конечно, идти не может (хотя даже в этой ситуации правильным, но не всегда работающим вариантом будет проесть плешь DBA или админу БД, потому что это именно он в первую очередь должен быть заинтересован в скорости работы популярных запросов к БД).

У меня, конечно, опыта поменьше раза в три. Но последние три года приходится работать с системами, обрабатывающими по 500-700 запросов к вебморде в секунду. И до 400млн. запросов к бд в день. Тут "не заморачиваться на оптимизацию" при всём желании не получится.

И чисто по опыту последних трёх дней могу тебе сказать, что небольшой оптимизацией(читай, изменением индекса на одной из таблице, и перестройкой запроса с cross join на left join с переносом части общего условия на левую табличку = сокращением выбираемой инфы) удалось время выполнения одного из запросов сократить с 40с. до 0.97с.

З.Ы. А если вернуться к теме - использование большого и сложного запроса, выдающий только необходимую информацию в условиях узкого канала предпочтительнее - не тратится время на коннект и передачу информации. Если же сложный запрос работает медленнее, чем должен - ешь плешь DBA.
label:
cli
jmp label

FFOX
Новичок
Новичок
 
Сообщения: 44
Зарегистрирован: 04.02.2010 (Чт) 12:42

Re: Помогите с ADO/ODBC/MySQL

Сообщение FFOX » 17.02.2010 (Ср) 17:04

iGrok писал(а):
У меня, конечно, опыта поменьше раза в три. Но последние три года приходится работать с системами, обрабатывающими по 500-700 запросов к вебморде в секунду. И до 400млн. запросов к бд в день. Тут "не заморачиваться на оптимизацию" при всём желании не получится.

Да я понял, понял :)
много запросов в секунду много, но
можно я останусь при своем мнении, основанном на личном опыте?

FFOX
Новичок
Новичок
 
Сообщения: 44
Зарегистрирован: 04.02.2010 (Чт) 12:42

Re: Помогите с ADO/ODBC/MySQL

Сообщение FFOX » 17.02.2010 (Ср) 17:31

djalex777 писал(а):"Правильность" построения отменяет компиляцию запроса? Или построение плана выполнения?

Это означает ровно то, что означают все эти слова в русском языке. Правильно построенный - это значит построенный правильно (следуя правилам

Если всё зависит от данных - оптимизируй структуру данных (если всё так плохо - добавь (удали, измени) поля, таблицы и т.д.).
[/quote]
т.е
"Правильность" построения отменяет компиляцию запроса? Или построение плана выполнения?

ты не замечаешь напрочь.
Следуя правилам нужно приводить таблицы к 3-й нормальной форме ну, минимум ко 2-й
в идеале к 4 (что есть само по себе сферический конь в вакууме)
На практике же встретить даже таблицу приведенную к 3-й нормальной форме - большая редкость, не потому, что не знаем,
а именно для того чтобы оптимизировать, потеряв на избыточности данных
Если всё зависит от данных - оптимизируй структуру данных (если всё так плохо - добавь (удали, измени) поля, таблицы и т.д.).

да нет проблем, я знаю, что "Когда не хватает одной таблицы надо сделать еще 3"
Но ты согласен, что план исполнения одного и того же запроса может быть разный при разных состояниях данных?

а это вообще перл
перестройкой запроса с cross join на left join

естественно повысилась производительность, ты же заменил "перемножение" на "пересечение" :)
т.е. у тебя две таблицы по миллиону записей кросс джойном перемножались
Сколько записей на выходе получалось?
миллион умножить на миллион = триллион
а при лефте всего миллион, как говорится почувствуйте разницу...

З.Ы. А если вернуться к теме - использование большого и сложного запроса, выдающий только необходимую информацию в условиях узкого канала предпочтительнее - не тратится время на коннект и передачу информации. Если же сложный запрос работает медленнее, чем должен - ешь плешь DBA

Да, и желательно, чтобы этот запрос лежал в базе на сервере, а не формировался на клиенте.
В идеале StoredProc на SELECT c некоторыми параметрами
И это темы "Большой запрос работает быстрее чем несколько маленьких" не касается.

А что ты можешь спросить у DBA?
максимум файл трассировки, в котором разбираться все равно тебе придется, запрос то твой.
Ну нет, конечно можно попросить разнести файл данных и транзакшен лог на разные винты,
базу tempDb тоже на отдельный винт положить, ну там дефрагментацию и реиндексацию сделать и все.
Да и с этим обычно у нормальных админов всегда все в порядке.
Ладно, все, смотри постом выше.

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

Re: Помогите с ADO/ODBC/MySQL

Сообщение iGrok » 17.02.2010 (Ср) 22:27

"Правильность" построения отменяет компиляцию запроса? Или построение плана выполнения?

Нет. Просто на одном сложном запросе всё это делается один раз, а на 10 простых - 10. При условии наличия правльной структуры - один сложный займёт примерно в два-три раза больше времени, чем один простой. И в 3-5 раз меньше, чем 10 простых.

а это вообще перл
перестройкой запроса с cross join на left join

естественно повысилась производительность, ты же заменил "перемножение" на "пересечение" :)
т.е. у тебя две таблицы по миллиону записей кросс джойном перемножались

А вот это как раз от таблиц и условий зависит. Кросс двух таблиц, связанных один-к-одному выдаёт наименьшее кол-во записей. Т.е. если в первой 5000, а во второй 500 - получится 500.

Ладно, все, смотри постом выше.

Да пожалуйста. =)
Твоё же мнение. Кому как не тебе при нём оставаться? ,-)
label:
cli
jmp label

FFOX
Новичок
Новичок
 
Сообщения: 44
Зарегистрирован: 04.02.2010 (Чт) 12:42

Re: Помогите с ADO/ODBC/MySQL

Сообщение FFOX » 18.02.2010 (Чт) 8:41

iGrok писал(а):Нет. Просто на одном сложном запросе всё это делается один раз, а на 10 простых - 10. При условии наличия правльной структуры - один сложный займёт примерно в два-три раза больше времени, чем один простой. И в 3-5 раз меньше, чем 10 простых.

Неправильно, простой запрос (он коротенький) попадет в КЕШ, и не будет компилиться
а большой, тоже попадет в кеш, но вероятность того, что он проживет там достаточное время мала.

А вот это как раз от таблиц и условий зависит. Кросс двух таблиц, связанных один-к-одному выдаёт наименьшее кол-во записей. Т.е. если в первой 5000, а во второй 500 - получится 500
.

Ты это, на SQL.ru c таким не ходи, а то там весь народ со смеху перемрет
MsSQL-server 2000 SP3
Код: Выделить всё
declare @t1 table (id int)
declare @t2 table (id int)
declare @i int
SET  @i = 1
WHILE  @i < 5001
BEGIN
     INSERT INTO @t1 values (@i)
     SET @i = @i + 1
END
SET @i = 1
WHILE @i < 501
   BEGIN
       INSERT INTO @t2 values (@i)
      SET @i = @i + 1        
    END

SELECT * FROM @t1 AS t_1
Cross join @t2 AS t_2

SELECT * FROM @t1 AS t_1
Left join @t2 AS t_2 ON(t_2.Id  = t_1.Id)


Сколько записей вернул каждый запрос?

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

Re: Помогите с ADO/ODBC/MySQL

Сообщение iGrok » 18.02.2010 (Чт) 21:30

Зависит от запроса. И в том и в том случае компиляция и т.п. - миллисекунды. А вот с попаданием в кеш - это да.
Впрочем, в случае ТС весь выигрыш от попадания мелких запросов в кэш съест скорость передачи по сети.

FFOX писал(а):
А вот это как раз от таблиц и условий зависит. Кросс двух таблиц, связанных один-к-одному выдаёт наименьшее кол-во записей. Т.е. если в первой 5000, а во второй 500 - получится 500
.

Ты это, на SQL.ru c таким не ходи, а то там весь народ со смеху перемрет
...
Сколько записей вернул каждый запрос?

Да народ вообще любит со смеху помирать не по делу.
Нисколько не вернул. У меня только mysql и мне не на чем это проверить.
А если в теории - Второй - 5к. Первый - 2.5м. Только как и чем это опровергает сказанное мной?
label:
cli
jmp label

FFOX
Новичок
Новичок
 
Сообщения: 44
Зарегистрирован: 04.02.2010 (Чт) 12:42

Re: Помогите с ADO/ODBC/MySQL

Сообщение FFOX » 19.02.2010 (Пт) 13:49

iGrok писал(а):А если в теории - Второй - 5к. Первый - 2.5м. Только как и чем это опровергает сказанное мной?


Вот это опровергает:
А вот это как раз от таблиц и условий зависит. Кросс двух таблиц, связанных один-к-одному выдаёт наименьшее кол-во записей. Т.е. если в первой 5000, а во второй 500 - получится 500.

Не получится, сам только что сказал:
А если в теории - Второй - 5к. Первый - 2.5м.

И ведь правильно то сказал :)
http://olegku.blogspot.com/2010/01/mysql.html
В MySQL INNER JOIN, JOIN и CROSS JOIN семантические эквиваленты и дают декартово произведение строк.

Если автор не врет. Так что никак не получится минимальное кол-во строк.
Ну все, удачи!
Последний раз редактировалось FFOX 19.02.2010 (Пт) 14:13, всего редактировалось 3 раз(а).

Хакер
Телепат
Телепат
Аватара пользователя
 
Сообщения: 16489
Зарегистрирован: 13.11.2005 (Вс) 2:43
Откуда: Казахстан, Петропавловск

Re: Помогите с ADO/ODBC/MySQL

Сообщение Хакер » 19.02.2010 (Пт) 13:51

Хакер
даст
по
шапке
за
оверквотинг
и
за
то,
что
чуть
ли
не
каждое
предложение
начато
с
новой
строки.

Удобно читать?
—We separate their smiling faces from the rest of their body, Captain.
—That's right! We decapitate them.


Вернуться в Visual Basic 1–6

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 21

    TopList