Witam.
Próbuję skrócić jakoś dostęp do bazy danych dla jednego zapytania, które strasznie piłuje serwer. Jego wykonanie to około 3 - 4 minuty. Ilość rekordów w tabeli to ponad 100 000, serwer stary i pewnie dlatego tak długo trwa. Myślałem, aby użyć widoku, ale to nie da do końca takiego zamierzonego efektu.

Myślałem, aby napisać procedurę, która będzie się uruchamiać o wyznaczonej godzinie i aktualizować utworzoną przeze mnie tabelę z danymi. Dane w tabeli będą miały postać już zsumowanych wartości w zależności od indeksu. Potem, jak będę chciał pobrać te dane, to po prostu wpiszę:

Select * From Moja_Tabela
Where Year = @myYear

Myślę, że tabele będzie przedstawiona jakoś tak:

Create Table Tabela (
IdIndex Char (30),
Opis Char(30),
Typ char(30),
Ilość integer,
Wartosc Money,
Miesiac integer,
Rok integer
)

Nie wiem, jak wygląda sprawa z kluczami głównymi. Wiem, że dobrą praktyką jest sprowadzenie tabeli przynajmniej do 3 postaci normalnej, ale jedyne co mi przychodzi na myśl, to utworzenie numeru wiersza i jego autoinkrementacja. Choć, tak sobie myślę, nie powinno być problemu z powtarzającymi się danymi, ale tego tak nie sprawdzałem.

Zastanawiam się nad tym, w jaki sposób aktualizować tą tabelę. Chodzi o to, że codziennie będę wprowadzał do niej dane, ale w taki sposób, aby były pogrupowane w rok i miesiąc.

Czyli, jeżeli w tabeli mam już rekord:

IdIndex | [size=2]Opis | Typ | Ilość | Wartosc | Miesiac | Rok[/size]

Głośnik | Niskotonowy | Muzyka | 20 | 233.45 | 3 | 2014

I chcę tabelę zaktualizować danymi:

IdIndex | [size=2]Opis | Typ | Ilość | Wartosc | Miesiac | Rok[/size]

Głośnik | Niskotonowy | Muzyka | 15 | 170.09 | 3 | 2014

To mogę spokojnie użyć Update.

Create table TMP ...
Insert Into TMP ....

Update Tabela
Set Tabela.Ilosc= TMP.Ilosc, Tabela.Wartosc = Tmp.Wartosc
From Tabela
INNER JOIN TMP
On Tabela.IndexId = TMP.IndexID AND Tabela.Miesiac = TMP.Miesiac ....
WHERE
     Tabela.Ilosc!= TMP.Ilosc or 
     Tabela.Wartosc != TMP.Wartosc or
     (TMP.Ilosc is not null and Tabela.Ilosc is null) or
     (TMP.Wartosc is not null and Tabela.Wartosc is null)

Ten przykład wziąłem ze strony:  StackOverflow i jeszcze nie sprawdziłem jego działania.
Ale jeżeli się okaże, że np. jest to nowy miesiąc, lub wprowadzony nowy indeks, który jeszcze w tej tabeli nie wystąpił, to wtedy będę musiał użyć polecenia INSERT INTO, bo UPDATE nie zadziała, wnioskuję ze sposobu działania tej funkcji.
I jak tu stwierdzić, czy użyć INSERT czy UPDATE. Na chwilę obecną, jedynie co mi przychodzi na myśl to usunięcie danych z konkretnego miesiąca i wstawienie nowych z tabeli TMP. Wtedy zawsze będę używał INSERT, nie wiem, czy jest jakaś lepsza i mądrzejsza metoda.
W przypadku, gdy serwer padnie a miesiąc się cały usunie, wtedy trzeba będzie czekać aż się zadanie uruchomi i uaktualni "Tabela", albo samemu aktywować zadanie.

Wrzuciłbym najpierw wyniki pośrednie do tabeli tymczasowej, nazwijmy ją #Temp

Zakładając, że tabela docelowa nazywa się Dane, zrobiłbym coś takiego

UPDATE d
SET d.Wartosc = t.Wartosc
FROM Dane d
JOIN #Temp t ON t.Rok = d.Rok AND t.Miesiac = d.Miesiac

INSERT INTO Dane(..)
SELECT .., .., ..
FROM #Temp t
WHERE NOT EXISTS (SELECT 1 FROM Dane _d WHERE _d.Rok = t.Rok AND _d.Miesiac = t.Miesiac)

W ten sposób najpierw zaktualizujesz rekordy, które już są w tabeli, a następnie dodasz te, których nie ma.

