dotNETportal.cz

Mám takový problém, potřebuji v T-SQL proceduře načíst náhodně buď ze souboru nebo jiné tabulky náhodně hodnoty jednoho atributu. Těch hodnot je asi 60 000. A načíst jich potřebuji náhodně 100 000. Když to udělám takhle:

CREATE PROCEDURE napln_tabulku 
AS
DECLARE @Random INT;
DECLARE @Upper INT = 100;
DECLARE @Lower INT = 1;
DECLARE @Id INT = 1;
DECLARE @Prijmeni NVARCHAR(255);
WHILE (@Id <= 100000)
BEGIN
SET NOCOUNT ON;
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Prijmeni = Prijmeni FROM muzska_prijmeni WHERE Poradi = @Random;
INSERT INTO naplneno (prijmeni) VALUES (@Prijmeni)
SET @Id = @Id + 1; 	
END
GO

Bude to trvat mnoho minut. Potřebuji něco mnohem rychlejšího. Uvažoval jsem o načtení do nějakého pole, ale v T-SQL pole neexistuje. Prosím o radu ...

Tomu vůbec nerozumím. Z tabulky, která má 60000 řádků jich potřebujete náhodně vybrat 100000? Tzn. že zhruba každý řádek tam bude "jedenapůlkrát"?

Ano, úplně náhodně, klidně jich mohlo být i míň nebo víc, mohou se opakovat, to je jedno. Jde mi jen o co nejrychlejší načtení náhodně vybraných dat do db tabulky.

Zauímalo by ma, aký to ma v praxi zmysel?

Optimalizace výkonu. Prostě ten kód, co jsem hodil nahoře zapsat tak, aby vložení do db bylo mnohem rychlejší s použitím prostředků T-SQL. Pomocí nějakého hromadného vložení dat. Je to vůbec takhle možné ? Nebo už to musím řešit v programovacím jazyce ?

edit: Dobrá, snížil jsem počet na 100 položek, ze kterých se bude vybírat, pro ten příklad to stačí.

Ale aký ma zmysel vkládať dáta náhodne nie aký ma zmysel otázka.

Kdyz je budete necitat po jednom zaznamu tak to bude pomaly. Poslat 100 000 SELECT dotazu bude proste trvat nez nacist 100 000 zaznamu jednim dotazem.

SELECT TOP 60000 prijmeni FROM tabulka ORDER BY NewID()

Nevim jeslti je presne co chcete ale je to podstatne rychlejsi.

Kdyz bude potreba vic zaznamu nez je v tabulce muzete to spojit dalsim dotazem s UNION.

Jako SELECT je to vyborne, ale jak to pouzit na zapis do tabulky?

Zkoušel jsem to přes kurzor, ale tim se to dost zpomali ...

CREATE PROCEDURE [dbo].[napln]
AS
DECLARE @prijmeni nvarchar(255);
DECLARE kurzor CURSOR FOR SELECT TOP 10000 prijmeni FROM muzska_prijmeni ORDER BY NEWID()
OPEN kurzor
FETCH NEXT FROM kurzor INTO @prijmeni
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON;
INSERT INTO naplneno (prijmeni) VALUES (@prijmeni)
FETCH NEXT FROM kurzor INTO @prijmeni    
END
CLOSE kurzor
DEALLOCATE kurzor
GO

na zapis do tabulky takto (neupravoval jsem to takže přiřadí 120000 hodnot)

CREATE PROCEDURE [dbo].[napln]
AS
INSTERT INTO naplneno SELECT TOP 60000 prijmeni FROM muzska_prijmeni ORDER BY NEWID()
INSTERT INTO naplneno SELECT TOP 60000 prijmeni FROM muzska_prijmeni ORDER BY NEWID()
GO

Děkuji a není ještě nějaký způsob jak pořešit tu situaci, kdy vybírám mnohonásobně více náhodných záznamů z menšího počtu ?

Myslím tím, že chci 100000 náhodně vložených záznamů pouze ze 100 záznamů (budou se opakovat), samozřejmě co nejrychleji vložit.

pak už mne napadá jen použití rekurze:

CREATE PROCEDURE napln
(
@Num INT
)
AS
DECLARE @sql VARCHAR(255)
IF @Num = 100
BEGIN
INSERT INTO naplneno SELECT TOP 100 Prijmeni FROM muzska_prijmeni ORDER BY NEWID()
END
ELSE
BEGIN
SET @sql = 'INSERT INTO naplneno SELECT TOP ' + CONVERT(VARCHAR(6), @Num/2) + ' prijmeni FROM naplneno ORDER BY NEWID()'
EXEC (@sql)
END
IF @Num < 102400 
BEGIN
SET @sql='napln ' + CONVERT(VARCHAR(6),@Num*2)
EXEC (@sql)
END
RETURN

pak to spustite s parametrem

exec napln 100

zkoušel jsem to na mssql7, první spuštění bylo pod 25 vteřin, každé další už bylo pod 3 vteřiny. hlavně si nedávejte index na 'prijmeni' jenak vám to pak podle něj seřadí.

