CODEWITHSUNDEEP
exact-onlineinvantive-sqlinvantive-control
Frank Leemkuil
Frank Leemkuil

Reputation: 83

How optimize the performance of SerialNumbers by SalesInvoicesExploded?

I have the following query:

select  sie.invoicedate         sie_invoicedate
,       sie.Silitem             sle_item
,       sie.Silitemcode         sle_itemcode
,       sie.Silitemdescription  sle_itemdescription
,       sie.Silnetprice         sle_netprice
,       sie.Silquantity         sle_quantity
,       sie.Silunitprice        sle_unitprice
,       ctr.ctr_code            ctr_code
,       ctr.ctr_name            ctr_name
,       ctr.parent_code         parent_code
,       ctr.parent_name         parent_name
,       gdlsn.ssrserialnumber   serialnumber
from    SalesInvoicesExploded sie
join    customers@inmemorystorage ctr
on      ctr.ctr_id = sie.invoiceto
join    GoodsDeliveryLineSerialNumbers gdlsn
on      gdlsn.salesorderlineid = sie.silid
where   sie.invoicedate >= '2016-01-01'
and     sie.invoicedate < '2016-01-03'
order
by      sie.invoicedate

How can I get the serial numbers only from the date range? In the debugger I see a lot of requests to Exact Online.

Upvotes: 3

Views: 76

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156938

For now, there isn't a very good filter possibility to get the result you want.

The problem is that there is no way to perform the gdlsn.salesorderlineid = sie.silid filter on the data set unless the data sets have been fetched from the other side.

Only specific filters are executed server-side (like your invoicedate >= '2016-01-01'). This is quite a hard nut to crack from the program side.

It would work if you can specify a filter that can be determined on beforehand, like that the date in GoodsDeliveryLineSerialNumbers.Created always comes after the invoicedate. It would mean a significant performance improvement if you can narrow down the set based on that date.

I suggest to use something like this, if possible:

select  sie.invoicedate         sie_invoicedate
,       sie.Silitem             sle_item
,       sie.Silitemcode         sle_itemcode
,       sie.Silitemdescription  sle_itemdescription
,       sie.Silnetprice         sle_netprice
,       sie.Silquantity         sle_quantity
,       sie.Silunitprice        sle_unitprice
,       ctr.ctr_code            ctr_code
,       ctr.ctr_name            ctr_name
,       ctr.parent_code         parent_code
,       ctr.parent_name         parent_name
,       gdlsn.ssrserialnumber   serialnumber
from    SalesInvoicesExploded     sie
join    customers@inmemorystorage ctr
on      ctr.ctr_id = sie.invoiceto
join    GoodsDeliveryLineSerialNumbers gdlsn
on      gdlsn.salesorderlineid = sie.silid
where   sie.invoicedate >= '2016-01-01'
and     sie.invoicedate < '2016-01-03'
-- add the following line, use a date that for sure will yield the rows:
and     gdlsn.created >= '2015-12-01'
--
order
by      sie.invoicedate

Upvotes: 1

Related Questions