Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
ADODB + SQL запрос (увеличить скорость)
 
#1
13.12.2012 13:45:16
Всем привет!  
Нуждаюсь в вашей помощи.  
Суть идеи, которую я попытался воплотить через ADODB с SQL запросом (возможно и криво но попытался :))  
1. Есть большая база данных, которая хранится в accdb (Access) файле, в которой есть три столбца (Filid, Lid и Pr);  
2. Также есть Эксель файл, в котором только два столбца (Filid и Lid);  
3. Необходимо по двум критериям (Filid и Lid) подтянуть данные (Pr) из accdb в Эксель файл (в соседний столбец);  
Во вложении код, у меня получилось сделать только с помощью цикла.  
Подскажите, пожалуйста – возможно ли уйти от цикла и сделать как-то один массивный SQL запрос?  
Т.к. через цикл получается очень-очень долго.  
Заранее благодарен.  
 
С уважением Богдан.
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#2
13.12.2012 14:45:25
Предлагаю такой вариант - копировать данные из БД в Эксель и опрашивать уже Эксель. :)
 
 
 
#3
13.12.2012 14:46:28
Придумал ещё такой вариант, с помощью словаря, но уже без запроса.  
Просто через ADODB.Recordset  
Но конечно, хотелось бы всё таки без цикла :)
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#4
13.12.2012 14:49:47
В конце процедуры, перед Application.DisplayAlerts = True, забыл поставить:  
 
shTemp.Delele  
 
:)
 
 
 
#5
13.12.2012 14:51:12
И самое главное - столбцы А и B на листе "Лист1" должны иметь заголовки "Filid" и "Lid", соответственно. :)
 
 
 
#6
13.12.2012 15:02:10
И вот тут надо поменять ("+1"):  
shTemp.Cells(1, i + 1) = rs(0).Name
 
 
 
#7
13.12.2012 15:06:28
Спасибо за отклик!  
Первое и самое главное - база на лист не влезет (от 3х млн строк)  
2. Ошибки, которые нашел в Вашем коде:  
For i = 0 To rs.Fields.Count - 1  
shTemp.Cells(1, i) = rs(0).Name  
Как можно записать в нулевой столбец? Правильно: Cells(1, i+1)  
Почему записывается только нулевой заголовок? Правильно: rs(i).Name  
 
shTemp.Range("A1").CurrentRegion = "ACC"  
Наверное в конце должно быть .name? Правильно:CurrentRegion.name = "ACC"  
 
xlConnString = "Provider=Microsoft.ACE.OLEDB.12.0;"  
Уверены, что это правильно? Мы же обращаемся к имени в Эксель
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#8
13.12.2012 15:08:23
Ещё нашёл кучу недочётов. В общем, выкладываю подрихтованный файл. :)
 
 
 
#9
13.12.2012 15:38:21
Можете сюда (sektor81@mail.ru) написать Ваш Skype или ICQ. Просто долго на форуме всё выяснять... Коды безопасности напрягают...
 
 
 
#10
13.12.2012 15:40:40
Спасибо, скайп bogdan_rud
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#11
13.12.2012 20:01:05
можно попробовать так...  
 
(суть метода)  
arr=sh.UsedRange.Columns("a:b").Value  
s="*|"  
for i=1 to ubound(arr)  
 s=s & arr(i,1) & "::" & arr(i,2) & "|"  
next  
s=s &"*"  
 
