jak wyrzucic dane z 7 tabel Opcje

[mika1]

Witajcie,

mam baze o strukturze:

[SQL] pobierz, plaintext 
 CREATE TABLE `umowy` (
  `NrUmowy` varchar(10) NOT NULL,
  `OpisUmowy` varchar(255) NOT NULL,
  `Grupa` varchar(255) NOT NULL,
  `Typ` varchar(255) NOT NULL,
  `Dzial` varchar(255) NOT NULL,
  `Jednostka` varchar(255) DEFAULT NULL,
  `Nazwisko` varchar(100) NOT NULL,
  `Imie` varchar(50) NOT NULL,
  `Poczatek` varchar(12) NOT NULL,
  `Koniec` varchar(12) NOT NULL,
  `BiezacyRok` varchar(5) NOT NULL,
  PRIMARY KEY (`NrUmowy`));
 
 
CREATE TABLE `zlecenie` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Wykonawca` varchar(255) NOT NULL,
  `DataFaktury` varchar(12) NOT NULL,
  `NrFaktury` varchar(20) NOT NULL,
  `Kwota` double NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `dzielo` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Nazwisko` varchar(100) NOT NULL,
  `Imie` varchar(50) NOT NULL,
  `Kwota` double NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `materialy` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Wykonawca` varchar(255) NOT NULL,
  `DataFaktury` varchar(12) NOT NULL,
  `NrFaktury` varchar(20) NOT NULL,
  `Kwota` double NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `ogolne` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Data` varchar(12) NOT NULL,
  `Kwota` double NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `prow` (
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(6) NOT NULL,
  `Lacznie` double NOT NULL,
  `wyplaty` double NOT NULL,
  `zlecenie` double NOT NULL,
  `Materialy` double NOT NULL,
  `Uslugi` double NOT NULL,
  `dzielo` double NOT NULL,
  `koszty` double NOT NULL,
  `Ogolne` double NOT NULL,  `VAT` double NOT NULL
);
 
