[SQL] Pohitritev izpisa @ Slo-Tech

Forum » Programiranje »
[SQL] Pohitritev izpisa

[SQL] Pohitritev izpisa

mrsmoke :: 6. sep 2019, 10:41

Zdravo,
Zanima me ali bi bil spodnji problem rešljiv samo s SQL stavki oz. vsaj večina in bi lahko s tem zadevo pohitril.

V MS sql tabeli imamo kartico prometa za vse artikle.
Primer:

- - - - - - - - - - - - - - - - - - - -
| Obdobje | Artikel | Prejem | Izdaja |
- - - - - - - - - - - - - - - - - - - -
|   60    |    1    |   1    |   0    |
|   30    |    1    |   2    |   0    |
|   30    |    1    |   0    |   1    |
|   15    |    1    |   1    |   0    |
|   15    |    1    |   0    |   1    |
- - - - - - - - - - - - - - - - - - - -

Na koncu moram dobiti zalogo teh artiklov po starosti posamezno po FIFO metodi.

- - - - - - - - - - - - - - - - - - - -
| Obdobje | Artikel | Zaloga |
- - - - - - - - - - - - - - - - - - - -
|   30    |    1    |   1    |
|   15    |    1    |   1    |
- - - - - - - - - - - - - - - - - - - -

Trenutno delam tako, da najprej dobim prejeme in jih izvozim v nek seznam (List).
Nato dobim še izdaje in jih odštevam od teh prejemov za vsak posamezni ident, seveda po obdobjih.
Zadeva deluje, vendar zaradi količine podatkov traja 6 minut, kar pa ni v redu.

Če je kakšen sql guru, bi prosil za pomoč oz. vsaj namig.

win64 :: 6. sep 2019, 10:46

Misliš tako?
select obdobje, artikel, sum(prejem) - sum(izdaja)
From promet
GROUP BY obdobje,artikel
ORDER BY obdobje DESC

mrsmoke :: 6. sep 2019, 11:48

win64 je 6. sep 2019 ob 10:46 izjavil:

Misliš tako?
select obdobje, artikel, sum(prejem) - sum(izdaja)
From promet
GROUP BY obdobje,artikel
ORDER BY obdobje DESC

Ne, ker mora izdaja artikla odšteti artikel po FIFO metodi, se pravi iz najstarejšega obdobja kjer je zaloga, ne pa iz istega obdobja.

Tvoj primer bi vrnil rezultat, ki pa ni željen rezultat.

- - - - - - - - - - - - - - -
| Obdobje | Artikel | Zaloga |
- - - - - - - - - - - - - - - 
|   60    |    1    |   1    |
|   30    |    1    |   1    |
- - - - - - - - - - - - - - -

win64 :: 6. sep 2019, 12:48

Imaš še kakšno polje za zaporedno številko na voljo, datum ali kaj podobnega?

GupeM :: 6. sep 2019, 13:51

mrsmoke je 6. sep 2019 ob 10:41 izjavil:

Zdravo,
Zanima me ali bi bil spodnji problem rešljiv samo s SQL stavki oz. vsaj večina in bi lahko s tem zadevo pohitril.

V MS sql tabeli imamo kartico prometa za vse artikle.
Primer:
 - - - - - - - - - - - - - - - - - - - -
 | Obdobje | Artikel | Prejem | Izdaja |
 - - - - - - - - - - - - - - - - - - - -
 | 60 | 1 | 1 | 0 |
 | 30 | 1 | 2 | 0 |
 | 30 | 1 | 0 | 1 |
 | 15 | 1 | 1 | 0 |
 | 15 | 1 | 0 | 1 |
 - - - - - - - - - - - - - - - - - - - -
 
Na koncu moram dobiti zalogo teh artiklov po starosti posamezno po FIFO metodi.
 - - - - - - - - - - - - - - - - - - - -
 | Obdobje | Artikel | Zaloga |
 - - - - - - - - - - - - - - - - - - - -
 | 30 | 1 | 1 |
 | 15 | 1 | 1 |
 - - - - - - - - - - - - - - - - - - - -
 
Trenutno delam tako, da najprej dobim prejeme in jih izvozim v nek seznam (List).
Nato dobim še izdaje in jih odštevam od teh prejemov za vsak posamezni ident, seveda po obdobjih.
Zadeva deluje, vendar zaradi količine podatkov traja 6 minut, kar pa ni v redu.