...  
strSQL = "SELECT filid, lid, pr FROM data WHERE """ & s & """ like ""*|"" filid & ""::"" & lid & ""|*""  
Set rs = cn.Execute(strSql)  
 
...  
в итоге в рекордсете должны быть только нужные записи    
 
пс. в общем-то, я проверил для Access 2003 - такая фигня работает, НО большой вопрос - нет ли ограничений на длину строки s?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#12
13.12.2012 20:13:21
ппс. в общем-то очевидно, но всё же добавлю (на всякий случай) - работает ПРИ УСЛОВИИ, если в кодах filid, lid и в строке s не будет символов, воспринимаемых оператором like как специальные (квадратных скобок, звездочек и т.п.)  
ИНАЧЕ - тоже можно выкрутиться. но надо будет экранировать такие бяки.  
 
если же (как я сначала предположил, поля filid и lid - чисто числовые или буквенные, то теоретически проблем быть не должно.  
 
в принципе, если в строке запроса можно использовать функции VBA (например, InStr), то лучше использовать их.  
примерно так:  
   
strSQL = "SELECT filid, lid, pr FROM data WHERE InStr(""" & s & """, ""|"" filid & ""::"" & lid & ""|"")"  
Set rs = cn.Execute(strSql)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#13
13.12.2012 21:00:55
я бы наверное взял данные с листа и  базы одним запросом, и обычным join сравнил и выплюнул бы готовый рекордсет на лист.
Спасибо
 
 
 
#14
13.12.2012 21:32:13
Богдан, вот пример обращение к данным excel из другого провайдера  
(select Filid, Lid FROM [Лист1#] IN 'Путь к файлу' [Excel 8.0;HDR=YES) b
а далее уже b.filid =.... b.lid=  
Думаю разберешься, будет не понятно буду в скайпе через часок, стучись.  
Дмитрий
Спасибо
 
 
 
#15
13.12.2012 22:21:41
Саша, Дима - спасибо.  
Завтра буду на работе, потестирую оба варианта, заодно и скорость сравню.  
Т.к. хочу найти самый быстрый вариант
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#16
13.12.2012 22:30:10
{quote}{login=ikki}{date=13.12.2012 08:01}{thema=}{post}НО большой вопрос - нет ли ограничений на длину строки s?{/post}{/quote} Кстати, я такой вариант тоже обдумывал, но всё таки хотел вообще уйти от циклов. Надо проверить скорость.  
Ps. Ну, если переменная как Стринг, тогда - от 0 до приблизительно 2 миллиардов знаков в кодировке Юникод.
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#17
13.12.2012 22:36:36
привет. Богдан.  
 
ага. 2 миллиарда - эт хорошо :)  
всё правильно - надо тестировать.  
но, к слову сказать, соединение таблиц - это тоже вложенные циклы.  
особенно с учетом того, что, как минимум, одна из таблиц - из экселя - не проиндексирована ни в одном глазу :)  
 
надеюсь, о результатах отпишешься?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#18
13.12.2012 23:12:46
Да, конечно. Результат и сами коды выложу сюда, может кому-то ещё понадобится
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#19
14.12.2012 03:05:52
R Dmitry  
К сожалению конструкция  
>>select Filid, Lid FROM [Лист1#] IN 'Путь к файлу' [Excel 8.0;HDR=YES) b
С провайдером Microsoft.ACE.OLEDB.12.0 не работает (при использовании Join - возникает "Ошибка синтаксиса в предложении From)  
Такой вариант работает (предполагается, что таблица в Excel на "Лист1" и названия столбцов таблицы совпадают с названиями в Access)  
Public Sub GetData()  
   Dim sConn As String, sSQL As String  
   Dim pConn As Object  
     
   sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\db.accdb;"  
   Set pConn = CreateObject("ADODB.Connection"): pConn.Open sConn  
   sSQL = "Select t1.Filid,t1.Lid,t2.Pr From [Excel 12.0;Database=" & ThisWorkbook.FullName
   sSQL = sSQL & ";HDR=YES].[Лист1$] As t1 Left Join [data] As t2"
   sSQL = sSQL & " On (t1.Filid=t2.Filid) And (t1.Lid=t2.Lid)"  
 
   Worksheets("Лист1").Range("A2").CopyFromRecordset pConn.Execute(sSQL)  
   pConn.Close  
End Sub
 
 
 
#20
14.12.2012 11:32:36
Еще неплохо сделать индексы на полях Filid и Lid в базе.
 
 
 
#21
14.12.2012 14:34:29
Что то обрезалось :)  
Суть такая, я показал пример, как сджоинить можно данные из двух баз.  
------------  
На таких объемах надо ставить ms sqlserver  
хранимкой создавать временную таблицу, джоинить ее с базовой и возвращать результат, про access надо забыть.( все равно MS его хоронит(медленно)с 2007 года)  
:))
Спасибо
 
 
 
#22
14.12.2012 17:28:52
Такс, ну вот, что получилось:  
1й вариант - способ anvg, работает быстрее всего;  
2й вариант - мой способ со словарем, скорость на втором месте;  
3й вариант - это execute-цикл, работает очень долго, можно сразу откинуть;  
 
Итого:  
Acc таблица (700к строк), Xl таблица (30к строк).  
1й вариант - 1сек, 2й вариант - 2сек, 3й вариант - 35сек;  
 
Но, во втором варианте все эти 2секунды происходит залив GetRows в массив, хотел понять как обратится сразу к определенному элементу в GetRows, но ничего не получилось... (может кто-то подскажет?)  
 
Способ Саши, к сожалению, у меня не получилось прикрутить. Может, что-то неправильно делал.  
 
Спасибо всем за помощь и советы!  
Также спасибо Евгению (Волосатый мужык) за консультацию :)
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#23
14.12.2012 19:04:12
ага.  
оч. познавательно.    
Богдан, спасибо за "полевые испытания", учтём на будущее.  
 
>>> Способ Саши, к сожалению, у меня не получилось прикрутить  
имхо, сожалений не стоит - на таком объема (30k) способ обещает быть тормознутым.  
 
тоже хочу сказаать своё спасибо профессионалам - Андрею, Дмитрию, Евгению (в алфавитном порядке, есличо :).
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#24
15.12.2012 14:04:09
>>хотел понять как обратится сразу к определенному элементу в GetRows, но ничего не получилось... (может кто-то подскажет?)  
В этом случае лучше работать не с массивом, а с Recordset  
Измерение коде  
   Dim pRSet As Object  
   '...  
   Set pRSet = CreateObject("ADODB.Recordset")  
   pRSet.CursorLocation = 3  
   pRSet.Open sSQL, pConn  
   Worksheets(1).Range("A2").CopyFromRecordset pRSet  
   'далее настраиваем сортировку для создания временных индексов, в этом случае поиск работает сопоставимо с Dictionary  
   pRSet.Sort = "Filid" 'можно задать несколько полей через ,  
   'настройка поиска  
pRSet.MoveFirst  
pRSet.Find "Flid = "'искомый текст'"  
If Not pRSet.EOF Then Debug.Print PRSet("Filid").Value  
 
Можно и отфильтровать по значениям нескольких полей, используя pRSet.Filter  
Подробнее можно почитать в справке, поищите в Programm Files\Common Files\Microsoft Shared  
файл ADO210.CHM
 
 
 
#25
15.12.2012 15:43:46
anvg, спасибо!  
Буду разбирать  
ps. А может кто-то ещё подскажет хороший учебник по SQL?  
Желательно на русском языке.
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#26
15.12.2012 16:00:50
на русском у меня есть в электронном виде:  
Аткинсон Л. MySQL. Библиотека профессионала.pdf  
Байдачный С. SQL Server 2005.djvu  
Грофф Д. SQL. Полное руководство.djvu  
Дюбуа П. MySQL 4.pdf  
Клайн К. SQL. Справочник.djvu  
Крейн Д. MySQL. Справочник по языку.rar  
Молинаро Э. SQL. Сборник рецептов.rar  
Нильсен П. Microsoft SQL Server 2005. Библия пользователя.pdf  
Ржеуцкая С. Базы данных. Язык SQL.rar  
Справочное руководство по MySQL.chm  
Тоу Д. Настройка SQL для профессионалов.djvu  
Ульман Л. MySQL.djvu  
Фиайли К. SQL. Руководство по изучению языка.rar  
Хендерсон К. Профессиональное руководство по SQL Server. Структура и реализация.djvu  
Хендерсон К. Профессиональное руководство по SQL Server. Хранимые процедуры, XML, HTML.djvu  
Хендерсон К. Профессиональное руководство по Transact-SQL.djvu  
Хернандес М. SQL-запросы для простых смертных.rar  
Шварц Б. MySQL. Оптимизация производительности.rar  
 
какие из этих книг можно назвать "хорошим учебником" - сам бы хотел знать :)  
 
пс. првой (и пока единственной) прочитанной мною была книга Мартина Грубера. Понимание SQL.  
но у меня нет практики :(
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#27
15.12.2012 16:04:51
прямая ссылка на Грубера: http://pv.bstu.ru/database/books/understanding%20sql.pdf
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#28
15.12.2012 16:11:45
Спасибо, скачал Губера. На досуге почитаю.  
А я о SQL знаю только из http://www.firststeps.ru/sql/  
Есть интересные моменты, не этого мало.  
Для практики использую acc-файл как базу, а xl-файл(vbe) как клиент :)
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
Страницы: 1
Наверх