Optymalizacja zapytania SQL

0

Witam,
Zwracam się z ogromną prośbą o sprawdzenie poniższego kodu SQL i udzielenie pomocy w jego optymalizacji - czy jest w ogóle taka możliwość?
Czy da się to napisać bardziej optymalnie - zarówno w kontekście wydajności jak również długości tekstu. Jest to tylko fragment zapytania, który stworzyłem a mam ograniczoną liczbę znaków w programie w którym je umieszczę i mam małe obawy, że mogę się nie wyrobić :-/.

Niestety SQL nie jest moją mocną stroną.

SELECT SUM(DeK_Kwota) FROM CDN_Testowa.CDN.DekretyKonta	WHERE DeK_Strona = 1 AND DeK_DataDok <= 
		(SELECT EDN_DataOpe FROM [CDN_Testowa].[CDN].[EwidDodNag] WHERE EDN_NumerPelny = 'EDK/1/2015') 
	AND DeK_AccId = (
		SELECT Acc_AccId FROM CDN_Testowa.CDN.Konta WHERE Acc_Numer = (
			SELECT Kat_KontoSegmentWN FROM CDN_Testowa.CDN.Kategorie WHERE Kat_KatID = (
				SELECT EDN_KatID FROM [CDN_Testowa].[CDN].[EwidDodNag] WHERE EDN_NumerPelny = 'EDK/1/2015'))
			AND Acc_OObId = (
				SELECT OOb_OObID FROM CDN_Testowa.CDN.OkresyObrach	WHERE OOb_DataOtw <= (
					SELECT EDN_DataOpe FROM [CDN_Testowa].[CDN].[EwidDodNag] WHERE EDN_NumerPelny = 'EDK/1/2015') 
					AND OOb_DataKoncowa >= (
						SELECT EDN_DataOpe FROM [CDN_Testowa].[CDN].[EwidDodNag] WHERE EDN_NumerPelny = 'EDK/1/2015')))

Głównie chodzi o to czy da się jakoś ograniczyć te zapytania - np. gdy w ostatniej linijce porównuję czas czy nie jest >= lub <= to tworzę dwa podzapytania z tej samej tabeli no i zastanawiam się czy nie dałoby się tego zrobić jednym zapytaniem?

Mam nadzieję, że wszystko jest czytelne (wcięcia odnoszą się do kolejnego podzapytania).
Aha, i ten numer 'EDK/1/2015' to tylko przykładowy string w celu sprawdzenia czy zapytanie działa - później zostanie zmieniony na zmienną.
No i oczywiście pomijam kwestię wyrzucenia nawiasów kwadratowych, które w tym przypadku są zbędne.

Z góry bardzo dziękuję za wszelką pomoc.

1

jak się gdzieś nie walnąłem to powinno być to

SELECT
  SUM(d.DeK_Kwota)  
FROM
  [CDN_Testowa].[CDN].[DekretyKonta] d,
  [CDN_Testowa].[CDN].[EwidDodNag] n,
  [CDN_Testowa].[CDN].[Konta] k,
  [CDN_Testowa].[CDN].[Kategorie] a,
  [CDN_Testowa].[CDN].[OkresyObrach] o  
WHERE
  d.DeK_Strona = 1   
  AND n.EDN_NumerPelny = 'EDK/1/2015'
  AND d.DeK_DataDok <= n.EDN_DataOpe
  AND n.EDN_KatID = a.Kat_KatID
  AND o.OOb_DataOtw <= n.EDN_DataOpe
  AND o.OOb_DataKoncowa >= n.EDN_DataOpe
  AND k.Acc_OObId = o.OOb_OObID
  AND a.Acc_Numer = k.Acc_Numer

Wydajnościowo nie wiem, które będzie lepsze - trzeba by sprawdzić, chociaż podzapytania zazwyczaj są wolniejsze.

A co do wcięć (bo Twoje naprawdę nie poprawiają czytelności) to można to zapisać np. tak http://poorsql.com/

SELECT SUM(DeK_Kwota)
FROM CDN_Testowa.CDN.DekretyKonta
WHERE DeK_Strona = 1
	AND DeK_DataDok <= (
		SELECT EDN_DataOpe
		FROM [CDN_Testowa].[CDN].[EwidDodNag]
		WHERE EDN_NumerPelny = 'EDK/1/2015'
		)
	AND DeK_AccId = (
		SELECT Acc_AccId
		FROM CDN_Testowa.CDN.Konta
		WHERE Acc_Numer = (
				SELECT Kat_KontoSegmentWN
				FROM CDN_Testowa.CDN.Kategorie
				WHERE Kat_KatID = (
						SELECT EDN_KatID
						FROM [CDN_Testowa].[CDN].[EwidDodNag]
						WHERE EDN_NumerPelny = 'EDK/1/2015'
						)
				)
			AND Acc_OObId = (
				SELECT OOb_OObID
				FROM CDN_Testowa.CDN.OkresyObrach
				WHERE OOb_DataOtw <= (
						SELECT EDN_DataOpe
						FROM [CDN_Testowa].[CDN].[EwidDodNag]
						WHERE EDN_NumerPelny = 'EDK/1/2015'
						)
					AND OOb_DataKoncowa >= (
						SELECT EDN_DataOpe
						FROM [CDN_Testowa].[CDN].[EwidDodNag]
						WHERE EDN_NumerPelny = 'EDK/1/2015'
						)
				)
		)

