BETA
Aby się zalogować, najpiew wybierz portal.
Aby się zarejestrować, najpiew wybierz portal.
Podaj słowa kluczowe
Słowa kluczowe muszą mieć co najmniej 3 sąsiadujące znaki alfanumeryczne
Pole zawiera niedozwolone znaki
Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru

 
0


Witam wszystkich,

W wątku http://www.wss.pl/frmThread.aspx?tid=67672 pojawił się ciekawy problem. Jest potrzeba zaimplementowania generatora kolejnych liczb (np. numerów dowodów). Generator ma generować na insert kolejne liczby bez dziur. Ma być oczywiście nieczuły na rollback i nie generujący duplikatów przy współbieżnej pracy użytkowników. Musi obsługiwać insert wielu wierszy jednocześnie. I wreszcie ma być możliwie jak najbardziej wydajny.

Żeby było ciekawiej, dokładam wymaganie, że numery mają być generowane niezależnie w ramach zakresów. Np. dla dowodów są to lata finansowe, magazyny, firmy itp.

Zatem mamy przykładową tabelę:

CREATE TABLE dbo.Transakcje (
Zakres varchar(20) NOT NULL,
Numer int NOT NULL,
Wartosc money NOT NULL,
CONSTRAINT PK_Numery PRIMARY KEY (Zakres, Numer)
)

Do tabeli wstawiamy wstawiamy Zakres i Wartosc, a oczekujemy wygenerowania kolejnego numeru (kolejnych numerów).

Rozwiązanie musi być w T-SQL. Możemy używać wszystkich funkcji SQL Server do wersji 2008 włącznie. Jeśli istnieje taka potrzeba, możemy założyć inną tabelę lub rozszerzyć zadaną o dodatkowe pola lub więzy. Nie wolno usuwać pól ani zmieniać istniejącego PK. Ew. dodane pola nie mogą być wymagane od użytkownika (muszą być nullowe lub z defaultem).

Rozwiązania umieszczamy w niniejszym wątku do końca dnia 22.07.2009. Można rozszerzać i wprowadzać poprawki do rozwiązań innych osób. Po podanym terminie wybiorę 3 osoby, które zaproponują najciekawsze pomysły lub rozwiązania i nagrodzę voucherami 40% na egzamin Microsoftu oraz pogratuluję zarówno witrualnie jak i osobiście na wrześniowym spotkaniu PLSSUG Warszawa, o ile będą w stanie dotrzeć.

Życzę wszystkim miłej zabawy.


__________
Pozdrawiam
Marek Adamczuk
Blog
tagi: SQL   SQL Server

__________
Pozdrawiam
Marek Adamczuk






Krzysztof Stachyra Ekspert WSS
Krzysztof Stachyra
1577 pkt.
Guru
 
0


.... voucher na egzamin - 40 % * 80 USD
... gratulacje Marka -- bezcenne ;)))))))
za to kartą nie zapłacisz;)

sory za mały OT ale nie mogłem sobie odebrać porannej zabawy ;))))

Pozdrawiam,
Krzysiek Stachyra

Edytowano 1 raz. Ostatnio 2009-07-17 08:21:27 przez wrkwarka.

Pozdrawiam,

Krzysiek Stachyra

PLSSUG

Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Krzyś,

Bierz się do boju zamiast marudzić ;). Na razie tyle dostałem z przydziału redakcyjnego. Jak zobaczę, że się coś dzieję, to spróbuję trochę pożebrać w celu podbicia stawki :).

__________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Krzysztof Stachyra Ekspert WSS
Krzysztof Stachyra
1577 pkt.
Guru
 
0


Marku to absolutnie nie marudzenie tylko próbuje zmotywować grono developerskie do boju ;))) Pozdrawiam,
Krzysiek Stachyra

Pozdrawiam,

Krzysiek Stachyra

PLSSUG

vezdohan
vezdohan
779 pkt.
Senior
 
0


Chciałem być pierwszy
create database rob
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[counter]') AND type in (N'U'))
DROP TABLE [dbo].[counter]
GO

CREATE TABLE [dbo].[counter](
[nazwa] [varchar](15) NOT NULL,
[counter] [numeric](10, 0) NULL,
CONSTRAINT [pk_counter] PRIMARY KEY CLUSTERED
(
[nazwa] ASC
)
)

create procedure dbo.getcounter (@nazwa varchar(15),@counter int out)
as
BEGIN
set nocount on
IF not exists (select * from counter WHERE counter.nazwa = @nazwa)
BEGIN
INSERT INTO counter
(nazwa, counter)
VALUES
(@nazwa, 1)
select @counter=1
return
END
else
begin
SELECT @counter = counter
FROM counter
WHERE nazwa = @nazwa

UPDATE counter
SET counter = @counter + 1
WHERE nazwa = @nazwa AND
counter = @counter
select @counter=@counter+1
return
end

return

END


