ADO + dbf + низкая скорость

[SLIM]

встала задача написать небольшое приложение для вывода отчета.
Значит смысл в чем. Есть два файла dbf. Нужно сначала выбрать записи из 1-ой по критерию (Делал через Open.Recordset). Потом начинаем все эти записи пробегать по одной для нахожедния их же во втором dbf. Если во втором dbf находим (в первом dbf одна запись с данным параметром, во втором уже неизвестно сколько), то начинаем перебирать то что нашли в поиске нужного. Поиск нужного осуществляется путем сравнения каждого значения dbf второго с значениями из Access-а. Вроде понятно объяснил....
Все бы ничего, но тормоза сильные. Когда стал тестировать программу пошагово, увидлел что самый главный тормоз на месте, где мы открываем dbf второй с выбранным из первого критерием для перебора (у него записей 29000 примерно, и это только пока).
Я думал что работать через фокспрошный драйвер будет быстрее, но было все равно долго. Попробовал связать dbf-файлы с БД Access путем связи таблиц, и использовал обычный OLEDB 4.0. Тормозов не убавилось. К сожалению как пользоваться ADO-шным Find и Filter не знаю, но думаю вряд ли поможет. Есть ли какие-то способы ускорить отбор записей по критерию.

[iGrok]

встала задача написать небольшое приложение для вывода отчета.
Значит смысл в чем. Есть два файла dbf. Нужно сначала выбрать записи из 1-ой по критерию (Делал через Open.Recordset). Потом начинаем все эти записи пробегать по одной для нахожедния их же во втором dbf. Если во втором dbf находим (в первом dbf одна запись с данным параметром, во втором уже неизвестно сколько), то начинаем перебирать то что нашли в поиске нужного. Поиск нужного осуществляется путем сравнения каждого значения dbf второго с значениями из Access-а. Вроде понятно объяснил....
Все бы ничего, но тормоза сильные. Когда стал тестировать программу пошагово, увидлел что самый главный тормоз на месте, где мы открываем dbf второй с выбранным из первого критерием для перебора (у него записей 29000 примерно, и это только пока).
Я думал что работать через фокспрошный драйвер будет быстрее, но было все равно долго. Попробовал связать dbf-файлы с БД Access путем связи таблиц, и использовал обычный OLEDB 4.0. Тормозов не убавилось. К сожалению как пользоваться ADO-шным Find и Filter не знаю, но думаю вряд ли поможет. Есть ли какие-то способы ускорить отбор записей по критерию.

Проблема в том, что у dbf нет индекса(кто-нить, поправьте меня =). Точнее, Ado не умеет работать с dbfными индексами. И для отбора по параметру каждый раз просматривается вся вторая Dbfка. Поскольку там 29к записей это, мягко говоря, не быстро.
Работающий вариант - считать всю вторую dbf в рекордсет, и работать уже с ним, в памяти, через Ado.filter и ado.find.
Уверяю тебя, поможет. У меня поиск в dbfке на 80к записей по find или filter почти моментальный. Чуть медленнее - если в find условие вида like.

Драйвер - dBase III; Т.е. открыта аксессовская бд через oledb. Часть Dbf линкована (но это вообще без разницы по скорости), часть - открывается как
select ... from 'file.dbf' in 'c:\path_to_dbf' 'dBase III;'

[SLIM]

Работающий вариант - считать всю вторую dbf в рекордсет, и работать уже с ним, в памяти, через Ado.filter и ado.find.

Если я правильно понял то я так уже пробовал - визуально ничего не изменилось. А понял я так - выбираем все записи (абсолютно все) в RecordSetпотом отбираем что нужно. Дело в том что все в цикле как я уже говорил - вряд ли поможет. Единственно что может помоч...о. можно за пределами цикла открыть dbf второй, а потмо фильтрвать. Не подскажешь синтаксис Filter и find

[iGrok]

Работающий вариант - считать всю вторую dbf в рекордсет, и работать уже с ним, в памяти, через Ado.filter и ado.find.

Если я правильно понял то я так уже пробовал - визуально ничего не изменилось. А понял я так - выбираем все записи (абсолютно все) в RecordSetпотом отбираем что нужно. Дело в том что все в цикле как я уже говорил - вряд ли поможет. Единственно что может помоч...о. можно за пределами цикла открыть dbf второй, а потмо фильтрвать. Не подскажешь синтаксис Filter и find

