SQL запрос выполняется непонятно как...

← →
Max Zyuzin © (2005-11-14 14:33) [0]

Приветствую!
Проблема тут появилась есть View вот примерно такого вида...
Извиняюсь за его длинну...
BillsView
SELECT dbo.Bills.bBillDate, dbo.Bills.bBillNumber, dbo.Bills.bShipmentBillNumber, dbo.Bills.bEnumerationBillNumber, dbo.Bills.bInvoiceBillNumber, dbo.Bills.bCodeAgreement, dbo.Bills.bShipmentSum, dbo.CodeAgreement.caConfirmedSum, (SELECT SUM(BillsEnumeration.beSumNoTax) FROM BillsEnumeration WHERE beBillNumber = bBillNumber AND beBillDate = bBillDate AND beBillStation = bBillStationCode) AS BillsEnumerationSumNoTax, (SELECT SUM(BillsEnumeration.beSumTax) FROM BillsEnumeration WHERE beBillNumber = bBillNumber AND beBillDate = bBillDate AND beBillStation = bBillStationCode) AS BillsEnumerationSumTax, (SELECT SUM(BillsEnumeration.beSumNoTax) + SUM(BillsEnumeration.beSumTax) FROM BillsEnumeration WHERE beBillNumber = bBillNumber AND beBillDate = bBillDate AND beBillStation = bBillStationCode) AS BillsEnumerationSumTotal, (SELECT SUM(biInvoiceSumNoTax) FROM BillsInvoice WHERE biBillNumber = bBillNumber AND biBillDate = bBillDate AND biBillStation = bBillStationCode) AS BillsInvoiceSumNoTax, (SELECT SUM(biInvoiceSumTax) FROM BillsInvoice WHERE biBillNumber = bBillNumber AND biBillDate = bBillDate AND biBillStation = bBillStationCode) AS BillsInvoiceSumTax, (SELECT SUM(biInvoiceSumNoTax) + SUM(biInvoiceSumTax) FROM BillsInvoice WHERE biBillNumber = bBillNumber AND biBillDate = bBillDate AND biBillStation = bBillStationCode) AS BillsInvoiceSumTotal FROM dbo.Bills LEFT OUTER JOIN dbo.NSIStation ON dbo.Bills.bBillStationCode = dbo.NSIStation.sNSICode LEFT OUTER JOIN dbo.CodeAgreement ON dbo.Bills.bCodeAgreement = dbo.CodeAgreement.caCode
Вот в обчем если его запустить через QA оно выбирает порядка 30000 записей за 3-4 секунды,
если эту же муть выбирать из программы запросом вида
select * from BillsView where bBillDate between :fd and :td то записи за месяц (5-6 тысяч) выбираются порядка 20 сек :(... как с этим бороться?
Содинение через ADO... провайдер Microsoft OLE DB... запрос выполняется через TADOQuery (не кидайтесь помидорами).
Курсор статический.

← →
ANB © (2005-11-14 14:49) [1]

> select * from BillsView where bBillDate between :fd and
> :td

А сей запрос из QA выполнять пробовали ?

← →
sniknik © (2005-11-14 15:02) [2]

надо переделывать на процедуру или функцию, чтобы параметры работали (сначала ограничить, после по ограниченному набору строить), а не выбирать все и обрезать по ним после...

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

← →
sniknik © (2005-11-14 15:05) [3]

да еще, QA работает с серверным курсором, со всеми из этого вытекающими последствиями.

← →
Nikolay M. © (2005-11-14 15:22) [4]

> надо переделывать на процедуру или функцию, чтобы параметры
> работали

Необязательно, в данном случае параметры уйдут внутрь селекта, так что причина, скорее всего, в другом.
А если попробовать селект без параметров исполнить в QA и в Дельфях?

← →
Max Zyuzin © (2005-11-14 15:38) [5]

Попробовал в прогамме выполнить запрос select * from BillsView результат ~ 5-6 сек. :(
Запрос в QA вида
select * from BillsView where bBillDate between "20050101" and "20051015"
Так же выполняется 3-4 секунды.

← →
Max Zyuzin © (2005-11-14 15:59) [6]

>sniknik © (14.11.05 15:02) [2]
Не совсем понял на счет оптимизации. А насчет переделывания в процедуру... то я даже не думал, что результаты такого View будут столь.. "плачевны"...
Вообще поделитесь мудростью, объясните как правильно создавать подобные запросы… дело в том что в программе в условие запроса могут попасть не только дата но много еще всякой всячины…
Т.е. я обычно делаю таблицу, потом создаю view для ее просмотра со всеми связками что в таблице есть, в программе уже использую запросы непосредственно из представления.

← →
Nikolay M. © (2005-11-14 17:00) [7]

> Max Zyuzin © (14.11.05 15:38) [5]

1) Думаю, что не поможет, но попробуй параметрам сделать Prepare.
2) Тип данных у параметров в Д какой? Подозреваю, что виновато преобразование типа данных из таблицы к типу данных параметра, что лишает возможности использовать индекс по полю bBillDate (надеюсь, что он есть конечно, но даже в случае Table scan лишние преобразования - это лишние тормоза). Случайно, SP4 на сиквеле не установлен?