Całość (łącznie z przygotowaniem tabeli tymczasowej) otaczasz jawną transakcją. W ten sposób jak cokolwiek się zwali po drodze nie zostaniesz z częściowo zaktualizowanymi danymi.

BEGIN TRAN T
   BEGIN TRY
       (..)Cały kod(...)

         COMMIT TRAN T
   END TRY
   BEGIN CATCH
      ROLLBACK TRAN T
     -- Może jakieś logowanie
   END CATCH


Jeśli chodzi o indeksy to oczywiście powinieneś mieć klucz główny i na nim klastrowy - jeśli kombinacja IdIndex, Rok, Miesiac jest unikalna załóż klucz kompozytowy i indeks właśnie na tych trzech kolumnach.
Gdybyś wprowadzał klucz sztuczny, typu IDENTITY(), to koniecznie załóż indeks nie-klastrowy na kolumnach Rok i Miesiac, aby przyspieszyć wyszukiwanie.
Trzecia postać normalna wydaje się już jest, zakładając, że pozostałe kolumny zależą tylko od klucz głównego [IdIndex, Rok, Miesiac]

Znaczy się, od momentu jawnej transakcji nic nie rozumiem ;)
Czytam teraz o indeksach.
Ograniczenie to SQL 2000 - więc nie wiem, czy to wszystko tam istnieje.

Transakcje to w skrócie mechanizm, który zapewnia spójność danych. Wszystko co jest wewnątrz transakcji ma się wykonać albo zostać wycofane.

Niejawne transakcje, to takie który system sam tworzy w momencie kiedy wykonujesz jakieś polecenie, np. SELECT, INSERT albo UPDATE. Jeśli np. UPDATEujesz 1000 rekordów i 550 pójdzie coś nie tak, to całość zostanie wycofana. Nie będzie tak, że 549 się zaktualizuje, a pozostałe 551 zostanie niezmienionych.

Transakcja jawna to taka, w której ty jako programista, decydujesz gdzie ma się zacząć i gdzie skończyć. W praktyce
BEGIN TRANSACTION NAZWA_TRANSAKCJI
Polecenie 1
Polecenie 2
...
Polecenie n
COMMIT TRANSACTION NAZWA_TRANSAKCJI