Če je kakšen sql guru, bi prosil za pomoč oz. vsaj namig.

Tale tvoj primer se mi pa ne zdi pravilen.

V obdobju 60 si namreč prejel 1 komad, v obdobju 30 si prejel še 2 komada, torej skupaj 3 komade. Ker si v obdobju 30 tudi enega izdal, imaš v tem obdobju zato zalogo 2. Ni tako? Če ni, zakaj ni?

Enako velja za obdobje 15. Enega si prejel in enega izdal in bi moral zalogo še vedno imeti 2.

Zgodovina sprememb…

spremenil: GupeM (6. sep 2019 ob 13:51)

darkolord :: 6. sep 2019, 14:11

Na zalogi je imel 1 komad iz obdobja 60, 2 komada iz obdobja 30 in 1 komad iz obdobja 15.

Izdal je 1 komad iz obdobja 60 (najstarejši na zalogi), potem pa še enega iz obdobja 30 (najstarejši na zalogi). Ostalo mu je 1 na 30 in 1 na 15.

Tody :: 6. sep 2019, 14:16

Uporabi with stavek je hitrejši kot sub query

Ce delas z enodnevno zamudo si lahko narrdis tudi materializirane view ali pa temp tabele ce rabis samo za kratek cas

Zgodovina sprememb…

spremenil: Tody (6. sep 2019 ob 14:18)

mrsmoke :: 6. sep 2019, 20:52

win64 je 6. sep 2019 ob 12:48 izjavil:

Imaš še kakšno polje za zaporedno številko na voljo, datum ali kaj podobnega?

Imam zaporedno številko int kot primary key, ter datum prometa.

no comment :: 7. sep 2019, 01:00

Nekako takole? Obdobja sem poimenoval po svoje in pri meni gredo od manjšega k večjem (tako kot v realnem življenju)...

create table ZALOGE_T (OBDOBJE int, ARTIKEL int, PREJEM int, IZDAJA int)
insert
into ZALOGE_T (OBDOBJE, ARTIKEL, PREJEM, IZDAJA)
values
(0, 1, 1, 0),
(1, 1, 2, 0),
(1, 1, 0, 1),
(2, 1, 1, 0),
(2, 1, 0, 1)
;

with TekocaVsota as (
	select distinct
		  OBDOBJE
		, ARTIKEL
		, [IN] = sum(PREJEM) over (partition by ARTIKEL, OBDOBJE)
		, [OUT] = sum(IZDAJA) over (partition by ARTIKEL, OBDOBJE)
		, ZALOGA_RR = sum(PREJEM) over (partition by ARTIKEL order by OBDOBJE desc)
	from ZALOGE_T
),
KoncnoStanjeZalog as (
	select
		  ARTIKEL
		, KONCNO_STANJE = sum(PREJEM) - sum(IZDAJA)
	from ZALOGE_T
	group by ARTIKEL
),
ObdobjeIzcrpanjaZalog as (
	select distinct
		  t1.ARTIKEL
		, OBDOBJE = first_value(OBDOBJE) over (partition by t1.ARTIKEL order by OBDOBJE desc)
	from TekocaVsota t1
	inner join KoncnoStanjeZalog t2 on t1.ARTIKEL = t2.ARTIKEL and t1.ZALOGA_RR >= t2.KONCNO_STANJE
)

select
	  t1.OBDOBJE
	, t1.ARTIKEL
	, ZALOGA =	case
				when (t1.OBDOBJE < t3.OBDOBJE)
					then (0)
				when (t1.OBDOBJE = t3.OBDOBJE)
					then (t2.KONCNO_STANJE - t1.ZALOGA_RR + t1.[IN])
				else (t1.[IN])
			end
from TekocaVsota t1
inner join KoncnoStanjeZalog t2 on t1.ARTIKEL = t2.ARTIKEL
left outer join ObdobjeIzcrpanjaZalog t3 on t1.ARTIKEL = t3.ARTIKEL

Nimam pojma kako hitro to deluje na velikih datasetih. Tudi nisem testiral, če vse deluje z večimi artikli itd...

select *
from ZALOGE_T
------------------------------------------
OBDOBJE	ARTIKEL	PREJEM	IZDAJA
0	1	1	0
1	1	2	0
1	1	0	1
2	1	1	0
2	1	0	1

Rezultat:

------------------------------------------
OBDOBJE	ARTIKEL	ZALOGA
0	1	0
1	1	1
2	1	1

Zgodovina sprememb…

spremenilo: no comment (7. sep 2019 ob 01:08)

smacker :: 7. sep 2019, 09:52

Vsak dan računaš iste stvari. Shranjuj si rezultate za pretekla obdobja v ločeno tabelo, pa jih potem samo dopolnjuješ, tako da v živo računaš le podatke iz tekočega obdobja.

Unilseptij :: 7. sep 2019, 16:45

Tole sem na hitro spravil skupaj in kot kaže deluje na navedenih podatkih

http://sqlfiddle.com/#!9/ac9159/6

.

Bi se dalo še polepšati in skrajšati, ampak sem pisal vse kar inline...

SELECT Obdobje, Artikel, 
CASE 
WHEN SUM(Z.Prejem) - (SELECT SUM(Izdaja) FROM ZALOGE_T AS Z1 WHERE Z1.Artikel=Z.Artikel) + (SELECT IFNULL(SUM(Prejem),0) FROM ZALOGE_T AS Z2 WHERE Z2.Obdobje > Z.Obdobje AND Z2.Artikel=Z.Artikel) <0 THEN 0
WHEN SUM(Z.Prejem) - (SELECT SUM(Izdaja) FROM ZALOGE_T AS Z1 WHERE Z1.Artikel=Z.Artikel) + (SELECT IFNULL(SUM(Prejem),0) FROM ZALOGE_T AS Z2 WHERE Z2.Obdobje > Z.Obdobje AND Z2.Artikel=Z.Artikel) <SUM(Z.Prejem) THEN SUM(Z.Prejem) - (SELECT SUM(Izdaja) FROM ZALOGE_T AS Z1 WHERE Z1.Artikel=Z.Artikel) + (SELECT IFNULL(SUM(Prejem),0) FROM ZALOGE_T AS Z2 WHERE Z2.Obdobje > Z.Obdobje AND Z2.Artikel=Z.Artikel)
ELSE SUM(Z.Prejem)
END AS ZALOGA
FROM ZALOGE_T AS Z
Group by Obdobje, Artikel
order by artikel, obdobje

Zgodovina sprememb…

spremenilo: Unilseptij (7. sep 2019 ob 16:49)

no comment :: 9. sep 2019, 11:16

@Unilseptij, precej slaba ideja, da delaš subselect za vsak zapis. Pa ti nimaš samo enega, ampak mnogo!

mrsmoke :: 9. sep 2019, 13:48

smacker je 7. sep 2019 ob 09:52 izjavil:

Vsak dan računaš iste stvari. Shranjuj si rezultate za pretekla obdobja v ločeno tabelo, pa jih potem samo dopolnjuješ, tako da v živo računaš le podatke iz tekočega obdobja.

Res je, ampak bi moral za vsa leta shraniti vrednosti v tabelo, saj ni nujno da uporabnik izpiše vrednost zalog na današnji dan. Lahko jo npr. na 1.1.2013.
Obstaja možnost, da bomo naredili na tvoj predlagan način.

@Unilseptij rešitev deluje. Potrebuje 3 minute.
@no comment rešitev potrebuje 45 sekund. Končni znesek je pravilen, zneski v posameznem obdobju se pa razlikujejo od pravilnih. Upam da bom danes popoldne imel čas in pa voljo, da se poglobim v tvojo rešitev in jo nekako dodelam.

smacker :: 9. sep 2019, 16:26

Lahko si hraniš tudi vmesna stanja, recimo za vsak mesec. Potem če uporabnika zanima 15.1.2013, vzameš stanje iz 1.1. + zapise za obdobje med 1.1. in 15.1. in za tiste pač izračunaš na enega izmed predlaganih načinov.
Poleg tega pa če te zanima stanje samo na 1 določen dan in ne na vse dneve, potem lahko računaš kot Začetno stanje + vsota vseh prejemov - vsota vseh izdaj, upoštevaš pa zapise vse zapise, ki so pred dotičnim datumom (obdobje ni pomembno). Podobno kot je @win64 predlagal zgoraj, samo da brez SELECT/GROUP BY po obdobjih + v WHERE izbereš le obdobja pred poizvedovanim datumom.

kuall :: 9. sep 2019, 21:43