Эмм.. Естественно, открывать бд нужно только один раз. Вне цикла. )

.Filter = "field_name = field_value AND field_name_1 < field_value_2" и.т.п.
Он мало чем отличается от синтаксиса sql where. Отличия в части инструкций Like и испольовании OR в объединении условий.

.Find = "field_name = field_value"
Та же фигня. Только ставит курсор на первое подходящее значение. Перед первым .Find нужно сделать .MoveFirst.
После .Find желательно проверить действительно ли условие соответствует. Иначе можешь получить "следующую" запись.

Если пользуешь рекурсию - вне цикла нужно открыть один рекордсет, а внутри создавать копии (.clone) и с ними работать.

[Денис]

я понял так:

Код: Выделить всё

Алгоритм:

1) было:
начало цикла
   обращение к dbf (долго)
   действия
конец цикла

2) предлагается:
объявление рекордсета
обращение к dbf (долго)
присвоение выборки рекордсету
начало цикла
   обращение к рекордсету
   действия
конец цикла
присвоение файлу dbf данных из рекордсета (долго)

Понятно, что разница в скорости зависит от итераций (если их 800, то алгоритм 2 (второй) в 800 раз быстрее)

[SLIM]

я понял так:

Верно понял. Я же спросил про синтаксис Find и Filter. Поискал по форума - примеры странные какие-то. Толи я не догоняю....
[Добавлено] Ой извините, не увидел сообщение ранее. Попробовал Filter - почему-то не фильтрует. Пробовал по-разному. Никак.

[iGrok]

я понял так:

Попробовал Filter - почему-то не фильтрует. Пробовал по-разному. Никак.

Что значит "не фильтрует"? Код в студию.

[SLIM]

полностью код представить не смогу - не будет иметь смысла, его лучше пошагово исполнять.
Могу показать как я делал фильтрацию
1.Вариант как было раньше

Код: Выделить всё

'Каждый раз в цикле открываем рекордсет с параметром отбора
    RsSHet.Open "Select * from RSCHT where NSCH='" & RsSchet("NSCH").Value & "';", CnDb, adOpenDynamic, adLockOptimistic
    If RsSHet.RecordCount > 0 Then
        Do While Not RsSHet.EOF
        ...............
        Loop
    End if

И так несколько раз в цикле (столько сколько нужно будет)
2.Вариант с фильтром

Код: Выделить всё

RsSHet.Open "Select * from RSCHT;", CnDb, adOpenDynamic, adLockOptimistic
    'Рекордсет один раз а потом в цикле фильтруем что нужно
    RsSHet.Filter = "NSCH=" & .....некий критерий из другого рекордсета
    If RsSHet.RecordCount > 0 Then
        Do While Not RsSHet.EOF
        ...............
        Loop
    End if
    RsSHet.Filter = 0

Соответственно первый вариант работает, а второй нет.
И еще. Как оказалось с обновленными данными таблица теперь содержит порядка 29 000 записей

[iGrok]

"Не работает" это что значит? RecordCount = 0 ?
Данные в обоих случаях одинаковые?

[SLIM]

"Не работает" это что значит? RecordCount = 0 ?

Да.В первом случаем RecordCount>0, а во втором =0.
Я даже подумал что все дело в клиентском курсоре. Поменял курсор, сделал проверку на наличие записей после фильра методом

Код: Выделить всё

If Not RsSHet.EOF And Not RsSHet.BOF Then

. Итог тот же.
Потом я решил проверить вручную находятся лизаписи. Остановил программу на шаге, где определяется критерий фильтра. Открыл таблицу и через Ctrl+F попробовал найти таки запись. Хе хе. Не нашел. Попробовал поискать совпадение с любой частью поля.....хе хе. Нашел.
Далее попробовал указать в фильтре Like. Даже попробовал очистить от пробелов критерий. Тут толи Like не верно проинструктировал, толи еще что-то. Like делал так

Код: Выделить всё

RsSHet.Find "NSCH LIKE " & RsSchet("NSCH")

и так

Код: Выделить всё

RsSHet.Find "NSCH LIKE " & Trim$(RsSchet("NSCH").Value)

Но вспомнил, что в SQL нужно задавать Like со звездочкой, т.е.

