október 2012 havi bejegyzések

Teszt adatok létrehozása

Csináljuk magunknak teszt adatokat! (Később még fogok hivatkozni erre a betöltésere.)

Az ideális betöltés valahogy így néz ki:

  1. Tábla létrehozása, ha még nincs.
  2. Adatok betöltése (script)
  3. Elsődleges kulcsok és clustered indexek létre hozása
  4. Egyéb kulcsok és indexek létre hozása
-- A tempdb-ben, mert annak optimálisabb a naplózása
USE [tempdb]
GO

-- Nem akarunk darabszám kírásokat
SET NOCOUNT ON;

-- Ha már léteznének az objektumok (most táblák), akkor eldobjuk őket
IF OBJECT_ID('transactions', 'U') IS NOT NULL DROP TABLE transactions
IF OBJECT_ID('accounts', 'U') IS NOT NULL DROP TABLE accounts
GO

-- Létrehozzuk a táblákat, kulcsok nélkül, mert úgy gyorsabb lesz a betöltése
CREATE TABLE accounts (id int identity(1,1) not null, name nvarchar(36) not null)
CREATE TABLE transactions (id int identity(1,1) not null, accounts_id int not null, amount int not null)

DECLARE
    @from int = 0
   ,@tmp int
   ,@rnd tinyint

WHILE (@from < 100000) BEGIN

    IF @@TRANCOUNT = 0 BEGIN TRAN
    INSERT INTO accounts (name) VALUES (newid())
    SELECT @tmp = @@identity
    SELECT @rnd = CAST(RAND() * 10 AS TINYINT)

    IF @rnd > 3 BEGIN
      INSERT INTO transactions (accounts_id, amount)
        SELECT @tmp, abs(left(checksum(newid()),3))
        FROM sys.databases
        WHERE database_id < @rnd
    END

    IF @from % 1000 = 0 AND @@TRANCOUNT <> 0 COMMIT
    SET @from += 1

END

IF @@TRANCOUNT <> 0 COMMIT
GO

-- Létrehozzuk a kulcsokat, indexeket
ALTER TABLE accounts ADD CONSTRAINT PK_accounts_id PRIMARY KEY CLUSTERED (id)
GO

ALTER TABLE transactions ADD CONSTRAINT PK_transactions_id PRIMARY KEY CLUSTERED (id)
GO

ALTER TABLE transactions ADD CONSTRAINT FK_transactions_accounts
FOREIGN KEY (accounts_id) REFERENCES accounts (id)
    ON UPDATE  NO ACTION
    ON DELETE  NO ACTION
GO

-- Az indexet csak a clustered index létrehozása után hozzuk létre!
CREATE INDEX IDX_accounts_id ON transactions (accounts_id)
GO

-- Check
SELECT t.name, si.rows
FROM sys.tables t
INNER JOIN sys.sysindexes si
    ON t.object_id = si.id
WHERE t.is_ms_shipped = 0
    AND t.name IN ('accounts', 'transactions')
    AND si.indid < 2

A CROSS JOIN gyakorlati haszna

A tapasztalataim azt mutatják, hogy ezt a JOIN típust nagyon kevesen ismerik, még kevesebben használják. A CROSS JOIN-t legtöbbször teszt adatok létrehozására szokták használni, nagyon sok helyen ezt olvasom, én is arra használtam. Aztán a gyakorlat hozta, hogy hol is tudnám hasznát venni.

Egy ilyen jellegű lekérdezéssel kerestek meg:

SELECT
    mezők,
    (SELECT COUNT(*) FROM másik tábla) darab
FROM tábla

Itt a SELECT listában lévő “alselect” minden egyes sorra lefut. Persze okosabb adatbázis kezelők cache-lik az eredményt, de mégis tanácsosabb az alábbi formát használni a fenti lekérdezés helyett:

SELECT
    mezők,
    tmp.darab
FROM tábla
CROSS JOIN (SELECT COUNT(*) darab FROM másik tábla) tmp

Így garantáltan csak egyszer fut le az “alselect” művelet. Kis dolog, de sok kicsi sokra megy és az SQL server sem fogja mindig felismerni, hogy mit is akarunk.
Ha a lekérdezés nagyon sok sorral tér vissza, akkor pedig két külön lekérdezésbe kell tenni.