ReSQL.ru
     
powered by simpleCommunicator - 2.0.55     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Оптимизировать вычисления в запросе
25 сообщений из 34, страница 1 из 2
  1  все
Оптимизировать вычисления в запросе
    #39178189
Дмитрий П.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дмитрий П.
Участник
Здравствуйте!

Access 2003: В конструкторе я создал запрос для вычисления значения Y как функции от полей X1 и Х2 в таблице myTable.
Но я хочу чтобы запрос выдавал только значения Y больше 1 и причем в отсортированном виде.

Когда я перешел в Режим SQL то получил:

SELECT X1, X2, (X1 + X2)/2 as Y FROM myTable WHERE (X1 + X2)/2 > 1 ORDER BY (X1 + X2)/2;

Подозреваю, что для исполнения такого запроса, для каждой строки из myTable Access 3 раза вычисляет (X1+X2)/2
Как заставить Access 2003 делать одно вычисление и использовать его результат и для SELECT и для WHERE и для ORDER BY

Спасибо
...
Рейтинг: 0 / 0
24.02.2016, 21:58
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178224
Фотография sdku
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sdku
Участник
Дмитрий П.,
А зачем ??? (чё очень долго,порядка 2 минут,работает)
...
Рейтинг: 0 / 0
24.02.2016, 23:25
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178289
\\\\
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
\\\\
Гость
Дмитрий П.,
Код: sql
1.
SELECT * FROM (SELECT X1, X2, (X1 + X2)/2 as Y FROM myTable) As qsel  WHERE qsel.Y > 1 ORDER BY qsel.Y;
...
Рейтинг: 0 / 0
25.02.2016, 05:51
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178366
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
\\\\Дмитрий П.,
Код: sql
1.
SELECT * FROM (SELECT X1, X2, (X1 + X2)/2 as Y FROM myTable) As qsel  WHERE qsel.Y > 1 ORDER BY qsel.Y;

Вот я тоже так подумала.
Но решила проверить.
Взяла таблицу с одной записьюAB2030Два запроса
1.
Код: sql
1.
SELECT A,B,FuncF(A,B) FROM Таблица1 WHERE FuncF(A,B)>0 ORDER BY FuncF(A,B);

2.
Код: sql
1.
SELECT * FROM (SELECT A,B,FuncF(A,B) AS C FROM Таблица1) AS T WHERE C>0 ORDER BY C;

Функция
Код: vbnet
1.
2.
3.
4.
Function FuncF(A, B) As Integer
 Debug.Print A & "," & B
 FuncF = 1
End Function

Получилось по Debug.Print, что для обоих запросов обращение к функции происходит трижды (при одной записи в таблице).20,30
20,30
20,30

20,30
20,30
20,30
Если же записей в таблице две, то обращений к функции и в одном, и в другом запросе - семь.20,30
20,30
40,50
40,50
40,50
20,30
40,50

20,30
20,30
40,50
40,50
40,50
20,30
40,50
Если записей в таблице три, то получилось по девять обращений к функции20,30
20,30
40,50
40,50
60,70
60,70
60,70
40,50
20,30

20,30
20,30
40,50
40,50
60,70
60,70
60,70
40,50
20,30
В общем, у меня получается, что количество обращений из обоих запросов одинаково.
Посмотрите, пожалуйста, есть ли ошибка в моих "изысканиях"?
...
Рейтинг: 0 / 0
25.02.2016, 09:40
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178405
Vladimir Baskakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Baskakov
Участник
добавить в таблицу колонку и пересчитывать ее при необходимости?
...
Рейтинг: 0 / 0
25.02.2016, 10:25
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178412
Vladimir Baskakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Baskakov
Участник
в конце концов - были вычислимые поля. они скорее всего много раз пересчитываться не станут.
https://support.office.com/ru-ru/article/Добавление-вычисляемого-поля-в-таблицу-14a60733-2580-48c2-b402-6de54fafbde3
...
Рейтинг: 0 / 0
25.02.2016, 10:30
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178461
Фотография sdku
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sdku
Участник
Да, можно порыться в литературе, но лень и узнать КАК выполняется запрос SQL на более низком уровне-сдается мне,что программисты не идиёты и вычисления выполняются без многократных пересчетов
...
Рейтинг: 0 / 0
25.02.2016, 11:03
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178466
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
sdkuДа, можно порыться в литературе, но лень и узнать КАК выполняется запрос SQL на более низком уровне-сдается мне,что программисты не идиёты и вычисления выполняются без многократных пересчетовКак же так?
Эксперимент же показал n-кратное обращение к функции, это и есть многократные пересчеты.
Нет разве?
...
Рейтинг: 0 / 0
25.02.2016, 11:07
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178494
Predeclared
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Predeclared
Участник
__Michelle... Эксперимент же показал ...
Эксперимент показал, что в случае пользования VBA функции...
Для случая НЕ пользования VBA функции он ничего НЕ показал.
:)
...
Рейтинг: 0 / 0
25.02.2016, 11:19
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178509
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Predeclared__Michelle... Эксперимент же показал ...
Эксперимент показал, что в случае пользования VBA функции...
Для случая НЕ пользования VBA функции он ничего НЕ показал.
:)Вычисление выражения с функцией или без функции - все равно вычисление выражения.
Применение функции просто позволяет это отследить.
А как отследить вычисление выражения без функции? Я не знаю.
Если знаете способ, подскажите, пожалуйста.
Может, и обнаружится искомая разница в подходах.
...
Рейтинг: 0 / 0
25.02.2016, 11:26
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178523
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
__MichelleПосмотрите, пожалуйста, есть ли ошибка в моих "изысканиях"?
Поскольку мы не знаем "начинки", трудно искать ошибку.