Dopiero kiedy wykona się COMMIT TRANSACTION NAZWA_TRANSAKCJI całośc zmian z poleceń zostanie "zapisana" i będzie widoczna dla innych spoza transakcji (w uproszczeniu, decyduje to jeszcze coś co nazywa się poziom izolacji, ale to szerszy temat-  http://msdn.microsoft.com/en-us/library/ms173763.aspx - ogólnie im wyższy poziom izolacji, tym lepsza spójność danych, lecz gorsza współbieżność).

Jeśli w którymś momencie nastąpi błąd całość nie wycofa się automatycznie, tylko będzie "wisiała". Do wycofania zmian służy polecenie ROLLBACK TRANSACTION NAZWA_TRANSAKCJI. Należy zawsze zatwierdzać (COMMIT) albo cofać (ROLLBACK) transakcje, inaczej będą wisiały (tzw. logi, które przechowują zmiany z transakcji) i w zależności od poziomu izolacji blokowały dostęp do zmodyfikowanych danych z poziomu innych transakcji.

Jeśli chodzi o indeksy to z grubsza działają jak w książce. Zamiast szukając słowa, czytać wszystko od początku do końca - patrzymy na posortowany spis treści, który zawiera odwołanie do konkretnej strony.
W MS SQL'u są dwa podstawowe typy indeksów:
- klastrowy: który odpowiada za fizyczny porządek rekordów w bazie danych, zakładany na ogół na kluczu głównym
- nieklastrowy: dodatkowo, na dowolnej kolumnie lub kolumnach

Indeksy moga być założone na pojedynczych lub wielu kolumnach. Dodatkowo moga też nie w samych indeksie trzymać dodatkowe kolumny, tak aby nie trzeba było pobierać całej strony. Dodatkowe kolumny nie biorą jednak udziału w sortowaniu.

Np.
SELECT Col1
FROM Tabela
WHERE Col2 = abc AND Col3 = efd

Optymalne dla tego zapytania będzie założenie indeksu na kolumnach Col2 i Col3 oraz dołożenie do niego kolumny Col1.

Indeksy znacząco (nieraz o parę rzędów wielkości) poprawiają wydajność zapytań (SELECT), jednak w uproszczeniu spowalniają operacje modyfikacji danych (INSERT, UPDATE, DELETE) ponieważ muszą być wtedy przebudowane. Zajmują też oczywiście dodatkowe miejsce w pamięci i na dysku. Z tego względu nie można po prostu zakładać indekstu na wszystko, tylko w zależności 
od konkretnych danych, sposobu ich wykorzystania i proporcji odczytów do zapisów.

W skrócie to tyle. Temat jest szeroki i zastosowałem pewne uproszczenia, ale starałem się opisać podstawowe koncepcje. Zastosowanie to ma ogólne, do wszystkich klasycznych, transakcyjnych baz danych. Mam nadzieję, że trochę objaśniłem. 

Można zrobić obie rzeczy w jednym zapytaniu. "INSERT INTO (....) ON DUPLICATE KEY UPDATE ..." czyli wstawiamy nowy rekord, a jeśli już taki istnieje to aktualizujemy wybrane pole.

@ NetCop

Niestety MS SQL 2000 (sprzed 14 lat) nie wspiera tego polecenia. Jest ono specyficzne dla MySQL. W nowszych wersjach jest alternatywa w postaci polecenia MERGE, ale nie w 2000.

Trochę poczytałem, poszukałem po necie i próbowałem napisać zapytanie, budujące tabelę.

Tak ono wygląda:

USE [TEST]
GO

DROP TABLE [dbo].[InventoryRaport]
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[InventoryRaport](
[Pozycja] [varchar](30) NOT NULL,
[Opis] [varchar](50) NULL,
[Lokalizacja] [varchar](30) NULL,
[Klasa] [varchar](10) NULL,
[Ilosc] [float] NOT NULL,
[Wartosc] [money] NOT NULL,
[Jednostka] [varchar](10) NULL,
[Miesiac] [tinyint] NOT NULL,
[Rok] [int] NOT NULL,

CONSTRAINT [PKey] PRIMARY KEY NONCLUSTERED
(
[Pozycja] ASC,
[Rok] ASC,
[Miesiac] ASC
)

) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX PIndex ON [dbo].[InventoryRaport] ([Pozycja], [Rok], [Miesiac])

GO
SET ANSI_PADDING OFF

Trochę nie rozumiem tych włączanych i wyłączanych opcji poza CREATE TABLE i czemu jest ON [PRIMARY].
Ale z kluczami o to chyba chodziło ?

SET ANSI_NULLS OFF - kontroluje zachowanie porównywanie wartości NULL
Przy standardzie, czyli ON wszelkie porównania typu X = NULL lub X <> NULL nigdy nie zwrócą rekordów. Należy używać poleceń IS NULL lub IS NOT NULL

SET QUOTED_IDENTIFIER ON - definiuję, czy można używać nazw pól w apostrofach. Np. SELECT "Pole1", "Pole2" from Tabela

SET ANSI_PADDING OFF - przy ON wartości w kolumnach znakowych, będą uzupłenianie spacjami do zadanej długości

Warto jednak pamiętać, że wszystko to tyczy się tylko konkretnego połączenia (skryptu). Nie jest nigdzie zapamiętywane.

ON PRIMARY - zostanie utworzone w podstawowym pliku bazy danych. Generalnie możesz bazę przechowywać w wielu plikach i umieszczać je np. na wielu dyskach, tak aby przyspieszyć odczyt. W nowszych wersjach można nawet dzielić pojedynczą tabelę na pliki (np. według daty) - tzw. partycjonowanie.

Dodałem do klucza głównego lokalizację i klasę, jednakże dalej mam kłopot z wypełnieniem tabeli, ponieważ zwraca błąd, że dane nie są unikalne.

Myślałem, że jak połączę kilka kolumn, wtedy będzie sprawdzane unikalność tych wszystkich kolumn, a nie każdej po kolei.

Hmm. Utworzyłem u siebie tabelkę twoim skryptem i działa zgodnie z oczekiwaniem - kombinacja (Pozycja, Rok, Miesiac) musi być unikalna.

Ok, mała pomyłka.

Kombinacja Miesiąc, Rok i Pozycja nie zawsze może być unikalna, bo może się zdarzyć, że Lokalizacja będzie różna.

Ale przy aktualizacji klucza głównego, zapomniałem uaktualnić Index i dlatego padało. Źle skojarzyłem nazwy.

Teraz wszystko działa.

Zastanawiam się tylko, czy aby nie dodać następnej linijki, która będzie usuwać wartości zerowe. Ta tabela i tak będzie duża 8800 rekordów tylko z jednego roku (niezerowych), a jak by dodać jeszcze kilka lat wstecz i kilka lat do przodu, to się rozrośnie. Czy może nie ma to większego znaczenia i poza wielkością bazy, wydajność będzie na podobnym poziomie ?

Parędziesiąt, czy nawet paręset tysięcy rekordów, nie powinno być żadnym problemem, chociaż oczywiście wszystko zależy od silnika i sprzętu.
Nie kombinowałbym nic dopóki nie okaże się, że faktycznie jest to problem.