← →
Nikolay M. © (2005-11-14 17:05) [8]

> А насчет переделывания в процедуру... то я даже не думал,
> что результаты такого View будут столь.. "плачевны"...

В этом запросе нет ничего такого, что требовало бы обертки в ХП. Более того, общераспространенное мнение, что все запросы, будучи обернутыми в ХП с параметрами не имеют ничего общего с реальностью. В этом случае план хоть и строится только при (пере)компиляции ХП и немного ускоряет выполнение ХП по сравнению с запросом, но в большинстве случаев будет неоптимален, т.к. оптимизатор ничего не знает о значении подаваемых ему на вход параметров и выберет тот план, который будет оптимальным для ЛЮБОГО значения параметра и не будет учитывать распределение статистики индекса для данного конкретного значения.

← →
sniknik © (2005-11-14 17:27) [9]

> Не совсем понял на счет оптимизации. А насчет переделывания в процедуру... то я даже не думал,
> что результаты такого View будут столь.. "плачевны"...
в общем то Nikolay M. прав, должно и так правильно работать.
вопрос почему не работает (если не работает. было и такое). ты пробовал открыть это с серверным курсором? если уж с QA сравниваеш, сколько так будет?
ну и попробовать не в твоей программе а в чьей нибудь еще (глюк в реализации...?), например могу свою прислать, провериш (параметры там можно задать, будет один в один как в делфи).

> Вообще поделитесь мудростью, объясните как правильно создавать подобные запросы…
правильно?? х.з. нет у меня никакой методики, вот это правильно, вот это нет. по каждому конкретному случаю смотреть надо. вот тут я бы попытался вынести твое
(SELECT xxx FROM BillsEnumeration WHERE beBillNumber = bBillNumber AND beBillDate = bBillDate AND beBillStation = bBillStationCode)
и присоеденил бы еще одним join-ом. суммы все естественно собрал в один запрос там где условие where у тебя повторяется (а это видно есть ;).

← →
Max Zyuzin © (2005-11-14 17:32) [10]

>Nikolay M. © (14.11.05 17:00) [7]
1. Уже пробовал эффект тот же.
2. SP3 стоит, 4-й не ставил. Индекс есть, мало того кластерный, именно по этому полю, кроме того bBillDate входит в PK таблицы Bills.
Типы параметров не определял явно, отдавал это все ADO делать, запрос формируется динамически. Ща поковыряюсь...

← →
Nikolay M. © (2005-11-14 17:38) [11]

> Типы параметров не определял явно, отдавал это все ADO делать,

Зря. Попробуй формировать весь запрос динамически, без параметров, в точности, как в примере с QA.

> sniknik © (14.11.05 17:27) [9]
> ты пробовал открыть это с серверным курсором?

Первая фраза из [5] говорит о том, что дело не в типе курсора, а в параметрах.

← →
sniknik © (2005-11-14 17:53) [12]