najhitrejša rešitev bo tmp tabela in dvojni cursor.
najprej v tmp tabelo nafilaš vsa distinct obdobja in seštevek prevzemov in izdaj za obdobje. dodaš še kolono zaloga, to je enostavno izdaja minus prevzem na začetku.
potem pa to kolono zaloga manjšaš. s prvim kurzorjem greš čez vsa obdobja. z drugim kurzorjem greš od našega obdobja do najmlajšega. pri vsakem mlajšem kurzorju vzameš izdajo in zalogo našega obdobja zmanjšaš za to izdajo. če zaloga pade pod 0 ali je 0 nehaš. izdajo pri tem mlajšem obdobju daš na 0 ali v zadnjem primeru jo zmanjšaš za razliko.

no comment :: 9. sep 2019, 23:17

V prvem poskusu sem pozabil najprej zagregirati po obdobjih in artiklih...

with ZalogeK as (
	select
		  OBDOBJE
		, ARTIKEL
		, PREJEM = sum(PREJEM)
		, IZDAJA = sum(IZDAJA)
	from ZALOGE_T
	group by OBDOBJE, ARTIKEL
),
TekocaVsota as (
	select distinct
		  OBDOBJE
		, ARTIKEL
		, [IN] = PREJEM
		, [OUT] = IZDAJA
		, ZALOGA_RR = sum(PREJEM) over (partition by ARTIKEL order by OBDOBJE desc)
	from ZalogeK
),
KoncnoStanjeZalog as (
	select
		  ARTIKEL
		, KONCNO_STANJE = sum(PREJEM - IZDAJA)
	from ZalogeK
	group by ARTIKEL
),
ObdobjeIzcrpanjaZalog as (
	select distinct
		  t1.ARTIKEL
		, OBDOBJE = first_value(OBDOBJE) over (partition by t1.ARTIKEL order by OBDOBJE desc)
	from TekocaVsota t1
	inner join KoncnoStanjeZalog t2 on t1.ARTIKEL = t2.ARTIKEL and t1.ZALOGA_RR >= t2.KONCNO_STANJE
)

select
	  t1.OBDOBJE
	, t1.ARTIKEL
	, ZALOGA =	case
					when (t1.OBDOBJE < t3.OBDOBJE)
						then (0)
					when (t1.OBDOBJE = t3.OBDOBJE)
						then (t2.KONCNO_STANJE - t1.ZALOGA_RR + t1.[IN])
					else (t1.[IN])
				end
from TekocaVsota t1
inner join KoncnoStanjeZalog t2 on t1.ARTIKEL = t2.ARTIKEL
left outer join ObdobjeIzcrpanjaZalog t3 on t1.ARTIKEL = t3.ARTIKEL
order by OBDOBJE, ARTIKEL

Na datasetu z 10mio zapisov select traja 15s (i5 3.2GHz, 12Gb ram, HDD).

Kurzor proti set-based poizvedbi nima nobenih šans.

kuall :: 9. sep 2019, 23:21

Imaš prav. Kar poučna tale tvoja koda.

kuall :: 10. sep 2019, 21:49

Namesto CTEs lahko uporabiš tmp tables in bo hitrost enaka. Edino ta razlika bo, da bo lažje berljivo, lažje si predstavljat in lažje za debuggirat. Se pa da eno pretvorit v drugo zlahka. Torej tele CTEs zgoraj si zlahka predstavljaš kot tmp tabele in je koda lažje berljiva.

Unilseptij :: 11. sep 2019, 22:07

no_comment .... če imaš možnost daj poženi še moj query (zgoraj). Moje mnenje je, da ne bi smel biti bistveno počasnejši, ker tudi jaz grupiram po artiklu in obdobju ter računam samo preproste agregate. S pravo optimizacijo (indeksi, itd.) in pametnim SQL enginom bi zadeva morala delati solidno hitro.

Strinjam se, da pri velikih tabelah kurzor ni rešitev.

kuall :: 11. sep 2019, 22:34

Tvoj je 1,5x počasnejši od njegovega, ki uporablja CTE ali mojega, ki uporablja tmp tabele. 9s vs 14s. Sem jaz na polno tole stestiral, ker mi ni dalo miru, vmes se pa še kaj naučil. Test enostavno tako narediš, da ponoviš primer, ki ga je dal op milijonkrat, vsakič samo povečaš št. artikla.