Код: Выделить всё

Like *Критерий

либо

Код: Выделить всё

like *Критерий*

Но при добавлении звездочек VB писал ошибку. Нашел где-то что можно добавлять знак процента. Пробовал - все бесполезно. Смотрел другие примеры- в других кодах Like работет (ну по крайней мере никто не жаловался)

[iGrok]

Мда.. Ладно. Сейчас примерчик наваяю..

О. Даже ваять ничего не надо. Писал простенький аксессозаменитель для работы.. )

Там же лежит пример dbf-ки. z1.dbf
Открой её, выбери какое-нить повторяющееся значение и нажми "фильтр". Исходники прилагаются.

З.Ы. "Column Like %A%"
Можешь там же посмотреть, как сделан "расширенный фильтр"..

[SLIM]

З.Ы. "Column Like %A%"

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

[iGrok]

Вообще странно, что медленно... Как бд открываешь?

[SLIM]

Мда.....Медленно не то слово. По одному условию фильтрация производится 128 раз. Обработка всего цикла присходит примерно секунд за 40 а то и за минуты полторы. В общем я всетки прикладываб код - может быть где-то я неправильно сделал. На некоторые косяки не обращай внимания, код только в разработке.

Код: Выделить всё

Public Sub QueryFuriniture(ByVal DateQuery As String, ByRef ErrCode As Long)
Dim CnDb As New ADODB.Connection

Dim RsSchet As New ADODB.Recordset
Dim RsSHet As New ADODB.Recordset
Dim RsPrice As New ADODB.Recordset
Dim NowDateFotmat As String

Dim StrConn As String

StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\Base.mdb"
NowDateFotmat = Replace$(DateQuery, ".", "/")


[Добавлено] Пробовал с твоим проектом - все быстро. Но там немного другие условия. Сам видишь какие бешанные циклы у меня стоят...

    CnDb.CursorLocation = adUseClient
    CnDb.Open (StrConn)
    RsSchet.Open "Select * from RSCHET where DOPL=#" & NowDateFotmat & "#;", CnDb, adOpenDynamic, adLockOptimistic
    If RsSchet.RecordCount > 0 Then
        RsSHet.Open "RSCHT", CnDb, adOpenDynamic, adLockOptimistic
        Do While Not RsSchet.EOF
            RsSHet.Filter = "NSCH Like %" & Trim$(RsSchet("NSCH").Value) & "%"
            If RsSHet.RecordCount > 0 Then
                Do While Not RsSHet.EOF
                    RsPrice.Open "Select * from Price where Name='" & RsSHet("KODTOV").Value & "';", CnDb, adOpenDynamic, adLockOptimistic
                    If RsPrice.RecordCount > 0 Then
                        CnDb.Execute "INSERT INTO DataMake(SChet,DateMakeSh,Pay) SELECT '" & RsSHet("NSCH").Value & "','" & NowDateFotmat & "','" & RsSchet("SUMITOG").Value & "';"
                        RsPrice.Close
                        Exit Do
                    End If
                    RsPrice.Close
                    RsSHet.MoveNext
                Loop
            End If
            RsSHet.Filter = 0
            RsSchet.MoveNext
        Loop
    Else
        ErrCode = ERROR_DATE_NONE
        Exit Sub
    End If
    RsSchet.Close
    CnDb.Close
    Set CnDb = Nothing
    Set RsSchet = Nothing
    Set RsSHet = Nothing
    Set RsPrice = Nothing

End Sub

Ограничить количество данных для фильтрации невозможно.

[Добавлено] Пробовал через твой проект. Но там немного не те условия. Фильтрация то та же. Но ты же видишь какие у меня бешанные циклы

[iGrok]

1) Зачем тебе динамический курсор (adOpenDynamic)? В бд что-то может поменяться, пока ты её обрабатываешь? Не уверен, что это сильно повлияет на скорость.. Но всё же поставь лучше adOpenStatic.

2) Like - он в принципе медленный. Что у тебя там за формат полей и записей, что не работает " = " ?

3) Я надеюсь, Price, DataMake - это аксессовские таблицы? )

4) Используй всё-таки нормальный синтаксис SQL..
CnDb.Execute "INSERT INTO DataMake (SChet, DateMakeSh, Pay) VALUES ('" & RsSHet("NSCH").Value & "', '" & NowDateFotmat & "', '" & RsSchet("SUMITOG").Value & "');"

