Tworzymy swoją testową bazę danych (1000000 rekordów) [SQL Server].

Na "małych" bazach danych wszystkie zapytania trwają "chwilkę" 🙂 Co zrobić jeżeli chcielibyśmy sprawdzić nasze zapytanie na "większej" bazie danych np. 1000000 rekordów. Możemy ją sobie sami stworzyć. W tym wpisie, krok po kroku, opiszę jak w łatwy sposób, za pomocą pętli WHILE, stworzyć przykładową bazę danych, o tak dużej liczbie rekordów. Ja stworzę bazę z przykładową jedną tabelą ale Wy możecie stworzyć sobie bazę jaką chcecie z większą ilością tabel i innymi kolumnami itd.

Moja baza (o nazwie "tmp") będzie się składała z jednej tabeli (o nazwie "MyTab") z zamówieniami, która będzie miała następujące kolumny:

  • ID – identyfikator rekordu
  • OrderDate – data zamówienia
  • OrderNumber – numer zamówienia
  • PositionsNumber – liczba pozycji w danym zamówieniu

Przyjmiemy sobie jakieś dodatkowe założenia

  • Pole identyfikator będzie zawierało kolejne liczby całkowite
  • Pole OrderDate będzie zawierało datę zamówienia ale z przedziału od 1900-01-01 do 2017-12-31 i będziemy je nadawać od daty początkowej, zwiększając w każdym rekordzie o jeden dzień. Jak dojdziemy do daty końcowej to wracamy do daty początkowej.
  • OrderNumber – numer zamówienia będzie złączeniem kolejnego numeru rozpoczynającego się od 1 przełamany na rok np. 1/1900, czyli zamówienie nr 1 z 1900 r.
  • PositionsNumber – liczba pozycji w danym zamówieniu także będzie liczbą całkowita z przedziału od 1 do 20. Rozpoczynamy od 1 i dodajemy, w każdym rekordzie, 1 aż do 20 i wtedy powracamy do wartości 1 i tak w kółko.

Krok 1. Tworzymy strukturę bazy "tmp", czyli tak naprawdę tabeli "MyTab"

CREATE DATABASE tmp
GO
USE tmp
GO
CREATE TABLE MyTab(
     ID bigint
    ,OrderDate datetime
    ,OrderNumber varchar(20)
    ,PositionsNumber int
)

Jeżeli ze stworzeniem takiej struktury masz jakieś problemy zajrzyj na stronkę z poleceniami i funkcjami SQL i poczytaj nt. wykorzystanych poleceń.

Krok 2. Deklarujemy zmienne początkowe.

Jeszcze przed zastosowaniem pętli WHILE musimy zadeklarować zmienne które zainicjalizujemy nadając im początkowe wartości.

DECLARE @id bigint = 1;
DECLARE @OrderDate Datetime = '1900-01-01 00:00:00';
DECLARE @OrderInt int = 1;
DECLARE @PositionsNumber bigint = 1;
DECLARE @OrderNumber varchar(10) = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

 Krok 3. Pętla WHILE i dodawanie nowych rekordów.

Dodajemy kawałek kodu dotyczącego pętli WHILE z instrukcją INSERT INTO, "wrzucającą" rekordy do tabeli "MyTab"

WHILE @id <= 1000000
    BEGIN
        INSERT INTO MyTab VALUES (@id , @OrderDate, @OrderNumber , @PositionsNumber)

   END

Krok 4. Inkrementacja wartości pola ID

Tutaj sprawa jest prosta, wartości pola ID mają rosnąć od 1 do 1000000. Na początku jeszcze przed pętlą zadeklarowaliśmy zmienną @id i nadaliśmy jej wartość 1

DECLARE @id bigint = 1;

Teraz musimy za każdym, "obrotem" pętli WHILE, zwiększać wartość tej zmiennej o 1. Odpowiedni kod musimy wrzucić wewnątrz pętli.

SET @id += 1

Krok 5. Inkrementacja wartości pola OrderDate.

Tutaj sprawa jest nieco trudniejsza. W naszych założeniach zapisaliśmy, że

Pole OrderDate będzie zawierało datę zamówienia ale z przedziału od 1900-01-01 do 2017-12-31 i będziemy je nadawać od daty początkowej, zwiększając w każdym rekordzie o jeden dzień. Jak dojdziemy do daty końcowej to wracamy do daty początkowej.

kod oczywiście wrzucamy wewnątrz pętli WHILE

        IF (@OrderDate < '2017-12-31')
            BEGIN
                SET @OrderDate = dateadd(day,1,@OrderDate)
            END
        ELSE
            BEGIN
                SET @OrderDate = '1900-01-01'
            END

Skorzystałem tutaj z instrukcji warunkowej IF.  W warunku sprawdzam czy wartość zmiennej @OrderDate jest mniejsza niż 2017-12-31 IF (@OrderDate < '2017-12-31'). Jeżeli tak to zwiększam wartość tej zmiennej o jedej dzień SET @OrderDate = dateadd(day,1,@OrderDate) za pomocą funkcji dateadd(). Jeżeli wartość tej zmiennej będzie już równa 2017-12-31 (warunek nie będzie spełniony) to wtedy ustawiam wartość tej zmiennej na datę 1900-01-01 (SET @OrderDate = '1900-01-01').

Krok 6. Inkrementacja wartości pola OrderNumber.

Przypominam założenia dotyczące pola OrderNumber

OrderNumber – numer zamówienia będzie złączeniem kolejnego numeru rozpoczynającego się od 1 przełamany na rok np. 1/1900, czyli zamówienie nr 1 z 1900 r.

