Prijavi se z GoogleID

» »

[SQL] Pohitritev izpisa

[SQL] Pohitritev izpisa

mrsmoke ::

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 ::

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

mrsmoke ::

win64 je 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 ::

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

GupeM ::

mrsmoke je 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 ()

darkolord ::

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.
spamtrap@hokej.si
spamtrap@gettymobile.si

Tody ::

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 ()

mrsmoke ::

win64 je 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 ::

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…

smacker ::

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 ::

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…

no comment ::

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

mrsmoke ::

smacker je 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 ::

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 ::

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 ::

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 ::

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

kuall ::

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 ::

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 ::

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 ::

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 ::

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 ::

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 ()

Unilseptij ::

no comment je 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 ::

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

kuall ::

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 ...

TemaSporočilaOglediZadnje sporočilo
TemaSporočilaOglediZadnje sporočilo
»

PostgreSQL pomoč

Oddelek: Programiranje
161618 (1111) Mato989
»

matematično izrazoslovje

Oddelek: Znanost in tehnologija
161296 (789) gzibret
»

PHP + MySQL excel export pomoč?

Oddelek: Izdelava spletišč
12954 (801) sumoborac
»

MySQL BETWEEN dates?

Oddelek: Programiranje
11696 (610) Housy
»

[SQL] poizvedba problem - obrtnik in njegov najdrazji artikel

Oddelek: Programiranje
51574 (1408) imagodei

Več podobnih tem