> Первая фраза из [5] говорит о том, что дело не в типе курсора, а в параметрах.
ну проверить то не мешает.
плюс к этому проверить и запрос из вьюшки с добавленными этими самыми параметрами "вчистую" сам по себе без вьюшки.

← →
Nikolay M. © (2005-11-14 18:03) [13]

> sniknik © (14.11.05 17:53) [12]
> плюс к этому проверить и запрос из вьюшки с добавленными
> этими самыми параметрами "вчистую" сам по себе без вьюшки.

Угу, ждем выхода автора.

← →
sniknik © (2005-11-14 20:06) [14]

кстати ... пока с работы ехал "осенило" :о), Disable\EnableControls... тоже ведь вариант.

← →
Max Zyuzin © (2005-11-15 10:10) [15]

>sniknik © (14.11.05 20:06) [14]
Не помогло, так же как и явное забивание запроса без параметров а сразу со значинями.
Целиком SQL запрос из View перенести в программу не пробовал, ибо...

В общем....
Проковырялся я вот до каких интересных весчей... явное задание типов параметров никак не повлияло на процесс... точнее на результат.

Зато очень сильно повлияло задание типа блокировки... LockType я выставлял датасету этому его ltReadOnly - скорость открывания была 35-40 секнунд 2000 записей... поменял на ltOptimistic как и было по умолчанию стало открываться примерно в 10-20 раз быстрее. (<2 сек.)
У меня возвращаемый DataSet не является редактируемым, по этом почитав описания LockType спокойно выставил ему ltReadOnly...

← →
Nikolay M. © (2005-11-15 10:14) [16]

Как-то смахивает на почесывание левой ногой... А есть возможность отпрофайлить процесс открытия вьюхи на сервере?

> ltReadOnly - скорость открывания была 35-40 секнунд 2000
> записей...

...

> почитав описания LockType спокойно выставил ему ltReadOnly.

Не понял, так тебе нужно, чтобы открывалось 35-40 сек?

← →
sniknik © (2005-11-15 10:43) [17]

> Целиком SQL запрос из View перенести в программу не пробовал, ибо...
чего?...

сюда то ты его положил. добавь WHERE ... и выполни. чего сложного то? одна простая проверка.
в программу сложно вставить? так я тебе же предлагал тестовую прислать, куда не сложно, но дело конечно твое.

>Nikolay M. © (15.11.05 10:14) [16]
Не понял, так тебе нужно, чтобы открывалось 35-40 сек?
Нет это типа предистория была, как появился ltReadOnly.

А есть возможность отпрофайлить процесс открытия вьюхи на сервере?
Извиняюсь но это как?

>sniknik © (15.11.05 10:43) [17]
Но имелось ввиду что выбрав другой LockType все забегало. вот. щаз сделаю, для проверки, рез. сообчу

> Max Zyuzin © (15.11.05 10:52) [18]
> >Nikolay M. © (15.11.05 10:14) [16]
> Не понял, так тебе нужно, чтобы открывалось 35-40 сек?
> Нет это типа предистория была, как появился ltReadOnly.

Предыстория понятна. Непонятно, чем выставление ltReadOnly в окончательном результате отличается от 0 поста, если в обоих случаях запрос открывается 40 сек?

>Nikolay M. © (15.11.05 11:15) [20]
А ничем и не отличается в 0-м посте именно ltReadOnly и стояло.

В результате экспериментов нашел интересную весч...

Если весь View скопировать в CommandText и добавить where bBillDate between :fd and :td, явно задать типы параметров то все выполняется опять 30 - 40 сек. 2000 записей. Если сделать что то вроде Select * from (select.......) mt and where bBillDate between :fd and :td
То выбирается все < 2 сек....
Так же как и Select непросредственно из View

> добавить where bBillDate between :fd and :td, явно задать
> типы параметров

А если без параметров, руками написать в тексте конкретные значения?

> Nikolay M. © (14.11.05 17:00) [7]
> лишние преобразования - это лишние тормоза). Случайно, SP4
> на сиквеле не установлен?

А что SP4 мог бы служить причиной такой проблемы?
А то я собираюсь его поставить. Лучше не надо?

SQL запрос выполняется непонятно как... Найти похожие ветки