lub tak http://www.freeformatter.com/sql-formatter.html

SELECT
  SUM(DeK_Kwota)  
FROM
  CDN_Testowa.CDN.DekretyKonta  
WHERE
  DeK_Strona = 1   
  AND DeK_DataDok <= (
    SELECT
      EDN_DataOpe    
    FROM
      [CDN_Testowa].[CDN].[EwidDodNag]    
    WHERE
      EDN_NumerPelny = 'EDK/1/2015'    
  )   
  AND DeK_AccId = (
    SELECT
      Acc_AccId    
    FROM
      CDN_Testowa.CDN.Konta    
    WHERE
      Acc_Numer = (
        SELECT
          Kat_KontoSegmentWN      
        FROM
          CDN_Testowa.CDN.Kategorie      
        WHERE
          Kat_KatID = (
            SELECT
              EDN_KatID        
            FROM
              [CDN_Testowa].[CDN].[EwidDodNag]        
            WHERE
              EDN_NumerPelny = 'EDK/1/2015'        
          )      
        )     
        AND Acc_OObId = (
          SELECT
            OOb_OObID      
          FROM
            CDN_Testowa.CDN.OkresyObrach      
          WHERE
            OOb_DataOtw <= (
              SELECT
                EDN_DataOpe        
              FROM
                [CDN_Testowa].[CDN].[EwidDodNag]        
              WHERE
                EDN_NumerPelny = 'EDK/1/2015'        
            )       
            AND OOb_DataKoncowa >= (
              SELECT
                EDN_DataOpe        
              FROM
                [CDN_Testowa].[CDN].[EwidDodNag]        
              WHERE
                EDN_NumerPelny = 'EDK/1/2015'        
            )      
          )    
        )
1
  1. Naucz się SQL.
  2. Coś albo jest optymalne, albo nie jest optymalne. To nie jest skala. Powiedzieć "bardziej optymalne" to jakby powiedzieć "bardziej najlepsze".
  3. Jeśli program łączy się bezpośrednio z CDN_Testowa, to można usunąć
CDN_Testowa.
  1. Bez schematu i opisu problemu wywróżyłem tak:
DECLARE @EDN_NumerPelny varchar(10)
SET @EDN_NumerPelny = 'EDK/1/2015'

SELECT SUM(DeK.DeK_Kwota)
FROM CDN_Testowa.CDN.EwidDodNag		 EDN
INNER JOIN CDN_Testowa.CDN.OkresyObrach OOb ON OOb.OOb_DataOtw <= EDN.EDN_DataOpe AND OOb.OOb_DataKoncowa >= EDN.EDN_DataOpe
INNER JOIN CDN_Testowa.CDN.Kategorie	Kat ON Kat.Kat_KatID = EDN.EDN_KatID
INNER JOIN CDN_Testowa.CDN.Konta		Acc ON Acc.Acc_OObId = OOb.OOb_OObID AND Acc.Acc_Numer = Kat.Kat_KontoSegmentWN
INNER JOIN CDN_Testowa.CDN.DekretyKonta DeK ON DeK.DeK_Strona = 1 AND DeK.DeK_DataDok <= EDN.EDN_DataOpe AND DeK.DeK_AccId = Acc.Acc_AccId 
WHERE EDN.EDN_NumerPelny = @EDN_NumerPelny
0

@abrakadaber Dziękuję Ci ślicznie za pomoc!!!
Rzeczywiście Twój kod wygląda "troszkę" czytelniej ;-)
Jak wrócę do domu to od razu go wypróbuję czy działa ;D.
Kombinowałem w podobny sposób (z aliasami), ale gdzieś robiłem błąd, który powodował, że nie działało i błędnie doszedłem do wniosku, że to zła droga :/.

Przy okazji zapytam: kiedy należy stosować aliasy a kiedy podzapytania w klauzuli WHERE ew. kiedy klauzulę JOIN?!
Wszystkie metody chyba dają ten sam wynik tylko, że Twoje rozwiązanie z aliasami jest zdecydowanie czytelniejsze i krótsze!!!