CREATE TABLE dbo.Transakcje (
Zakres varchar(20) NOT NULL,
Numer int NOT NULL,
Wartosc money NOT NULL,
CONSTRAINT PK_Numery PRIMARY KEY (Zakres, Numer)
)



CREATE TRIGGER nadajnumer on dbo.Transakcje
INSTEAD OF INSERT
AS
BEGIN
declare @Zakres varchar(20)

declare @Wartosc money
declare @counter int
set nocount on

DECLARE numerator CURSOR LOCAL FAST_FORWARD FOR
SELECT Zakres,Wartosc FROM inserted

OPEN numerator

FETCH NEXT FROM numerator into @Zakres,@Wartosc
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC getcounter @Zakres,@counter OUTPUT
insert into dbo.Transakcje (
Zakres ,
Numer ,
Wartosc )
values(@Zakres , @counter , @Wartosc )

FETCH NEXT FROM numerator into @Zakres,@Wartosc
END

CLOSE numerator
DEALLOCATE numerator
END
GO


insert into dbo.Transakcje (
Zakres ,
Numer ,
Wartosc )
select top 10 'start',0,0 from dbo.sysobjects

select * from dbo.Transakcje

Teraz się popastwcie.

Edytowano 2 razy. Ostatnio 2009-07-17 09:29:26 przez vezdohan.
Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


No to pierwszy pomysł już mamy! Dzięki za naprawdę ekspresową reakcję.

Oczywiście cały czas zapraszam Ciebie i wszystkich do zgłaszania swoich rozwiązań i ew. ulepszania obecnego. Postaram się w czasie konkursu nie odnosić merytorycznie do proponowanych rozwiązań, a tylko co najwyżej wyjaśniać wątpliwości w specyfikacji.


__________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Marcin Goł
Marcin Goł
1971 pkt.
Guru
 
0


Marku myslalem o podobnym rozwiazaniu jakie zostalo pokazane powyzej - mam tylko pytanie co z transakcjami/lockami? zdajemy sie na laske tego co sie SQLowi wymysli? w szczegolnosci warto by chyba cos powalczyc w procedurze pobierajacej counter ?

ponadto tutaj dziury i tak moga wystapic - nie ma obslugi bledow jesli jakis insert mimo wszystko nie pojdzie to counter zostanie zwiekszony mimo wszystko ;/ --
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }

--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }
Marcin Szeliga Ekspert WSS
Marcin Szeliga
1834 pkt.
Guru
MVP
 
0


Zaporopnuję rozwiązanie pozakonkuroswe:

USE tempdb
GO
CREATE TABLE Tab1Base (
ID INT IDENTITY,
KOL1 INT,
K0L2 VARCHAR(10));
GO
CREATE VIEW Tab1 AS
SELECT ROW_NUMBER()OVER (ORDER BY ID ASC) AS Numer,*
FROM Tab1Base;
GO

INSERT INTO Tab1 (KOL1, K0L2)
SELECT 12,'A'
UNION ALL
SELECT 1,'B';

SELECT *
FROM Tab1

---
Szelor
http://blog.sqlexpert.pl/
Ekspert @ SQLExpert.pl

Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Uzupełnienie do specyfikacji: oczekuję zapisu permanentnego! Nie chcę, żeby np. wszystkie dowody mi się przenumerowały, jeśli usunąłem jeden ze środka (pomijam dopuszczalność takiej operacji). Takie numery są zazwyczaj bardzo ważne, bo ich odbiorca się na nie powołuje. Muszę po nich również efektywnie wyszukiwać i się na nie bezpiecznie powoływać. Nie wykluczam jednak zastosowania Twojego pomysłu jako wstępu do ciekawego rozwiązania. __________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

vezdohan
vezdohan
779 pkt.
Senior
 
0


A zobacz:
select * from dbo.Transakcje
select * from counter

insert into dbo.Transakcje (
Zakres ,
Numer ,
Wartosc )
select top 10 'start',0,0 from dbo.sysobjects
union all
select top 10 'start',0,null from dbo.sysobjects

select * from dbo.Transakcje
select * from counter

Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Marcin,

Jeśli masz pomysł jak to rozwiązanie naprawić, to śmiało dorzucaj swoją propozycję. Jeśli potrafisz pokazać sytuację w której wystąpi problem współbieżności, to też będzie to cenna uwaga. Jak wspomniałem zależy mi zarówno na nowych rozwiązaniach, jak i na poprawianiu już zamieszczonych. W ten sposób wszyscy uczymy się czegoś ciekawego.

__________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Cezary Ołtuszyk
Cezary Ołtuszyk
485 pkt.
Junior
 
0


Jeżeli dobrze zrozumiałem zadanie, to wystarczy aby licznik działał tylko dla 1 tabeli (w tym przypadku Transakcje) :)

A zatem:

-- tworzymy tabelę z zadania
CREATE TABLE dbo.Transakcje (
Zakres varchar(20) NOT NULL,
Numer int NOT NULL,
Wartosc money NOT NULL,
CONSTRAINT PK_Numery PRIMARY KEY (Zakres, Numer)
)
GO

-- tworzymy tabelę wierszy usuniętych
CREATE TABLE dbo.TransakcjeOld (
Zakres varchar(20) NOT NULL,
Numer int NOT NULL,
Wartosc money NOT NULL,
CONSTRAINT PK_Numery_old PRIMARY KEY (Zakres, Numer)
)
GO

-- tworzymy trigger przepisujący wiersze usunięte z tabeli Transakcje
-- prawdopodobnie i tak będziemy chcieli mieć ich kopie
CREATE TRIGGER trgTransakcjeDelete
ON dbo.Transakcje
AFTER DELETE
AS BEGIN
INSERT INTO dbo.TransakcjeOld(Zakres, Numer, Wartosc)
SELECT Zakres, Numer, Wartosc
FROM deleted
END
GO

-- tworzymy widok pokazujący Maksymalny numer dla danego zakresu
-- z obydwu tabel
CREATE VIEW dbo.v_MaxTranNumbers
AS
SELECT Zakres, MAX(MaxN) AS MaxNumer
FROM (
SELECT Zakres, MAX(Numer) AS MaxN
FROM dbo.Transakcje
GROUP BY Zakres
UNION ALL
SELECT Zakres, MAX(Numer) AS MaxN
FROM dbo.TransakcjeOld
GROUP BY Zakres
) a
GROUP BY a.Zakres
GO

-- tworzymy trigger dopisujący wiersze do tabeli Transakcje
-- główna część zadania
CREATE TRIGGER dbo.trgTransakcjeInsert
ON dbo.Transakcje
INSTEAD OF INSERT
AS BEGIN
INSERT INTO Transakcje(Zakres, Numer, Wartosc)
SELECT i.Zakres, ISNULL(MAxNumer, 0) + ROW_NUMBER() OVER(PARTITION BY i.zakres ORDER BY i.wartosc), i.wartosc
FROM inserted i
LEFT JOIN dbo.v_MaxTranNumbers m
ON i.zakres = m.zakres
END
GO

-- w tym momencie rozwiązanie powinno działać
INSERT INTO dbo.Transakcje(Zakres, Wartosc)
VALUES ('aaa', 5), ('bbb', 10), ('bbb', 11)


PS. Ostatni INSERT działa tylko na SQL 2008, cała logika rozwiązania powinna działać także na SQL 2005 (acz kolwiek nie sprawdzałem, mam tylko 2008)

Pozdrawiam, Czarek

-----------------------------------
MCTS: SQL 2005 IM
MCTS: SQL 2008 DD

Edytowano 1 raz. Ostatnio 2009-07-17 20:44:07 przez coltuszyk.
Łukasz Herman Ekspert WSS
Łukasz Herman
3540 pkt.
Guru
 
0


Ja mam tylko taką uwagę żeby na przyszłość kod dawać w znaczniki [CODE ][ /CODE]. Poprawi to trochę czytelność ;)

Cezary Ołtuszyk
Cezary Ołtuszyk
485 pkt.
Junior
 
0


OK, będę pamiętać.

Nie wiedziałem jak zaznaczyć kod :)

Pozdr, Czarek -----------------------------------
MCTS: SQL 2005 IM
MCTS: SQL 2008 DD

mmoskit
mmoskit
224 pkt.
Junior
 
0


Oto moja propozycja rozwiązania tego nie banalnego wyzwania:

if exists(select 1 from sys.tables where name = 'Transakcje') drop table Transakcje
go
CREATE TABLE dbo.Transakcje (
Zakres varchar(20) NOT NULL,
Numer int NOT NULL,
Wartosc money NOT NULL,
CONSTRAINT PK_Numery PRIMARY KEY (Zakres, Numer)
)
go

if exists(select 1 from sys.tables where name = 'MaxNumerDlaTabeli') drop table MaxNumerDlaTabeli
go
create table MaxNumerDlaTabeli
(
NazwaTabeli sysname not null,
Zakres varchar(20) not null,
MaxNumer int not null,
CONSTRAINT PK_MaxNumerDlaTabeli PRIMARY KEY (NazwaTabeli, Zakres)
)
go

if exists (select 1 from sys.triggers where name = 'trNumer' and object_id = object_id('MaxNumerDlaTabeli')) drop trigger trNumer
go

create trigger trNumer
on Transakcje instead OF insert
as
declare @tmpTable table
(
Zakres varchar(20),
Ilosc int
)
insert into @tmpTable
(Zakres, Ilosc)
select
Zakres,
count(*) as Ilosc
From
inserted
group by
Zakres


insert into MaxNumerDlaTabeli
(NazwaTabeli, Zakres, MaxNumer)
select
'Transakcje', Zakres, 0 as MaxNumer
from
@tmpTable t
where
not exists (select 1 from MaxNumerDlaTabeli where Zakres = t.Zakres)