Я, например, попробовал то же самое, но на другом коде (при той же структуре таблицы, значение поля А строго больше нуля):
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
Public Function Counter(Reset As Long, ParamArray args()) As Long
Static CallsCount As Long
Select Case Reset
    Case 0
        CallsCount = 0
    Case -1
        Counter = CallsCount
    Case Else
        CallsCount = CallsCount + 1
        Counter = Reset
End Select
End Function

Sub test()
Dim rs As Recordset
Dim tmp As Long
tmp = Counter(0)
Set rs = CurrentDb.OpenRecordset("SELECT A,B,Counter(A,B) FROM [Таблица1] WHERE Counter(A,B)>0 ORDER BY Counter(A,B);")
Debug.Print "Calls Count ="; Counter(-1)
tmp = Counter(0)
Set rs = CurrentDb.OpenRecordset("SELECT * FROM (SELECT A,B,Counter(A,B) AS C FROM [Таблица1] WHERE Counter(A,B)>0) AS T ORDER BY C;")
Debug.Print "Calls Count ="; Counter(-1)
tmp = Counter(0)
Set rs = CurrentDb.OpenRecordset("SELECT * FROM (SELECT A,B,Counter(A,B) AS C FROM [Таблица1]) AS T WHERE C>0 ORDER BY C;")
Debug.Print "Calls Count ="; Counter(-1)
tmp = Counter(0)
rs.Close
End Sub

Вызов функции выполняется дважды на количестве записей в таблице до 100 тыс. для всех трёх версий запроса.

Как интерпретировать - пока не понимаю. Но есть подозрение, что пользовательская функция просто считается безусловно недетерминированной.
...
Рейтинг: 0 / 0
25.02.2016, 11:33
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178536
Predeclared
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Predeclared
Участник
__Michelle... А как отследить вычисление выражения без функции? ...
Пробовать менять вычисляемое выражение и отслеживать изменение времени исполнения.
Не?

Например:
изменение выражений в секциях WHERE и ORDER BY на
... WHERE X1 + X2 > 2 ORDER BY X1 + X2
уменьшило время исполнения (на моем стенде) на 20%,
при том-же возвращаемом наборе данных.

А в таком варианте секции ORDER BY:
... ORDER BY 3
время не изменяется, несмотря на отсутствие формулы вычисления в секции.
...
Рейтинг: 0 / 0
25.02.2016, 11:38
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178716
showplan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
showplan
Гость
План запроса для MsAccess дает одинаковые результаты

showplan.out--- q1 ---

- Inputs to Query -
Table 't'
- End inputs to Query -

01) Restrict rows of table t
by scanning
testing expression "x1-x2>0"
02) Sort result of '01)'


--- q2 ---

- Inputs to Query -
Table 't'
- End inputs to Query -

01) Restrict rows of table t
by scanning
testing expression "x1-x2>0"
02) Sort result of '01)'
...
Рейтинг: 0 / 0
25.02.2016, 13:03
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39178986
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Akina
 
__MichelleПосмотрите, пожалуйста, есть ли ошибка в моих "изысканиях"?

Поскольку мы не знаем "начинки", трудно искать ошибку.

Я, например, попробовал то же самое, но на другом коде (при той же структуре таблицы, значение поля А строго больше нуля):
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
Public Function Counter(Reset As Long, ParamArray args()) As Long
Static CallsCount As Long
Select Case Reset
    Case 0
        CallsCount = 0
    Case -1
        Counter = CallsCount
    Case Else
        CallsCount = CallsCount + 1
        Counter = Reset
End Select
End Function