Żeby stworzyć taką strukturę numeru zamówienia posłużyłem się zmienną pomocniczą  @OrderInt którą będę zwiększał od 1 do … no właśnie skąd będę wiedział, ze skończył się już rok ?
Żeby wiedzieć kiedy "kończy" się rok i żeby zmienić numer z 365 (bo tyle jest dni w roku, no chyba że będzie to rok przestępny) znowu na 1, zastosowałem poniższy kod

IF (YEAR(@OrderDate) = YEAR(dateadd(day,-1,@OrderDate)))
            BEGIN
                SET @OrderInt += 1
            END
        ELSE
            BEGIN
                SET @OrderInt = 1
            END

Znowu zastosowałem IF-a. W warunku sprawdzam czy rok z aktualnej daty jest równy rokowi z aktualnej daty + 1 dzień, innymi słowy czy kolejny dzień jest jeszcze z tego samego roku czy już jest kolejny rok. Jeżeli jest ten sam rok to tylko zwiększamy o 1 wartość zmiennej @OrderInt a jeśli warunek nie będzie spełniony to ustawiamy wartość zmiennej @OrderDate znowu na 1. Do "wyciągnięcia" roku z daty stosuję funkcję YEAR()
OK. Ale narazie załatwiliśmy sprawę numeru zamówienia a jeszcze musi on być przełamany przez rok. W tym celu korzystam z poniższego kodu

SET @OrderNumber = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

Zwródźcie uwagę, że zmienna @OrderNumber ma być typu VARCHAR a zmienna @OrderInt jest typu INT, dlatego muszę tu skorzystać z dwóch funkcji YEAR(), ta funkcja posłuży mi do "wyciągnięcia" roku z daty oraz CAST(), ta funkcja posłuży mi do zamiany obu części numeru (samego numeru i roku) na typ VARCHAR.

Krok 7. Inkrementacja ilości pozycji w każdym zamówieniu.

Do tego celu także wykorzystam instrukcję IF.

IF (@PositionsNumber < 20)
            BEGIN
                SET @PositionsNumber += 1
            END
        ELSE
            BEGIN
                SET @PositionsNumber = 1
            END

założyliśmy że

PositionsNumber – liczba pozycji w danym zamówieniu także będzie liczbą całkowita z przedziału od 1 do 20. Rozpoczynamy od 1 i dodajemy, w każdym rekordzie, 1 aż do 20 i wtedy powracamy do wartości 1 i tak w kółko.

W warunku sprawdzam więc czy zmienna @PositionsNumber jest mniejsza od 20. Jeżeli tak to zwiększamy tylko jej wartość o 1, a jeśli już dojdzie do wartości 20 to ustawiamy jej wartość z powrotem na 1.

Takim oto sposobem skończyliśmy nasz skrypt którym utworzymy sktukturę bazy "tmp" (w naszym przypadku to tylko utworzenie tabeli "MyTab") a później "wrzucimy" wartości do tabeli "MyTab". Cały kod naszego skryptu poniżej.

CREATE DATABASE tmp
GO
USE tmp
GO
CREATE TABLE MyTab(
     ID bigint
    ,OrderDate datetime
    ,OrderNumber varchar(20)
    ,PositionsNumber int
)

DECLARE @id bigint = 1;
DECLARE @OrderDate Datetime = '1900-01-01 00:00:00';
DECLARE @OrderInt int = 1;
DECLARE @PositionsNumber bigint = 1;
DECLARE @OrderNumber varchar(10) = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

WHILE @id <= 1000000
    BEGIN
        INSERT INTO MyTab VALUES (@id , @OrderDate, @OrderNumber , @PositionsNumber)

SET @id += 1

       IF (@OrderDate < '2017-12-31')
            BEGIN
                SET @OrderDate = dateadd(day,1,@OrderDate)
            END
        ELSE
            BEGIN
                SET @OrderDate = '1900-01-01'
            END

IF (YEAR(@OrderDate) = YEAR(dateadd(day,-1,@OrderDate)))
            BEGIN
                SET @OrderInt += 1
            END
        ELSE
            BEGIN
                SET @OrderInt = 1
            END

SET @OrderNumber = CAST(@OrderInt AS VARCHAR)+'/'+CAST(YEAR(@OrderDate) AS VARCHAR)

IF (@PositionsNumber < 20)
            BEGIN
                SET @PositionsNumber += 1
            END
        ELSE
            BEGIN
                SET @PositionsNumber = 1
            END

   END

Po uruchomieniu skryptu (F5) ja przy swoim sprzęcie spędziłem aż 13 minut żeby skrypt sie wykonał (na kompie ASUS X53TA, 6GB RAMu, SSD). Zrzut poniżej.

t-sql, sql, temporary database

Teraz uruchomimy proste zapytanie które wyświetli nam wszystkie rekordy z naszej tabeli "MyTab".

SELECT
   *
FROM
   MyTab

Wynik zapytania poniżej

t-sql, sql, temporary database

Widzimy, że wszystkie rekordy z tabeli wyświetlił w 16 s i jest ich 1 MLN tak jak chcieliśmy. Jeszcze tylko sprawdźmy, czy na przełomie lat 2017-12-31 i 1990-01-01 mamy prawidłową numerację, czyli znowu zaczynamy numerację zamówień od 1.

t-sql, sql, temporary database

OK. Jest w porządku 🙂 To oczywiście pewnie jedna z możliwości utworzenia takiej bazy. Na dzień tworzenia wpisu nie znam innej metody utworzenia takiej bazy, jak poznam to się z Wami podzielę 🙂

Teraz wykorzystując wiedzę przestawioną po niniejszym artykule możecie sobie stworzyć swoją testową bazę jakomkolwiek chcecie.


SQL tutorial. AdventureWorks exercises no.34. SQL SERVER.  SQL tutorial. AdventureWorks exercises no.34. SQL SERVER. SQL tutorial. AdventureWorks exercises SQL SERVER.