insert into Transakcje
(Zakres, Numer, Wartosc)
select
i.zakres
, ROW_NUMBER()OVER (PARTITION BY i.Zakres ORDER BY (select 1) ASC) + MaxNumer AS Numer
, i.Wartosc
from
inserted i
inner join MaxNumerDlaTabeli m on i.Zakres = m.Zakres and m.NazwaTabeli = 'Transakcje'

update m set
MaxNumer = MaxNumer + t.Ilosc
from
MaxNumerDlaTabeli m
inner join @tmpTable t on
m.Zakres = t.Zakres
and m.NazwaTabeli = 'Transakcje'

go

/*test*/
begin tran
insert into dbo.Transakcje
(Zakres, Wartosc)
select 'zak1', 0.0
union all
select 'zak2', 0.0
union all
select 'zak3', 0.0
union all
select 'zak2', 0.0
union all
select 'zak3', 0.0
union all
select 'zak3', 0.0
union all
select 'zak4', 0.0
commit

begin tran
insert into dbo.Transakcje
(Zakres, Wartosc)
select 'zak1', 0.0
union all
select 'zak2', 0.0
union all
select 'zak3', 0.0
rollback

go 3

select Zakres, Numer, Wartosc from dbo.Transakcje order by zakres, numer


MCITP SQL Server 2005

ryszarddrozd
ryszarddrozd
21 pkt.
Nowicjusz
 
0


Moja propozycja:

begin tran
declare @nr int;
select @nr = max(Numer) + 1 from Transakcje with (updlock);
if @nr is null
set @nr = 1;
--insert into Transakcje (Zakres,Numer,Wartosc) values (XXX,@nr,YYY);
commit tran;

pafi
pafi
7 pkt.
Nowicjusz
 
0


Witam, poniżej propozycja rozwiązania - trigger, żadnych dodatkowych tabel ani pól w tabeli dbo.Transakcje.


create TRIGGER [dbo].[trgTransakcje_InsteadOfInsert]
ON [dbo].[Transakcje]
instead of insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @tab table(ID int identity(1,1),Zakres varchar(20),Numer int, Wartosc money)
insert into @tab(Zakres,Wartosc)
select Zakres,Wartosc from inserted order by Zakres

update @tab set Numer = ID - (select min(ID) from @tab t1 where t1.Zakres = t.Zakres) + (select isnull(max(Numer),0) from dbo.Transakcje t4 with (tablock) where t4.Zakres = t.Zakres) + 1
from @tab t

insert into dbo.Transakcje(Zakres,Wartosc,Numer)
select Zakres,Wartosc,Numer
from @tab

END



pozdrawiam,
paweł

pozdrawiam, pawel
stasiek.d
stasiek.d
418 pkt.
Junior
 
0


Ja mam coś takiego (patrz poniżej), widze tutaj problemy wydajnościowe ale moja wiedza o blokodach na tabelach jest zbyt mała bym mógł to optymalizować. Może ktoś mi wskazać jak badać że to co mam poniżej wysypie się (zduplikuje się wartość, powstanie klucz) gdy będzie wiele jednoczesnych instertów i jak podblądać jakie blokady miały miejsce. Jak czytam powyższe posty to wszystko odbywa się w sferze wyobraźni a jak testować? Temat transakcji i ziarnistości blokad jest niezwykle ciekawy.

create TRIGGER dbo.t_rozwiazanie
ON dbo.Transakcje
INSTEAD OF INSERT
AS
BEGIN
INSERT Transakcje (zakres, numer, wartosc)
SELECT i.zakres
, isnull(m.z_max,0) + row_number() over (partition by i.zakres order by i.zakres )
, i.wartosc
FROM inserted i left join (SELECT zakres, max(numer)as z_max FROM transakcje GROUP BY zakres) as m on i.zakres = m.zakres
END

Cezary Ołtuszyk
Cezary Ołtuszyk
485 pkt.
Junior
 
0


U mnie rozwiązanie jest przetestowane i nie wykryłem problemów z funkcjonalnością, a w szczególności transakcjami, ziarnistością, ROLLBACK itp.

Jedynym problemem może być wydajność. Tutaj przyznam się szczerze, że liczę na to, że przy małej tabeli nie będzie problemu, zaś przy dużej - łudzę się, iż SQL Server skorzysta z PRIMARY KEY (a dokładniej z indeksu grupującego i rego, że dane są odpowiednio posortowane) i będzie robił INDEX SEEK a nie INDEX SKAN.

Dużych tabel akurat nie sprawdzałem więc jakby co to faktycznie trzeba przećwiczyć :)

Tak czy inaczej, twoje rozwiązanie jest bardzo podobne do mojego i powinno zachowywac się tak samo ... z jedną małą różnicą:

Jak dodasz do tabeli wiersz dla nowego zakresu to dostaniesz w liczniku 1.
Jak potem go skasujesz i dodasz kolejny wiersz to jego licznik znów wynosi 1.

Nie wiem czy tak ma być czy nie. Jeżeli taki jest twój zamiar, to OK.

U mnie po skasowaniu wiersza i dodaniu nowego będzie 2, gdyż chciałem aby każdy zakres miał unikalny licznik niezależnie od tego czy kasowałeś wiersze czy nie.

Jakby ktoś też testował rozwiązania lub miał pomysł na bugi, to też chętnie posłucham.

Pozdr, Czarek -----------------------------------
MCTS: SQL 2005 IM
MCTS: SQL 2008 DD

Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Pytanie o sposób przetestowania problemu jest bardzo słuszne. To znowu wbrew pozorom wcale nie taka łatwa sprawa. Wcale nie łatwo bowiem zasymulować jednoczesną pracę wielu użytkowników. Podpowiem tu kilka sposobów, które być może później ubierzemy w jakiś kod.

1. Joby SQL Server Agenta. Przygotowujemy statementy, które chcemy uruchamiać równolegle i dodajemy do kopii Jobów (step typu Execute Transact SQL Script). Gdy już mamy te kilka/kilkanaście/kilkadziesiąt jobów, uruchamiamy wszystkie za pomocą zeskryptowanych procedur sp_start_job z przekazanymi nazwami lub jobid. Jako, że joby startują asynchronicznie, nawet przy 40 uruchomionych jednocześnie jobach różnice startu są na poziomie co najwyżej dziesiątek milisekund. Wadą rozwiązania jest niemożność przetestowania go na SQL Server Express - tam nie ma agenta.

2. WAITFOR TIME. Nasze zapytania umieszczamy w osobnych skryptach, które wszystkie otwieramy w Management Studio. Na początku każdego ze skryptów umieszczamy polecenie WAITFOR TIME z tym samym parametrem wskazującym na nieodległą przyszłość. Wszystkie skrypty uruchamiamy jeden po drugim - wiszą do momentu nastania godziny zero a potem jednocześnie ruszają. Wadą jest potrzeba poprawiania w kółko argumentu we wszystkich skryptach i czekanie na właściwą chwilę.

3. Locki aplikacyjne. Nawiązujemy połączenie na którym stawiamy procedurą sp_getapplock locka aplikacyjnego typu Exclusive z Ownerem Session. Zapytania umieszczamy w kolejnych skryptach jak w punkcie 2 z tym, że na początek stawiamy w nich takiego samego locka tylko typu shared i nieskończonym timeoutem. Uruchamiamy skrypty jeden po drugim - wszystkie stoją na locku aplikacyjnym. Gdy już wszystkie uruchomimy, zwalniamy locka Exclusive w pierwszym połączeniu albo poprzez sp_releaseapplock albo porzez zwykłe zamknięcie połączenia. Wtedy wszystkie skrypty jednocześnie startują. To rozwiązanie jest chyba najbardziej eleganckie. Szukam chętnego na przygotowanie właściwych skryptów - wtedy na takim środowisku testowym skorzystamy wszyscy.






__________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Właśnie dostałem dodatkowy voucher. Przeznaczam go na nagrodę specjalną dla kogoś, kto jako pierwszy zaproponuje środowisko testowe oparte na pomyśle 3 (locki aplikacyjne).

Jeszcze wyjaśnienie obrazowe:

Musimy uzyskać równy start wszystkich skryptów testowych, żeby zasymulować jednoczesną pracę użytkowników. Używając aktualnej przenośni sportowej: wpuszczamy żużlowców na tor, zastawiamy linię startu taśmą i czekamy aż wszyscy się zameldują. Taśma w górę, a my wypiekami na twarzy obserwujemy rywalizację :).

Ważność konkursu pomocniczego do dziś, godzina 22.00. Jeśli nie otrzymam zgłoszeń, przypadki testowe przygotuję sam. Voucher przejdzie wtedy do wspólnej puli laureatów.

__________
Pozdrawiam
Marek Adamczuk
Blog

Edytowano 1 raz. Ostatnio 2009-07-19 10:37:06 przez marek_adamczuk.

__________
Pozdrawiam
Marek Adamczuk

Radosław Kępa Ekspert WSS
Radosław Kępa
2903 pkt.
Guru
 
0


Marku RML Utilities i testujesz 500 rownoleglych userow :). Dokladnie ostress.exe. Zgrywasz zachowanie jednego usera tracem, przeksztalcasz na zapis RML narzedziem ReadTrace.exe i symulujesz ostress.exe, ktory uruchamia Ci 500 równoleglych watkow w tej samej chwili. Naklad pracy 20 minut. W dokumentacji do RML jest to wszystko bardzo dobrze rozpisane.
Troszę więcej w materiałach z moje ostatniej sesji na PLSSUG.
http://radek4kepa.spaces.live.com/blog/
__________________________
Radosław Kępa
radek@kempes.com.pl