j_s_r_n napisał(a):
  1. Naucz się SQL.
  1. Uczę się.
  2. Jestem księgowym (jak sama nazwa nicku sugeruje) a nie programistą, więc proszę o wyrozumiałość.
  3. Umiem dość dobrze (jak na księgowego) programować w C++, ale SQL do tej pory nie był mi w zasadzie do niczego potrzebny...
j_s_r_n napisał(a):

Coś albo jest optymalne, albo nie jest optymalne. To nie jest skala. Powiedzieć "bardziej optymalne" to jakby powiedzieć "bardziej najlepsze".

Nie zgodzę się. To jest tak jakby coś było dobre/złe a chcieć zrobić coś lepiej. Jeśli moje rozwiązanie jest złe, słabe lub dobre, ale nie najlepsze to bardziej optymalnie oznacza po prostu jeszcze lepsze - chociaż nie koniecznie najlepsze (optymalne).

j_s_r_n napisał(a):

Jeśli program łączy się bezpośrednio z CDN_Testowa, to można usunąć

Tak, masz rację. Jednak dlatego, że najpierw testuję te zapytania w SQL Managment-cie to muszę podawać nazwę bazy danych do której się odnoszę. Docelowo zamierzałem usunąć nazwę bazy danych i nawiasy kwadratowe.

j_s_r_n napisał(a):

Bez schematu i opisu problemu wywróżyłem tak: (...)

No tak, ale w zasadzie czym różni się Twoje rozwiązanie od mojego? Pod jakim względem jest ono lepsze od mojego? Czy JOIN-y są wydajniejsze czy chodzi o coś innego? Jak ocenisz rozwiązanie, które przedstawił @abrakadaber?
Powtórzę również pytanie: kiedy należy stosować aliasy, WHERE a kiedy JOIN? Dla mnie osobiście najlepiej wyglądają aliasy -> porównując swój kod i Twój z tym co przedstawił @abrakadaber nie ma nawet porównania!

Jeszcze raz dziękuję i pozdrawiam.

1

Aliasy ułatwiają czytanie i pisanie, więc należy stosować je tam, gdzie .... ułatwiają czytanie i pisanie :)
Join używa się do łączenia tabel, a where do warunków. Tak w uproszczeniu, generalizując :)

1

dokładnie - aliasy po prostu skracają zapis. Podzapytania należy używać tylko wtedy kiedy to konieczne, nie powinno się ich używać tak jak Ty to zrobiłeś, np. tu

Acc_OObId = (
          SELECT
            OOb_OObID      
          FROM
            CDN_Testowa.CDN.OkresyObrach      
          WHERE
            OOb_DataOtw <= (
              SELECT
                EDN_DataOpe        
              FROM
                [CDN_Testowa].[CDN].[EwidDodNag]        
              WHERE
                EDN_NumerPelny = 'EDK/1/2015'        
            )       
            AND OOb_DataKoncowa >= (
              SELECT
                EDN_DataOpe        
              FROM
                [CDN_Testowa].[CDN].[EwidDodNag]        
              WHERE
                EDN_NumerPelny = 'EDK/1/2015'        
            )      
          )

czyli kiedy wynik podzapytania jest przyrównywany do jakiejś wartości - teraz działa ale może się zdarzyć że dla jakiegoś EDN_NumerPelny zostanie zwrócone kilka rekordów OOb_OObID i wtedy wszystko się wysypie. W większości przypadków podzapytania wydłużają czas całego zapytania, tzn. zapisanie tego samego z podzapytaniami i bez będzie się wykonywało szybciej bez podzapytań (chociaż znam przypadki, gdzie było odwrotnie).

Następnie zapis

select a.*, b.* from tabA a, tabB b where a.id = b.id

jest równoważny zapisowi

select a.*, b.* from tabA a inner join tabB b on a.id = b.id

i dla bazy z "poważnym" optymalizatorem nie ma różnicy bo plan wykonania będzie taki sam. Natomiast już LEFT/RIGHT/OUTER JOIN to jest inna sprawa i nie da się ich inaczej zapisać. Ja preferuję zapis z WHERE zamiast INNER JOIN bo jest dla mnie czytelniejszy. Generalnie trzeba trochę wyczucia z joinami bo może się okazać, że coś co powinno działać nie działa (chodzi o to, które warunki można umieścić po ON a które trzeba po WHERE)

1

Przeprasza za to

  1. Naucz się SQL.

Nie lubię emoticon i jak na to teraz patrzę to wygląda jak rozkaz, albo nagana. Chodziło mi bardziej o to, że skoro i tak będziesz go używał to łatwiej ci będzie pracować, jak go będziesz znał.

Zgodnie ze słownikiem

optymalny - najlepszy z możliwych w jakichś warunkach

USE CDN_Testowa

sprawi, że kolejne zapytania będą wykonywane w kontekście bazy CDN_Testowa, czyli w SSMS można tak:

USE CDN_Testowa
DECLARE @EDN_NumerPelny VARCHAR(10)
SET @EDN_NumerPelny = 'EDK/1/2015'
 