CREATE TABLE `blokady` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Data` varchar(12) NOT NULL,
  `Kwota` double NOT NULL,
  `Cel` varchar(100) NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `uslugi` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Wykonawca` varchar(255) NOT NULL,
  `DataFaktury` varchar(12) NOT NULL,
  `NrFaktury` varchar(20) NOT NULL,
  `Kwota` double NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `wyplaty` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Miesiac` varchar(15) NOT NULL,
  `Kwota` double NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `koszty` (
  `Lp` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `NrUmowy` varchar(10) NOT NULL,
  `Rok` varchar(5) NOT NULL,
  `Nazwisko` varchar(100) NOT NULL,
  `Imie` varchar(50) NOT NULL,
  `Kwota` double NOT NULL,
  PRIMARY KEY (`Lp`));
 
CREATE TABLE `grupy_umow` (
  `NazwaGrupy` varchar(255) NOT NULL,
  PRIMARY KEY (`NazwaGrupy`));
 
 
CREATE TABLE `jednostki` (
  `NazwaJednostki` varchar(255) NOT NULL,
  `NazwaDzialu` varchar(255) NOT NULL,
  PRIMARY KEY (`NazwaJednostki`));
 
 
CREATE TABLE `typy_umow` (
  `NazwaTypu` varchar(255) NOT NULL,
  `NazwaGrupy` varchar(255) NOT NULL,
  PRIMARY KEY (`NazwaTypu`));
 
 
CREATE TABLE `Dzialy` (
  `NazwaDzialu` varchar(255) NOT NULL,
  PRIMARY KEY (`NazwaDzialu`));
[SQL] pobierz, plaintext 

i potrzebuje wydobyc dane z 7 tabel. Robie to tak:

[SQL] pobierz, plaintext 
SELECT a.NrUmowy, a.Grupa, a.Dzial, a.Nazwisko, b.sum(Kwota), b.Rok, c.sum(Kwota), c.Rok, d.sum(Kwota), d.Rok,  e.sum(Kwota), e.Rok, f.sum(Kwota), f.Rok, g.sum(Kwota), g.Rok,  m.sum(Kwota), m.Rok
FROM umowy a
LEFT JOIN zlecenie b ON b.NrUmowy = a.NrUmowy
LEFT JOIN dzielo c ON c.NrUmowy = a.NrUmowy
LEFT JOIN materialy d ON d.NrUmowy = a.NrUmowy
LEFT JOIN uslugi e ON e.NrUmowy = a.NrUmowy
LEFT JOIN wyplaty f ON f.NrUmowy = a.NrUmowy
LEFT JOIN koszty g ON g.NrUmowy = a.NrUmowy
LEFT JOIN ogolne m ON m.NrUmowy = a.NrUmowy
GROUP BY b.NrUmowy, b.Rok, c.NrUmowy, c.Rok, d.NrUmowy, d.Rok,  e.NrUmowy, e.Rok, f.NrUmowy, f.Rok, g.NrUmowy, g.Rok,  m.NrUmowy, m.Rok
[SQL] pobierz, plaintext 

Chciałabym dodać jeszcze 2 tabele. Ale na chwilę obecną wydajnościowo działa to tak że po 4 godzinach brak efektu. Co mogę zrobić żeby uzyskać na początek te dane?

Dzięki
B

[mar1aczi]

Zacznij od jednej tabeli i dokładaj po jednej sprawdzając wyniki oraz explain - czy czasem nie leci po nieindeksowanych danych.

[kartin]

Należy zastosować indeksy na kluczach obcych.

Abstrahując od wydajności to należy stosować właściwe typy danych

[SQL] pobierz, plaintext 
`Rok` varchar(5) NOT NULL,
`Data` varchar(12) NOT NULL,
`Kwota` double NOT NULL,
[SQL] pobierz, plaintext 

O ile przez rok i datą nic złego się nie stanie, to z kwotą w double możesz się przejechać.

[SQL] pobierz, plaintext 
SELECT a.NrUmowy, a.Grupa, a.Dzial, a.Nazwisko, b.sum(Kwota), b.Rok, c.sum(Kwota), c.Rok, d.sum(Kwota), d.Rok,  e.sum(Kwota), e.Rok, f.sum(Kwota), f.Rok, g.sum(Kwota), g.Rok,  m.sum(Kwota), m.Rok
...
GROUP BY b.NrUmowy, b.Rok, c.NrUmowy, c.Rok, d.NrUmowy, d.Rok,  e.NrUmowy, e.Rok, f.NrUmowy, f.Rok, g.NrUmowy, g.Rok,  m.NrUmowy, m.Rok
[SQL] pobierz, plaintext 

Powinno się grupować po wszystkich kolumnach w których nie są użyte funkcje agregujące. To, że MySQL nie wymusza tego to już inna bajka.

[SQL] pobierz, plaintext 
CREATE TABLE `grupy_umow` (
  `NazwaGrupy` varchar(255) NOT NULL,
  PRIMARY KEY (`NazwaGrupy`));
 
CREATE TABLE `Dzialy` (
  `NazwaDzialu` varchar(255) NOT NULL,
  PRIMARY KEY (`NazwaDzialu`));
[SQL] pobierz, plaintext 

Czemu to ma służyć?

Ten post edytował kartin 15.09.2015, 10:55:14

[mmmmmmm]

Zapytanie ogólnie jest do dupy.
1. b.sum(Kwota) nie ma prawa zadziałać.
2. załóżmy, że masz umowę o id=1, do tego masz dwa zlecenia (umowa_id=1), więc robią ci się dwa rekordy z umowa.id=1. Każda dołączona tabela ma już zwielokrotnione rekordy.
3. najbardziej spowalnia LEFT JOIN
4. Zapytanie powinno wyglądać np. mniej więcej tak:

[SQL] pobierz, plaintext 
SELECT a.NrUmowy, a.Grupa, a.Dzial, (SELECT Sum(Kwota) FROM zlecenie b WHERE b.NrUmowy=a.NrUmowy), ... FROM umowy a
[SQL] pobierz, plaintext 

(oczywiście można to tez inaczej rozwiązać, ale ten sposób jest najbardziej logiczny)

[mika1]

Nie mogę zmienić struktury bazy ani typów danych. Dostałam taki twór z którego muszę wyłowić dane. Ktoś kilka lat temu to napisał i tak już jest.

[Pyton_000]

Pozakładaj Indeksy na kolumnach po których łączysz tabele, bo wg. struktury ich nie masz.