Edytowano 2 razy. Ostatnio 2009-07-19 12:55:11 przez radek_kepa.

__________________________

Radosław Kępa : radek@radekkepa.com

Marcin Goł
Marcin Goł
1971 pkt.
Guru
 
0


wiesz co niby napisalem ale jest dziwnie (byc moze nie zrozumialem popraw mnie w takim razie):

na poczatek kilka uwag:
1) zeby sprobowac wykorzystac locki aplikacyjne musimy umiec wywolac asynchronicznego T-SQL (czyli klania sie service broker, sql agent job, batch lub jakies narzedzie potrafiace symulowac ruch userow) - ja wybralem batcha
2) kolejno:

setup:
if (db_id('wssquiz') is not null)
drop database wssquiz
go
create database wssquiz
go
use wssquiz
go
create table tabela (id int identity(1,1), data datetime default(getdate()), wartosc char(10))
go

zalozenie locka:
osql -S . -E -d wssquiz -q "exec sp_getapplock @Resource = 'QUIZ', @LockMode = 'Exclusive', @LockOwner='Session'"

testowe zapytanie:
insert into tabela (wartosc) values(cast(@@spid as nvarchar(10)))
exec sp_getapplock @Resource = 'QUIZ',@LockMode = 'Shared', @LockOwner='Session', @LockTimeout=-1
insert into tabela (wartosc) values(cast(@@spid as nvarchar(10)))
exec sp_releaseapplock @Resource = 'QUIZ', @LockOwner='Session'

batch generujacy duzo otwartych sesji:
FOR /L %%G IN (1,1,5) DO echo start osql -S . -E -d wssquiz -i test.sql

oczywiscie zamiast "5" można wpisac %1 - wowczas bedzie to parametr ktory mozna przekazac wykonujac skrypt

no i na koncu efekt:


zwrocie uwage jak dziwnie sa zwalniane locki - jak dla mnie cos jest nie halo

:edit:
wada powyzszego rozwiazania: - tyle okien cmd na komputerze ile sesji userow ;/


--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }

Edytowano 2 razy. Ostatnio 2009-07-19 13:05:55 przez theos.
--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }
Marcin Goł
Marcin Goł
1971 pkt.
Guru
 
0


zrobilem kilka malych poprawek, kod dostepny jest tutaj:
http://itsouldiers.com/PLSSUG/wss/test-wss.7z --
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }

--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }
Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Marcin,

To jest nawet bliskie temu, czego potrzebuję ale to jeszcze nie to.
sp_getapplock w pliku test-start jest już dobry. Błędem jest natomiast próba użycia go poprzez osql. Przy tym podejściu połączenie jest nawiązane, lock postawiony i ... połączenie razem z lockiem zerwane. Nie ma szans, żeby na tym exclusive locku skumulowały się te shared ze scenariuszy testowych.

Natomiast użycie tabeli, którą zakładasz w skrypie "setup" też jeszcze nie gra. Rozumiem, że chcesz za jej pomocą mierzyć czas wykonania poszczególnych "jobów". Joby powinny ruszyć jednocześnie - wtedy, gdy zwolnisz exclusive locka. Ty tymczasem robisz insert przed postawieniem locka shared, czyli zaczynasz mierzyć czas dużo za wcześnie.

Do pliku test_start.sql dołóż exclusive zwolnienie exclusive locka z komentarzem, że to trzeba uruchomić wtedy, gdy wystartujesz wszystkie skrypty ze scenariuszami testowymi.

Template scenariusza testowego popraw tak, żeby był najpierw lock, potem zarejestrowanie startu na tabeli loga, później miejsce na właściwy scenariusz polegający na jakimś insercie do tabeli dbo.Transakcje, zalogowanie zakończenia i zwolnienie shared locka. Tabelę loga zaimplementowałbym z 2 polami - datą rozpoczęcia i zakończenia. Start wstawia datę rozpoczęcia, a koniec update'uje datę zakończenia.

I uwaga! Wszystkie skrypty uruchamiamy z SSMS. Ze skryptu start tylko postawienie locka, uruchomienie scenariuszy, zwolnienie locka ze skryptu start - wtedy faktycznie ruszą scenariusze.

Teraz kolejna sprawa: Scenariusze niech nie będą banalne! Wcale też nie muszą być takie same. Proszę o przygotowanie:
- inserta wstawiającego jednocześnie dużo rekordów (w tym do wielu zakresów jednocześnie)
- wielu insertów po jednym rekordzie (np. w pętli)
- dłuższej transakcji zakończonej commitem
- dłuższej transakcji zakończonej ROLLBACKIEM!!!
- jeszcze jakichś innych zagwozdek

Zachęcam autorów rozwiązań do przygotowania scenariuszy i testowania nimi swoich i cudzych rozwiązań. Proszę o dzielenie się nimi na forum, szczególnie tymi, które powalą rozwiązanie konkurencji ;).