Sub test()
Dim rs As Recordset
Dim tmp As Long
tmp = Counter(0)
Set rs = CurrentDb.OpenRecordset("SELECT A,B,Counter(A,B) FROM [Таблица1] WHERE Counter(A,B)>0 ORDER BY Counter(A,B);")
Debug.Print "Calls Count ="; Counter(-1)
tmp = Counter(0)
Set rs = CurrentDb.OpenRecordset("SELECT * FROM (SELECT A,B,Counter(A,B) AS C FROM [Таблица1] WHERE Counter(A,B)>0) AS T ORDER BY C;")
Debug.Print "Calls Count ="; Counter(-1)
tmp = Counter(0)
Set rs = CurrentDb.OpenRecordset("SELECT * FROM (SELECT A,B,Counter(A,B) AS C FROM [Таблица1]) AS T WHERE C>0 ORDER BY C;")
Debug.Print "Calls Count ="; Counter(-1)
tmp = Counter(0)
rs.Close
End Sub

Вызов функции выполняется дважды на количестве записей в таблице до 100 тыс. для всех трёх версий запроса.

Как интерпретировать - пока не понимаю. Но есть подозрение, что пользовательская функция просто считается безусловно недетерминированной. 1. Да, действительно, считает дважды для каждой записи.
Но так считает только, если открывать рекордсет - только для WHERE и ORDER BY.
2. Если же открывать аналогичный сохраненный запрос, то посчитает трижды для каждой записи - для WHERE, ORDER BY и SELECT.
Если при этом открытый запрос перекрыть другим окном, то при возвращении в окно запроса пересчитает еще по разу для каждой видимой записи.
И так каждый раз.
При закрытии запроса опять пересчитает еще по разу для каждой видимой записи.
3. Если же присваивать аналогичный RecordSource ленточной форме, то посчитает дважды для всех записей (для WHERE и ORDER BY) + столько раз, сколько записей видно (для SELECT).
В форме тоже, как и в открытом запросе, постоянно пересчитывает, если форма не видна/видна.

Какая-то погибель.)))
...
Рейтинг: 0 / 0
25.02.2016, 15:52
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179012
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Predeclared__Michelle... А как отследить вычисление выражения без функции? ...
Пробовать менять вычисляемое выражение и отслеживать изменение времени исполнения.
Не?Можно ли таким образом с уверенностью определить количество проходов вычислений?
Довольно "косвенный" способ. Хотя определенные результаты даст, конечно.
PredeclaredНапример:
изменение выражений в секциях WHERE и ORDER BY на
... WHERE X1 + X2 > 2 ORDER BY X1 + X2
уменьшило время исполнения (на моем стенде) на 20%,
при том-же возвращаемом наборе данных.

А в таком варианте секции ORDER BY:
... ORDER BY 3
время не изменяется, несмотря на отсутствие формулы вычисления в секции.А какова причина уменьшения времени исполнения?
Возможно, не в том, что предположительно сократилось число проходов вычислений,
а в том, что не пришлось расшифровывать 3 и переводить в X1 + X2?
...
Рейтинг: 0 / 0
25.02.2016, 16:03
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179026
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
showplan План запроса для MsAccess дает одинаковые результаты

 
showplan.out--- q1 ---

- Inputs to Query -
Table 't'
- End inputs to Query -

01) Restrict rows of table t
by scanning
testing expression "x1-x2>0"
02) Sort result of '01)'


--- q2 ---

- Inputs to Query -
Table 't'
- End inputs to Query -

01) Restrict rows of table t
by scanning
testing expression "x1-x2>0"
02) Sort result of '01)'
Спасибо за полезную информацию.
...
Рейтинг: 0 / 0
25.02.2016, 16:06
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179259
Predeclared
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Predeclared
Участник
__Michelle... Можно ли таким образом с уверенностью определить ...
С уверенностью можно констатировать то, что Jet дело темное.
:)

__Michelle... А какова причина уменьшения времени исполнения?...
В уменьшении математических операций выражения в секции WHERE, полагаю.
__Michelle... , а в том, что не пришлось расшифровывать 3 и переводить в X1 + X2?
Что означает расшифровывать и переводить?
Я указал сортировать по значениям третьего столбца.
Прироста скорости в замене выражения на номер столбца не обнаружилось.
Сделал вывод: выражение в секции ORDER BY в данном случае не вычисляется.
...
Рейтинг: 0 / 0
25.02.2016, 19:04
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179274
Фотография sdku
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sdku
Участник
вывод:(полностью солидарен с нижесказанным)
PredeclaredС уверенностью можно констатировать то, что Jet дело темное.
:)