5) Далее, вот это что такое?
RsSHet.Open "RSCHT", CnDb, adOpenDynamic, adLockOptimistic
Это аналог SELECT * FROM RSCHT ?

6) Ещё создаётся ощущение, что у тебя там есть что-то лишнее.. Сейчас времени нет разбирать код - вечером посмотрю и отпишусь.

[SLIM]

1) Зачем тебе динамический курсор (adOpenDynamic)? В бд что-то может поменяться, пока ты её обрабатываешь? Не уверен, что это сильно повлияет на скорость.. Но всё же поставь лучше adOpenStatic.

Да, угадал, в БД может что-то поменяться. Но впринципе это не критично, попробую поменять

2) Like - он в принципе медленный. Что у тебя там за формат полей и записей, что не работает " = " ?

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

3) Я надеюсь, Price, DataMake - это аксессовские таблицы? )

Да, так и есть

4) Используй всё-таки нормальный синтаксис SQL..
CnDb.Execute "INSERT INTO DataMake (SChet, DateMakeSh, Pay) VALUES ('" & RsSHet("NSCH").Value & "', '" & NowDateFotmat & "', '" & RsSchet("SUMITOG").Value & "');"

Ну это тоже не так критично. Исправлю.

5) Далее, вот это что такое?
RsSHet.Open "RSCHT", CnDb, adOpenDynamic, adLockOptimistic
Это аналог SELECT * FROM RSCHT ?

Да это его аналог, причем раьотает в сотни раз быстрее чем через Select. Программа даже не останавливается на этом месте. А вот с Select значительно подвисает на таком то киличестве записей...

6) Ещё создаётся ощущение, что у тебя там есть что-то лишнее.. Сейчас времени нет разбирать код - вечером посмотрю и отпишусь.

Если честно то такое же ощущение-но смотрю как баран на новые ворота и не могу это лишнее понять. В коде есть что-то не так и это факт.



[Добавлено] В общем я так понял работа с dbf без косяков не обходится. То работает то нет что-то. Значит после применения фильтра скорось увеличилось в полтора раза почти. Конечно это большой выигрыш. Но все равно этого мало. И если честно я не знаю как возможно еще увеличить скорость. Есть консольное приложение, работающее с этими файлами. Не скажу что оно работает супер быстро, но быстрее чем если бы с ними работал я. Начинаю думать что методами ADO не справиться, возможно есть другой способ. Пробовал кстати не связывать таблицы в Access а просто импортировал и проделывал тоже самое. Итог один и тот же. Скорости не прибавилось.

[iGrok]

Слушай.. Вот правильно я углядел, что там много лишнего.. )

Это же всё делается вообще одним запросом к бд (insert into... () select .. from ... where)!

Либо, как вариант, весь список отбирается одним, по сложному условию (типа "where rschet.nsch like rscht.nsch AND price.name = rscht.kodtov AND rschet.dopl = NowDateFormat") а потом по нему проходишь, и делаешь кучу инсертов..
Как пользоваться join'ами, или условиями where знаешь?

Попозже напишу, как это выглядит...

[UPD:]
Примерно так:
"INSERT INTO [DataMake] ([Schet], [DateMakeSh], [Pay]) SELECT [RSCHT.NSCH], '" & NowDateFormat & "', [RSCHET.SUMITOG] FROM [RSCHET], [RSCHT], [PRICE] WHERE [RSCHET.NSCH] Like [RSCHT.NSCH] AND [RSCHET.DOPL] = '" & NowDateFormat & "' AND [Price.Name] = [RSCHT.KODTOV]"

Возможно, где-то накосячил. Сделай тестовую бд и сравни результат выполнения с тем, что делается твоим кодом.. =)

[SLIM]

Идея понятна. Щас попробую. Что-то мне даже в голову не приходило...

[SLIM]

Возможно, где-то накосячил. Сделай тестовую бд и сравни результат выполнения с тем, что делается твоим кодом.. =)

Да, подвело меня конечно знание SQL!!!!
Спасибо тебебольшое iGrok. Респект тебе и уважуха!!!! Я бы и не подумал даже что буду делать такими средствами (мышление видимо пока не в ту сторону). Спасибо!!!!