Děkuji a ještě bych prosil pomoci s dotazem insert, kde hromadně vkládám do jedné tabulky ze dvou tabulek úplně náhodně. Snažil jsem se to řešit nějak takto:

INSERT INTO naplneno (prijmeni,jmeno) SELECT prijmeni FROM (SELECT TOP 100000 prijmeni FROM muzska_prijmeni ORDER BY NEWID()) AS prijmeni UNION ALL SELECT jmeno FROM (SELECT TOP 100000 jmeno FROM muzska_jmena ORDER BY NEWID()) AS jmeno

Ale mám to špatně. Myslím, že ale z toho pochopíte o co mi jde ...

zkuste toto

CREATE PROCEDURE napln_tabulku 
AS
DECLARE @id INT
CREATE TABLE TMP(pk INT IDENTITY(1,1) NOT NULL,id INT, prijmeni VARCHAR(255) CONSTRAINT pk PRIMARY KEY (pk))
SET @id=1
WHILE (@id<=100000)
BEGIN
SET NOCOUNT ON;
INSERT INTO tmp(id) VALUES (round(rand()*60001,0))
SET @Id = @Id + 1; 	
END
UPDATE tmp SET prijmeni = (SELECT prijmeni FROM muzska_prijmeni WHERE poradi=id)
ALTER TABLE tmp DROP COLUMN id
GO

Dík, ale trvá to skoro minutu, to je moc dlouho:(

CREATE PROCEDURE [SelectRandomData] (
  @targetCount INT
)
AS BEGIN

  DECLARE @t TABLE (id INT)

  -- vygenerovat posloupnost 0,1,2,3... tak dlouhou, aby počet záznamů v tabulce * počet čísel byl víc, než chceme záznamů na výsledku
  DECLARE @i INT, @count INT
  SET @i = 0
  SELECT @count = COUNT(*) FROM tabulka
  WHILE (@i <= @targetCount / @count) BEGIN
    INSERT INTO @t (id) VALUES (@i)
    SELECT @i = @i + 1
  END WHILE

  -- vrátit výsledky
  SELECT TOP (@targetCount) t0.*
    FROM tabulka t0
    JOIN @t t1 ON 1 = 1
    ORDER BY NEWID()
END

Tohle by mělo být rychlé, protože to nepoužívá kurzory (ty extrémně zpomalují).

Protože záznamy z tabulky tam mají být víckrát, musíme je "namnožit" pomocí JOINu s jinou tabulkou (JOIN dělá dvojice každý s každým tak, aby to vyhovovalo jeho podmínce, vzhledem k tomu, že za ON je 1 = 1, tak to prostě vezme každý záznam z tabulky tabulka a každý záznam z @t a vygeneruje to všechny dvojice).

Na začátku si jen zjistíme, kolik potřebujeme záznamů a kolik jich máme, a podle toho si vygenerujeme tabulku @t (je úplně jedno, co v ní bude, jde o počet řádků) tak, aby nám JOIN se skutečnou tabulkou dal dostatek záznamů (víc než jich potřebujeme na výstup).

Pak už jen z toho JOINu vybereme prvních @targetCount záznamů a seřadíme je podle NEWID (nový guid, což je pseudonáhodná hodnota), takže je dostaneme v náhodném pořadí.

Nebude to úplně přesná matematická náhoda (to by tabulka @t musela mít vždy @targetCount prvků, což asi nebude příliš efektivní). Kdybych v taulbce tabulka měl řekněme čísla od 1 do 10 a chtěl vybrat 20 prvků, tak každé číslo bude v JOINu jen dvakrát (resp. třikrát, je tam <=). Kdybych chtěl pravou náhodu, musím připustit i situaci, kdy si vyberu 20x jedničku, což tady nejde. Leda že by @t obsahovala 20 čísel, ale to by u velkých množin bylo náročné na paměť.

P.S. Píšu z hlavy, asi v tom SQL budou chyby.

Díky, projdu si to, já to zatím udělal takhle:

Vytvořil jsem 4 variable tabulky, do prvních dvou jsem pomocí UNION ALL rozmnožil 2 tabulky, kde mám uložené "slovníky" s potřebnými daty.

Ty jsem potom vložil do těch dalších dvou variable tabulek už náhodně pomocí NEWID() a poté využil identity-očíslování k tomu, abych je pomocí joinu slepil do konečné tabulky :)

I při 100000 záznamech jde jen o pár sekund ...

Ještě mám takovou, možná blbou otázku .... Vytvořím si tabulku a chci v ní vytvořit určitý počet řádků, klidně s NULL daty. Jde to nějak udělat ?

Důvod, proč ji takto potřebuji, je, že ji chci naplnit (UPDATE) náhodně vygenerovanými čísly a datumy pomocí funkce NEWID(). Pokud je to nesmysl, tak bych poprosil o způsob jak toto udělat pomocí INSERT, vždy mi to totiž vloží jen jeden řádek.