Ура,товарищи!
...
Рейтинг: 0 / 0
25.02.2016, 19:22
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179324
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
PredeclaredЯ указал сортировать по значениям третьего столбца.
Прироста скорости в замене выражения на номер столбца не обнаружилось.
Сделал вывод: выражение в секции ORDER BY в данном случае не вычисляется.Проверила.
Выражение в секции ORDER BY вычисляется и тогда, когда задан лишь номер столбца,
выражение берется из определения этого столбца в секции SELECT.
...
Рейтинг: 0 / 0
25.02.2016, 21:17
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179332
Predeclared
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Predeclared
Участник
__Michelle... выражение берется из определения этого столбца в секции SELECT.
Нетъ.
:)
В целях сортировки берется готовый результат выражения.

showplan же показал это.
...
Рейтинг: 0 / 0
25.02.2016, 21:27
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179339
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Но Debug.Print из функции (дополнительно передаю индекс 1 - Select, 2 - Where, 3 - Order By, который и распечатывается)
показывает, что выражение вычисляется заново.
...
Рейтинг: 0 / 0
25.02.2016, 21:37
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179343
Predeclared
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Predeclared
Участник
__MichelleНо Debug.Print из функции ...
...
Рейтинг: 0 / 0
25.02.2016, 21:49
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179351
Predeclared
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Predeclared
Участник
Пример работы оптимизатора с функциями в запросе:
Смотреть попарно:
Запрос1 и Запрос2
Запрос3 и Запрос4
...
Рейтинг: 0 / 0
25.02.2016, 22:13
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179360
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Predeclared,

Ваш пример обязательно посмотрю. Спасибо.

Вот мое.
На основе методики Akina (изменения в функции помечены красным).
Затем в Sub test() описываю рекордсеты с запросом "по нарастанию" -
сначала только SELECT, затем добавляю WHERE, затем добавляю ORDER BY, наконец меняю в ORDER BY выражение на номер столбца.
Выполнение в окне Immediate через call test.
Там все хорошо видно, в какой секции идут вычисления.
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
Public Function Counter(ind As Byte, Reset As Long, ParamArray args()) As Long
 Static CallsCount As Long
 Select Case Reset
    Case 0
        CallsCount = 0
    Case -1
        Counter = CallsCount
    Case Else
        Debug.Print ind
        CallsCount = CallsCount + 1
        Counter = Reset
 End Select
End Function

Sub test()
 Dim rs As DAO.Recordset
 Dim tmp As Long
 
 tmp = Counter(0, 0)
 Debug.Print vbCrLf
 Debug.Print "SELECT A,B,Counter(1,A,B) FROM [Таблица1];"
 Set rs = CurrentDb.OpenRecordset("SELECT A,B,Counter(1,A,B) FROM [Таблица1];")
 Debug.Print "Calls Count ="; Counter(1, -1)
 
 tmp = Counter(0, 0)
 Debug.Print vbCrLf
 Debug.Print "SELECT A,B,Counter(1,A,B) FROM [Таблица1] WHERE Counter(2,A,B)>0;"
 Set rs = CurrentDb.OpenRecordset("SELECT A,B,Counter(1,A,B) FROM [Таблица1] WHERE Counter(2,A,B)>0;")
 Debug.Print "Calls Count ="; Counter(1, -1)
 
 tmp = Counter(0, 0)
 Debug.Print vbCrLf
 Debug.Print "SELECT A,B,Counter(1,A,B) FROM [Таблица1] WHERE Counter(2,A,B)>0 ORDER BY Counter(3,A,B);"
 Set rs = CurrentDb.OpenRecordset("SELECT A,B,Counter(1,A,B) FROM [Таблица1] WHERE Counter(2,A,B)>0 ORDER BY Counter(3,A,B);")
 Debug.Print "Calls Count ="; Counter(1, -1)
 
 tmp = Counter(0, 0)
 Debug.Print vbCrLf
 Debug.Print "SELECT A,B,Counter(1,A,B) FROM [Таблица1] WHERE Counter(2,A,B)>0 ORDER BY 3;"
 Set rs = CurrentDb.OpenRecordset("SELECT A,B,Counter(1,A,B) FROM [Таблица1] WHERE Counter(2,A,B)>0 ORDER BY 3;")
 Debug.Print "Calls Count ="; Counter(1, -1)
End Sub
...
Рейтинг: 0 / 0
25.02.2016, 22:31
| Ответить | Цитировать | Написать  
Оптимизировать вычисления в запросе
    #39179361
Фотография __Michelle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
__Michelle
Участник
Еще:
Для сокращения вывода диагностики в таблице только одна запись.
...
Рейтинг: 0 / 0
25.02.2016, 22:33
| Ответить | Цитировать | Написать  
25 сообщений из 34, страница 1 из 2
  1  все
Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Оптимизировать вычисления в запросе
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=45&msg=39179012&tid=1613866]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 39ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 61ms
get tp. blocked users:
 1ms
others: 17ms
total:  160ms
созд. / загр.: 160ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]