CODEWITHSUNDEEP
mysqloptimizationinventory
Alper OZBEK
Alper OZBEK

Reputation: 89

Optimizing Sql Query For Better Performance

Is it possible to design the following query works more efficiently? I have a inventory table lists purchased and sold products. My aim is to calculate the net remaining quantity of products in our inventory.

select StokKodu, GirenMiktar, Sum(GirenMiktar)-Sum(IF(IrsaliyeliCikanMiktar IS NULL, 0, IrsaliyeliCikanMiktar)) As GorunurKalan,Sum(GirenMiktar)-(Sum(IF(IrsaliyeliCikanMiktar IS NULL, 0, IrsaliyeliCikanMiktar))+Sum(IF(IrsaliyesizCikanMiktar IS NULL, 0, IrsaliyesizCikanMiktar))) As GercekKalan from inventory where StokKodu IN(select StokKodu from inventory where GirenMiktar>0) group by StokKodu

When I purchase a new product. It enters to the table with a new StokKodu (Stock Id) and its quantity is GirenMiktar. Whenever I begin to sell, that there are 2 kinds of procedure that would subtract from GirenMiktar. Selling with Invoice (IrsaliyeliCikanMiktar), selling with WayBill (IrsaliyesizCikanMiktar).

For Example:

Id     StokKodu     GirenMiktar     IrsaliyeliCikanMiktar     IrsaliyesizCikanMiktar
1      SKU1           100
2      SKU1                                  20
3      SKU1                                                                       10
4      SKU2           300
5      SKU2                                  50
6      SKU2                                                                       30

Result Must Be:

StokKodu    GirenMiktar    GorunurKalan    GercekKalan
SKU1          100                80(100-20)        70(100-20-10)
SKU2          300                250(300-50)      220(300-50-30)

Of course there will be a few more description columns such as ProductName, Date etc. in result set that returns from the rows which GirenMiktaris greater than zero.

StokKodu means an Identification describes product.
GirenMiktar means quantity of purchased product.
IrsaliyeliCikanMiktar means quantity of sold product that already issued an invoice.
IrsaliyesizCikanMiktar means quantity of sold product that has not issued an invoice yet.

Many thanks for your helps.

Upvotes: 0

Views: 63

Answers (2)

Alper OZBEK
Alper OZBEK

Reputation: 89

Thank Benni for the guidance. To develop his query offer a little bit solved my issue. My first query above takes about 2.1 seconds. But the query in the following takes about 0.0147 seconds. The difference is awesome.

select 
  * 
from (
    select 
      Tarih, StokKodu, FaturaNo, UrunAdi, GirenMiktar, 
      Sum(coalesce(GirenMiktar,0))
        - Sum(coalesce(IrsaliyeliCikanMiktar, 0)) 
      As GorunurKalan,
      Sum(coalesce(GirenMiktar,0))
        - Sum(coalesce(IrsaliyeliCikanMiktar, 0)) 
        + Sum(coalesce(IrsaliyesizCikanMiktar, 0)) 
      As GercekKalan, 
      Birim, Fiyat, TedarikciId 
    from 
      inventory 
    group by 
      StokKodu 
    order by 
      Tarih Desc
) sonuc 
where 
  GirenMiktar > 0

Upvotes: 0

Benvorth
Benvorth

Reputation: 7722

Your WHERE / IN part is not neccessary. Try this:

select 
    StokKodu, 
    GirenMiktar, 
    Sum(coalesce(GirenMiktar,0))
      -Sum(coalesce(IrsaliyeliCikanMiktar, 0)) As GorunurKalan, 
    Sum(coalesce(GirenMiktar,0))
      - Sum(coalesce(IrsaliyeliCikanMiktar, 0))
      + Sum(coalesce(IrsaliyesizCikanMiktar, 0)) As GercekKalan, 
    Birim, 
    Fiyat, 
    TedarikciId 
from 
    inventory 
group by 
    StokKodu 
order by Tarih Desc;

Upvotes: 0

Related Questions