SELECT SUM(DeK.DeK_Kwota)
FROM CDN.EwidDodNag         EDN
INNER JOIN CDN.OkresyObrach OOb ON OOb.OOb_DataOtw <= EDN.EDN_DataOpe AND OOb.OOb_DataKoncowa >= EDN.EDN_DataOpe
INNER JOIN CDN.Kategorie    Kat ON Kat.Kat_KatID = EDN.EDN_KatID
INNER JOIN CDN.Konta        Acc ON Acc.Acc_OObId = OOb.OOb_OObID AND Acc.Acc_Numer = Kat.Kat_KontoSegmentWN
INNER JOIN CDN.DekretyKonta DeK ON DeK.DeK_Strona = 1 AND DeK.DeK_DataDok <= EDN.EDN_DataOpe AND DeK.DeK_AccId = Acc.Acc_AccId 
WHERE EDN.EDN_NumerPelny = @EDN_NumerPelny
Księgowy napisał(a):

No tak, ale w zasadzie czym różni się Twoje rozwiązanie od mojego? Pod jakim względem jest ono lepsze od mojego? Czy JOIN-y są wydajniejsze czy chodzi o coś innego? Jak ocenisz rozwiązanie, które przedstawił @abrakadaber?

Subselect w waruknu po jednej stronie równości to potencjalny crash.

@abra

0

cd. (nie założę konta)

Zapytanie @abrakadaber używa "ukrytych" joinów, co dla mnie zmniejsza czytelność kodu. Jego kod wizualnie wydaje się lżejszy(a to tylko kwestia formatowania), ale trzeba się doszukiwać intencji i kolejności łączenia tabel. Poza tym ma beznadziejne aliasy.

[Kategorie] a

0

Okey, wszystko działa jak należy! W kodzie był mały błąd, ale udało się go szybko wyeliminować ;-)
Jeszcze raz wszystkim ogromnie dziękuję za udział w dyskusji i okazaną pomoc!!!
pozdrawiam

0

Ehh, myślałem, że już sobie poradzę, ale jednak znowu pojawiły się problemy ;-(

Chciałem więc ponownie prosić o pomoc.

Czy jest możliwość, bez korzystania ze zmiennych lub funkcji, by zapytanie zwróciło różnicę lub sumę dwóch innych zapytań? Chodzi o to, że mam dwa zapytania typu:

	SELECT 
		SUM(dekret.DeK_Kwota)  
	FROM
		[CDN_Testowa].[CDN].[DekretyKonta] dekret,
		[CDN_Testowa].[CDN].[EwidDodNag] ewidencja,
		[CDN_Testowa].[CDN].[Konta] konta,
		[CDN_Testowa].[CDN].[Kategorie] kategoria,
		[CDN_Testowa].[CDN].[OkresyObrach] okres  
	WHERE	
			dekret.DeK_Strona = 1   
		AND dekret.DeK_DataDok <= ewidencja.EDN_DataOpe
		AND dekret.DeK_AccId = konta.Acc_AccId
		AND ewidencja.EDN_NumerPelny = 'EDK/1/2015'
		AND ewidencja.EDN_KatID = kategoria.Kat_KatID
		AND okres.OOb_DataOtw <= ewidencja.EDN_DataOpe
		AND okres.OOb_DataKoncowa >= ewidencja.EDN_DataOpe
		AND konta.Acc_OObId = okres.OOb_OObID
		AND kategoria.Kat_KontoSegmentWN = konta.Acc_Numer

oraz

	SELECT 
		BO.BOE_KwotaWn 
	FROM 
		[CDN_Testowa].[CDN].[BOElem] BO,
		[CDN_Testowa].[CDN].[EwidDodNag] ewidencja,
		[CDN_Testowa].[CDN].[Konta] konta,
		[CDN_Testowa].[CDN].[Kategorie] kategoria,
		[CDN_Testowa].[CDN].[OkresyObrach] okres
	WHERE 
			ewidencja.EDN_NumerPelny = 'EDK/1/2015'
		AND ewidencja.EDN_KatID = kategoria.Kat_KatID
		AND kategoria.Kat_KontoSegmentWN = konta.Acc_Numer
		AND konta.Acc_AccId = BO.BOE_AccId
		AND okres.OOb_DataOtw <= ewidencja.EDN_DataOpe
		AND okres.OOb_DataKoncowa >= ewidencja.EDN_DataOpe
		AND konta.Acc_OObId = okres.OOb_OObID

A chciałbym, żeby zapytanie zwróciło sumę tych dwóch zapytań (docelowo będą 4 zapytania).
Czy mogę prosić jeszcze chociaż o naprowadzenie?

Z góry dziękuję!

1 użytkowników online, w tym zalogowanych: 0, gości: 1