Kar sem jaz hotel povedat je, da so tmp tabele rešitev tukaj, no ali pa CTE, kar je zadaj ista stvar po moje. Tisto s kurzorji sem mau zajebal, ker se mi ni dalo razmišljat, kaj je bistvo problema tukaj. To pa je, da obstaja točno eno obdoje pri vsakem artiklu, ki ima to lastnost, da imajo vsa starejša obdobja zalogo 0 in vsa mlajša obdobja nespremenjeno zalogo.

Tody :: 12. sep 2019, 09:13

Cursorji so po naravi počasnejši, sicer obstajajo triki, ki jih pospešijo ampak zakaj če imaš lepšo varjanto na voljo :)

no comment :: 12. sep 2019, 09:42

Jaz sem si zgeneriral primer z desetimi zapisi za artikel na eno obdobje. 1000 artiklov in 1000 obdobij da 10mio zapisov.

Uniseptijev select na takih podatkih potrebuje več kot 20min (kljub temu, da sem dodal index). Potrjuje moje zgornje pomisleke o skalabilnosti.

HotBurek :: 12. sep 2019, 12:41

Jst sem sicer out iz tega, ampak vem, da ima MS en software, ki pride zraven. Imenuje se Profiler. Tam se da vidit execution plan, in potem gledat, kakšna je pot izvedbe ter kje se query najdlje časa zadrži.

Če že merite hitrost različnih kverijev na istem datasetu (vsak, ki pri sebi dela teste), bi lahko dodali še ta plan, da se vidi, kaj pravi sql engine in se potem primerja in optimizira.

https://www.mssqltips.com/sqlservertip/...

Pa še knjige, kjer se rešujejo problemi brez kurzorjev :)

https://www.amazon.com/Itzik-Ben-Gan/e/...

root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window

Zgodovina sprememb…

spremenilo: HotBurek (12. sep 2019 ob 12:42)

Unilseptij :: 12. sep 2019, 14:01

no comment je 12. sep 2019 ob 09:42 izjavil:

Jaz sem si zgeneriral primer z desetimi zapisi za artikel na eno obdobje. 1000 artiklov in 1000 obdobij da 10mio zapisov.

Uniseptijev select na takih podatkih potrebuje več kot 20min (kljub temu, da sem dodal index). Potrjuje moje zgornje pomisleke o skalabilnosti.

Hmmmm, sem testiral tudi tvojo poizvedbo in ne vrne tega, kar zahteva OP na podanih podatkih... Vec tule http://sqlfiddle.com/#!18/ac915/1

Kar se tice indeksov, bi jaz naredil 3 indekse, po enega za obdobje ter artikel in kombiniranega za obdobje+artikel. Priznam, da se mi zdi cudno, da kot pravis tvoj query tece 15s... kaj pa vem, to je ogromna razlika.

no comment :: 12. sep 2019, 15:28

Sem povedal, da je moj select napisan za čas kot ga štejemo ljudje. Obdobje 1 je starejše od obdobja 2.

kuall :: 12. sep 2019, 21:39

Execution plane je težko brati, so pa uporabni, da na hitro pogledaš, če obstaja kakšen index scan. Če obstaja in če obenem ta query vzame velik procent časa potem ti manjka index.

Vredno ogleda ...

	Tema	Sporočila	Ogledi	Zadnje sporočilo
	Tema	Sporočila	Ogledi	Zadnje sporočilo
»	SQL query za datume, ki se ne prekrivajo puncka Oddelek: Programiranje	21	2443 (2074)	omnimint 5. jul 2013 20:51:28
»	[SQL] - združitev glede na iste vrednosti tx-z Oddelek: Programiranje	13	1263 (905)	tx-z 29. mar 2013 15:41:39
»	MySQL BETWEEN dates? Housy Oddelek: Programiranje	11	1287 (1201)	Housy 3. dec 2010 23:56:07
»	MYSQL vprašanje l0g1t3ch Oddelek: Programiranje	13	1662 (1277)	MrBrdo 8. maj 2010 20:16:09
»	[SQL] poizvedba problem - obrtnik in njegov najdrazji artikel Fairplay Oddelek: Programiranje	5	1870 (1704)	imagodei 23. jan 2008 15:03:49

Več podobnih tem

Zadnje novice

Zadnji članki

Išči:

Forum » Programiranje »
[SQL] Pohitritev izpisa

[SQL] Pohitritev izpisa