__________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Marcin Goł
Marcin Goł
1971 pkt.
Guru
 
0


Błędem jest natomiast próba użycia go poprzez osql. Przy tym podejściu połączenie jest nawiązane, lock postawiony i ... połączenie razem z lockiem zerwane. Nie ma szans, żeby na tym exclusive locku skumulowały się te shared ze scenariuszy testowych.

Marku nie wiem czy patrzyles na wszystkie pliki w paczce - wczesniej bylo faktycznie tak jak mowiesz ale 1.test-LOCK.bat został zmodyfikowany do takiej postaci:

osql -S . -E -d wssquiz -q "exec sp_getapplock @Resource = 'QUIZ', @LockMode = 'Exclusive', @LockOwner='Session'"

a przy takim wywolaniu osql zostawia aktywna sesje - co jest odwrotnoscia tego jesli uzyjemy polecenia z opcja -i <plik input>

Natomiast użycie tabeli, którą zakładasz w skrypie "setup" też jeszcze nie gra. Rozumiem, że chcesz za jej pomocą mierzyć czas wykonania poszczególnych "jobów". Joby powinny ruszyć jednocześnie - wtedy, gdy zwolnisz exclusive locka. Ty tymczasem robisz insert przed postawieniem locka shared, czyli zaczynasz mierzyć czas dużo za wcześnie.

byc moze nie rozumiem dokladnie jak dziala blokada aplikacyjna ale skrypt jaki wykonuje kazdy "worker" wyglada tak:

insert into tabela (spid) values(@@spid)
exec sp_getapplock @Resource = 'QUIZ',@LockMode = 'Shared', @LockOwner='Session', @LockTimeout=-1
insert into tabela (spid) values(@@spid)
exec sp_releaseapplock @Resource = 'QUIZ', @LockOwner='Session'

czyli robie insert do tabeli na starcie - pozniej zakladam locka i znowu robie insert. w ten sposob sytuacja jest taka:

zakladam lock exclusive (1.test-LOCK.bat), po czym uruchamiam duzo powyzszych skryptow
pierwszy insert sie odpali, potem pojdzie request na locka shared i bedzie stal w miejscu; dopiero po zwolnieniu blokady exclusive - shared lock sie zalozy a ja zrobie kolejny insert - po czym zwalniam locka (choc to nie jest konieczne).

to co mnie martwi w moim rozwiazaniu to fakt w jaki sposob sa wykonywane inserty wyklada to mniej wiecej tak jak by inserty do tabeli szly w kolejnosci:

worker#1 insert lock
worker#2 insert lock
worker#3 insert lock
...
master zdejmuje exc lock
...
worker#3 insert
worker#2 insert
worker#1 insert

nie wiem skad takie zachowanie ale zawsze inserty po shared locku sa wykonywane w odwrotnej kolejnosci do startu watkow; inna rzecz ktora powoduje ze mysle ze to dziala w jakis inny sposob jest taka ze jak bys odpalal te skrypty w taki sposob zeby widziec co zwraca procedura zakladajaca lock to tylko 1 worker zwraca 1 z uruchomienia sp_getapplock - wszystkie inne zwracaja 0

0 - The lock was successfully granted synchronously.
1 - The lock was granted successfully after waiting for other incompatible locks to be released.

nie wiedze tutaj tej "bariery" - ktora mial byc exclusie lock; tzn. niby jest ale chyba nie w taki sposob o jaki nam chodzilo.

to co dostarczylem jest jedynie "templatem" do w ktory zostanie ubrane rozwiazanie dostarczone przez ktoregos z konkusowiczow - ja jestem poza nim ;-) - stad takie banalne przyklady z insertami.

--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }

Edytowano 1 raz. Ostatnio 2009-07-19 16:21:53 przez theos.
--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }
Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Co do osql, to rzeczywiście sesja zostaje otwarta. Gdy uruchamiałem go przez xp_cmdshella, sesja do SQL się zamykała, ale, jak widać tylko dlatego, że zamykał się sam xp_cmdshell. Ja też się czegoś nauczyłem :).

Testowałem to rozwiązanie tak, jak powiedziałem, tzn. z Management Studio i mi bariera w postaci Exclusive Locka zadziałała prawidłowo. Moje workery zaczynają od shared locka, a dopiero potem robią inserty do tabeli Transakcje. Wszystkie grzecznie czekają na zdjęcie exclusive locka. Przetestuj to jeszcze, proszę - może jestem odosobniony.

Tymczasem, skoro nie bierzesz udział w konkursie, to doprowadź proszę środowisko do ładu i szansę innym na wymyślenie fajnych scenariuszy testowych. Zależy mi na wręczeniu komuś tego dodatkowego vouchera.
__________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Marcin Goł
Marcin Goł
1971 pkt.
Guru
 
0


mam kilka dobrych wieści ;-)

zweryfikowałem te shared locki i działają dobrze - kolejność była przypadkowa, przy większej liczbie workerow jest ok;-) zmodyfikowalem tez skrypty tak zeby tworzyly twoja tabele.

po shared locku wolam teraz procedure:

create procedure dbo.TransakcjeInsert
as
begin
insert into dbo.Transakcje (Zakres, Numer, Wartosc)
select case
when checksum(newid()) % 4 = 1 then 'Jeden'
when checksum(newid()) % 4 = 2 then 'Dwa'
when checksum(newid()) % 4 = 3 then 'Trzy'
else 'Zero'
end as 'zakres',
0,
rand() * (checksum(newid()) % 10000)
end

jesli ktos chce uzyc mojego rozwiazania jest dostepne tutaj: http://itsouldiers.com/PLSSUG/wss/test-0.2.7z

--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }

Edytowano 2 razy. Ostatnio 2009-07-19 20:31:28 przez theos.
--
Pozdrawiam
Marcin Goł

{ Zapraszam na: PLSSUG & mój nowy blog }
Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Ogłaszam wynik konkursu. Zdecydowałem się nagrodzić kolegów: vezdohan, coltuszyk, mmoskit, pafi. Wybaczcie, że na razie bez komentarza - warunki czasowe nie pozwalają mi na to. Obiecuję dokładniejszą analizę rozwiązań i podsumowanie za jakieś 3 tygodnie.

Vouchery otrzymacie od redaktora naczelnego, Roberta Stuczyńskiego mailem. Jeśli nie macie uzupełnionych mail w profilu, proszę zróbcie to teraz.

__________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Cezary Ołtuszyk
Cezary Ołtuszyk
485 pkt.
Junior
 
0


Dzięki :),

Pozdr, Czarek -----------------------------------
MCTS: SQL 2005 IM
MCTS: SQL 2008 DD

Robert Stuczynski VIP
Robert Stuczynski
8560 pkt.
Guru
MVP
 
0


Gratuluje wygranym ale prosze uzupelnic/udostepnic adresy email jak prosil Marek. Bez tego nie wiem gdzie wyslac Wam vouchery. Nikt tego nie zrobil. Prosze to zrobic do poniedzialku - wtedy wysle emaile.

--
Robert Stuczynski (Noise) - Microsoft MVP
Moj Blog | W2k8 Blog | SEClub

Edytowano 2 razy. Ostatnio 2009-07-25 19:23:47 przez noise.

Robert Stuczynski (Noise) - Microsoft MVP

Cezary Ołtuszyk
Cezary Ołtuszyk
485 pkt.
Junior
 
0


Zrobione :)

Pozdr, Czarek
--------------------------------------------------
MCTS: SQL 2005 IM, MCTS SQL 2008 DD

pafi
pafi
7 pkt.
Nowicjusz
 
0


Hmm, profil mam uzupełniony - jest adres email oraz adres "fizyczny" nawet - przed chwilą sprawdziłem. Chyba, że chodzi o "profil publiczny"? (tu faktycznie u mnie pustawo)

pozdrawiam, pawel
Robert Stuczynski VIP
Robert Stuczynski
8560 pkt.
Guru
MVP
 
0


teraz juz widze.

--
Robert Stuczynski (Noise) - Microsoft MVP
Moj Blog | W2k8 Blog | SEClub

Robert Stuczynski (Noise) - Microsoft MVP

Cezary Ołtuszyk
Cezary Ołtuszyk
485 pkt.
Junior
 
0


A kiedy można się spodziewać maila ??

Bo ja jszcze nie dostałem :)

Pozdr, Czarek --------------------------------------------------
MCTS: SQL 2005 IM, MCTS SQL 2008 DD

Robert Stuczynski VIP
Robert Stuczynski
8560 pkt.
Guru
MVP
 
0


dzis :)

update: poszly przed chwila
--
Robert Stuczynski (Noise) - Microsoft MVP
Moj Blog | W2k8 Blog | SEClub

Edytowano 1 raz. Ostatnio 2009-07-27 14:09:31 przez noise.

Robert Stuczynski (Noise) - Microsoft MVP

vezdohan
vezdohan
779 pkt.
Senior
 
0


Jupii dzięki

vezdohan
vezdohan
779 pkt.
Senior
 
0


Obiecuję dokładniejszą analizę rozwiązań i podsumowanie za jakieś 3 tygodnie.
I?

Marek Adamczuk Ekspert WSS
Marek Adamczuk
2392 pkt.
Guru
 
0


Hm, póki co zawodzę :$ . Niestety po powrocie nie mogę się odgrzebać spod roboty...
Dzięki za call to action. Postaram się coś podjąć temat jak tylko opanuję sytuację. __________
Pozdrawiam
Marek Adamczuk
Blog

__________
Pozdrawiam
Marek Adamczuk

Udziel odpowiedzi

pkt.
Treść wpisu:

Zaloguj się lub Zarejestruj się aby wykonać tę czynność.