Cursor FAST_FORWARD i insert

Witam,
Znalazłem w necie taki opis co do kursora FAST_FORWARD.

(...)Generalnie najszybszy jest taki kursor, który porusza się tylko w jedną stronę i zakłada, że nikt przy jego udziale nie będzie modyfikował danych. Taki kursor definiujemy z opcją FAST_FORWARD(...)

Ale co mam rozumieć przesz modyfikacje danych? Chodiz o modyfikacje danych na tabeli z której jest utworzony kursor?
jakby można jakośłopatologicznie mi to wyjaśnić to byłoby fajnie :)

chciałbym w kursorze robić insert do innej tabeli.

pozdrawiam

Zapomnij o kursorach. NA PEWNO można to zrobić bez nich

No oczywiście, że się da zrobić na wiele sposobów zapewne.
Ale ja też nie jestem tak doświadczony jeszcze, a poza tym to jest jednorazowe przygotowanie danych,
nie będzie to chodzić produkcyjnie, więc nie ma konieczności robienia tego idealnie,
ale fakt, trzeba być profesjonalistą :)

Ale to co chcę zrobić działa idealnie na kursorze.

Mianowicie mam dane: sklep, artykuł, data od,data do. Te daty oznaczają że dany artykuł był na sklepie od do.
Ktoś chce te dane nie w zakresach od do, tylko na każdy dzień za ostatni powiedzmy rok, czy artykuł był na sklepie(1) bądź nie(0).

No i stworzyłem sobie tabelę z kolumną daty, trzymam tam każdy dzień za okres 1 roku i kursorem jadę po każdym dniu i sprawdzam czy jest jakiś artykuł na sklepie gdzie ta data z kursora mieści się between data od and data do, następnie insert sklep, artykuł, data z kursora i jeśli się mieści to 1 jeśli nie to 0. no i tak 365 okrążeń kursora.

A jakieś przykładowe dane wyjściowe?
Co jeśli w danym dniu będzie kilka sklepów i kilka artykułów?

więc tak
Kalendarz

CREATE TABLE #CalendarDay ([Date] date)
declare @startDate date = '2012-01-01'

while @startDate <= '2012-06-01'
begin
	insert into #CalendarDay
	values (@startDate)

	set @startDate = DATEADD(day,1,@startDate)
end

Tabela wynikowa

CREATE TABLE #ItemdeactivationPerDay ([ID_STORE] int,[ID_ARTICLE] int,[DATE] date,[FLAG] bit)

Tabela źródłowa z danymi

CREATE TABLE #ItemdeactivationRanges ([ID_STORE] int,[ID_ARTICLE] int,[DATE_FROM] date,[DATE_TO] date)
insert into  #ItemdeactivationRanges ([ID_STORE],[ID_ARTICLE],[DATE_FROM],[DATE_TO])
VALUES (1,2222,'2012-01-01','2012-02-01'),
	(1,2222,'2012-03-01','2012-04-28'),
	(2,2422,'2012-02-01','2012-03-01'),
	(3,2222,'2012-01-02','2012-03-15'),
	(4,2121,'2012-04-01','2012-05-01'),
	(1,4442,'2012-03-01','2012-04-01'),
	(1,1241,'2012-01-11','2012-02-01')

Mój kursor

DECLARE @DATE DATE
DECLARE cPerDay CURSOR
FOR
SELECT * FROM #CalendarDay

OPEN cPerDay 
FETCH NEXT FROM cPerDay INTO @DATE 
		
WHILE @@FETCH_STATUS = 0 
BEGIN
	INSERT INTO #ItemdeactivationPerDay([ID_STORE],[ID_ARTICLE],[DATE],[FLAG])
	SELECT 
		[ID_STORE]
		,[ID_ARTICLE]
		,@DATE AS [DATE]
		,CASE WHEN @DATE between [DATE_FROM] AND [DATE_TO] THEN 1 ELSE 0 END AS FLAG
	FROM
		#ItemdeactivationRanges 

	FETCH NEXT FROM cPerDay INTO @DATE
END 
CLOSE cPerDay 
DEALLOCATE cPerDay 

No i ogólnie mam to tak rozwiązane jak dodam przy kursorze FAST_FORWARD to idzie o 30% szybciej dla większej ilości danych.
bodajże wejsciowych jest koło 100k wierszy a wypluwa około 100kk wierszy. idzie około 5 minut bez Fasta

Za każdym obrotem kursora wrzucasz do tabeli wszystkie rekordy z #ItemdeactivationRanges.
np dla daty 2012-01-01 ten sam artykuł w tym samym sklepie wejdzie dwa razy raz z flagą na 0 i raz 1
dodaj na koniec

SELECT * FROM #ItemdeactivationPerDay
ORDER BY [DATE], ID_STORE, ID_ARTICLE

i spójrz na 2 i 3 rekord, na pewno tak ma być? :D

2 wiersz poprawiłem :)

Gdzie mam dodać te ordery dokładnie?
bo faktycznie nie powinno tak być :/

Na sam koniec aby wyświetlić wynik, zobacz jak się powtarzają.

Ja bym to zrobił tak:

	INSERT INTO #ItemdeactivationPerDay([ID_STORE],[ID_ARTICLE],[DATE], FLAG)
	SELECT 
		[ID_STORE]
		,[ID_ARTICLE]
		,[DATE]
		,FLAG
	FROM
		#CalendarDay C CROSS APPLY
		(
			SELECT 
				[ID_STORE]
				,[ID_ARTICLE]
			FROM 
				#ItemdeactivationRanges
			GROUP BY 
				[ID_STORE], [ID_ARTICLE]
		) T	CROSS APPLY
		(
			SELECT 
				CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS FLAG
			FROM 
				#ItemdeactivationRanges T2
			WHERE 
				T2.[ID_STORE] = T.[ID_STORE] AND 
				T2.[ID_ARTICLE] = T.[ID_ARTICLE] AND
				C.[DATE] BETWEEN [DATE_FROM] AND [DATE_TO]
		) T3

A to, to wiem że się powtarzają, tylko nie wiem chyba jak to zabezpieczyć, ale przy selecie zawsze będę wybierał ten wiersz gdzie jest 1 i to powinno być poprawne :)

No to w moim zapytaniu się nie powtarzają :D

niestety nigdy nie miałem okazji zastosować cross applaya, więc nie wiedziałem że tak można, przeanalizuje Twoje zapytanie, tak abym zrozumiał od deski do deski :)
Dzięki :)

W poniedziałek Ci napisze :-)

kursor zwykły - 5 minut
kursor FAST_FORWARD- 4 minuty
Cross Apply - 16,5 minuty

Więc średnio, aczkolwiek wynik jest poprawny.
Jeszcze pokombinuje.

Pozytyw jest taki że się dowiedziałem że mój kursor generuje błąd i dowiedziałem jak działa cross apply :)

Analizuje temat dalej i aby zabezpieczyć w kursorze to przed tym aby wpisy się nie powielały gdzie sklep, art, data,flag na przemian 1 i 2,
to musiałbym dodać rownumbera albo counta, a to mega wydłuża :)
więc zostaję przy cross apply :)

Dzięki :)

Tu jest bardziej pasujący cross join, ile pójdzie takie zapytanie:

declare @start_date datetime
,@end_date as datetime

select
	@start_date =  '2012-01-01'
	,@end_date = '2012-06-30';

WITH sample AS (
    SELECT @start_date AS dt
    UNION ALL
    SELECT DATEADD(dd, 1, dt)
      FROM sample s
     WHERE DATEADD(dd, 1, dt) <= @end_date)
SELECT s.dt into #dates
  FROM sample s
OPTION ( MAXRECURSION 500 )


CREATE TABLE #ItemdeactivationRanges ([ID_STORE] INT,[ID_ARTICLE] INT,[DATE_FROM] DATE,[DATE_TO] DATE)
INSERT INTO  #ItemdeactivationRanges ([ID_STORE],[ID_ARTICLE],[DATE_FROM],[DATE_TO])
VALUES (1,2222,'2012-01-01','2012-02-01'),
    (1,2222,'2012-03-01','2012-04-28'),
    (2,2422,'2012-02-01','2012-03-01'),
    (3,2222,'2012-01-02','2012-03-15'),
    (4,2121,'2012-04-01','2012-05-01'),
    (1,4442,'2012-03-01','2012-04-01'),
    (1,1241,'2012-01-11','2012-02-01')

CREATE TABLE #ItemdeactivationPerDay ( [ID_STORE] INT,[ID_ARTICLE] INT,[DATE] DATE,[FLAG] bit)

insert into #ItemdeactivationPerDay
select ID_STORE,ID_ARTICLE,dt ,0 FLAG
from #dates
cross join (select [ID_STORE],[ID_ARTICLE] from  #ItemdeactivationRanges group by [ID_STORE],[ID_ARTICLE]) t

update 
	#ItemdeactivationPerDay
set 
	flag = 1
from 
	#ItemdeactivationPerDay pd
	inner join #ItemdeactivationRanges r on pd.[ID_STORE]=r.[ID_STORE]
											and pd.[ID_ARTICLE]=r.[ID_ARTICLE]
											and pd.[date] between r.[DATE_FROM] and r.[DATE_TO]


select * from #ItemdeactivationPerDay

drop table  #ItemdeactivationPerDay
drop table #ItemdeactivationRanges
drop table #dates

Marcin.Miga napisał(a):

Zapomnij o kursorach. NA PEWNO można to zrobić bez nich

OK, zatem jak bez użycia kursora wstawić wiersze do tabeli pochodzące z SELECT, którego wartość w zadanym polu tej tabeli pochodzi z wywołania procedury składowanej?
Czyli coś takiego:

create table foo(
  id int IDENTITY(1,1) not null primary key, 
  val1 int, 
  val2 int)

insert into foo(val) 
select oldVal, (exec pGetFooID)
 form Foo2

@Panczo no chyba wygrałeś :)
Twoje zapytanie idzie niecałe 6 minut :) dane są identyczne jak z zapytania @dam1an

W gruncie rzeczy nie pomyśałem żeby update robić :)

Ja jestem zadowolony, sprawa załatwiona, a ja poznałem 2 sposoby, na pewno mi się przydadzą :)

Dzięki i pozdrawiam

@leonkuczma nie wiedziałem, że brałem udział w zawodach ;)

@wloochacz Twoje zapytanie bez kursora. Oczywiście od razu napiszę, że to chamski workaround i bardziej bym szedł w kierunku funkcji skalaranej, to ma tylko a celu potwierdzenie, że się da ;) Na marginesie dodam, że perfidnie wybrałeś przykład, bo nawet dokumentacja pisze, że wyjściowe parametry są dostępne tylko z kursora...

Przygotowanie danych testowych:

CREATE TABLE foo(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
  val1 INT, 
  val2 INT)

CREATE TABLE foo2(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
  val1 INT)

create table fooid(
    id int not null primary key
)

insert into foo2 (val1) values (100),(101),(102),(103)

insert into fooid values(1)

CREATE PROCEDURE [dbo].[pGetFooID]
AS
declare @id int
update fooid set id=id+1
select @id = id from fooid
return @id
go

Rozwiazanie bez kursora:

declare @sql nvarchar(max)

set @sql = N'declare @i int
'
select @sql = @sql + 'exec @i = [dbo].[pGetFooID]
insert into foo (val1, val2) values ('+ convert(nvarchar(10),val1)+',@i)
'
from foo2


exec sp_executesql @sql


select * from foo

Panczo napisał(a):

@wloochacz Twoje zapytanie bez kursora. Oczywiście od razu napiszę, że to chamski workaround i bardziej bym szedł w kierunku funkcji skalaranej, to ma tylko a celu potwierdzenie, że się da ;)

Nie da się użyć UDFa, ponieważ ta procedura de-facto coś tam robi z UPDATE ;-)
A tak na marginesie, porównałeś wydajność tej "chamskiej" sklejki z kursorem?
Ja porównałem i tak dla 10 tyś wierszy:
Twoja sklejka: ok. 1 min 20s
Kursor fast_forward: ok. 12s
Komentarz chyba niepotrzebny...

Ktoś ma lepszy pomysł i dowód na to, że kursor jest zawsze najlepszy i na pewno da się zrobić to lepiej szybciej?

Na marginesie dodam, że perfidnie wybrałeś przykład, bo nawet dokumentacja pisze, że wyjściowe parametry są dostępne tylko z kursora...

To cały ja... ;-)
A poważnie, nie perfidnie tylko z życia wzięty.

A tak BTW czy ktoś może mi odpowiedzieć na pierwszy post w tym temacie? bo to też chciałbym wiedzieć :)

leonkuczma napisał(a):

Witam,
Ale co mam rozumieć przesz modyfikacje danych? Chodiz o modyfikacje danych na tabeli z której jest utworzony kursor?
jakby można jakośłopatologicznie mi to wyjaśnić to byłoby fajnie :)

Istnieją specjalne typy kursorów, przy pomocy których możesz aktualizować dane w tabeli w oparciu o ten kursor. A dokładnie - aktualizujesz wiersz, na którym stoi kursor w danej chwili. Gdzie przez kursor należy rozumieć, hmm... aktywny (w danej chwili, bo kursor służy do przeglądania danych) wiersz w tabeli :)
Są też kursory dwukierunkowe, przy pomocy których możesz się poruszać nie tylko do przodu FETCH NEXT from CURSOR, ale też wstecz FETCH PRIOR from CURSOR zbioru danych na którym kursor operuje.

Tu więcej o możliwościach poruszania się w zbiorze danych przy pomocy kursora:
https://technet.microsoft.com/en-us/library/ms187881(v=sql.105).aspx

A co do aktualizacji danych w oparciu o kursor, to przykład wyjaśni więcej:
http://www.manjuke.com/2011/07/how-to-use-update-cursors-in-sql-server.html

Ale uważaj z tym; generalna zasada mówi, nie używaj kursorów. Nigdy. No chyba, ze naprawdę wiesz co robisz i nie ma innej drogi ;-)

wyjście pewnie zawsze jest jakieś inne, ale nie znam pewnie takich opcji jeszcze , dlatego zawsze pisze do Was :)

Teraz wszystko wiem.

Temat można zamknąć :)

Pozdrawiam jeszcze raz i dziękuję wszystkim :)

@wloochacz musze przyznać że nie dawało mi to spokoju i znalazłem inne obejście ;)
Jest mozliwość wywołania procedury z funkcji uzytkownika która zmienia dane, pod pewnymi warunkami:

• będzie to funkcja CLR
• będzie miała dostęp do danych (DataAccessKind.Read)
• nie będzie korzystała z połaczenia "contextowego"
• będzie jako UNSAFE/EXTERNAL_ACCESS
  Postanowiłem to przetestować.
  Przygotowanie danych:

IF OBJECT_ID('dbo.foo', 'U') IS NOT NULL 
  DROP TABLE dbo.foo; 
CREATE TABLE foo(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
  val1 INT, 
  val2 INT)
go
IF OBJECT_ID('dbo.foo2', 'U') IS NOT NULL 
  DROP TABLE dbo.foo2; 
go   
CREATE TABLE foo2(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
  val1 INT)
GO
IF OBJECT_ID('dbo.fooid', 'U') IS NOT NULL 
  DROP TABLE dbo.fooid; 
go   
CREATE TABLE fooid(
    id INT NOT NULL PRIMARY KEY
)
go
 
INSERT INTO foo2 (val1) VALUES (1)
go 10000
 
INSERT INTO fooid VALUES(0)
go 
IF OBJECT_ID('[dbo].[pGetFooID]', 'P') IS NOT NULL 
  DROP PROCEDURE [dbo].[pGetFooID]; 
go  
CREATE PROCEDURE [dbo].[pGetFooID]
AS
DECLARE @id INT
UPDATE fooid SET id=id+1
SELECT @id = id FROM fooid
RETURN @id
GO

Kod funkcji CLR:

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,IsDeterministic = false)]
    public static SqlInt32 pFooCLR(SqlString connString)
    {
        using (SqlConnection cnn = new SqlConnection(connString.ToString()))
        {
            cnn.Open();
            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.pGetFooID";
                var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
                returnParameter.Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                int i = (int)returnParameter.Value; 
                return (SqlInt32)i;

            }
        }
    }

T-SQL do tworzenia ASSEMBLY, należy zmienić nazwę bazy danych

Wynik testu:

"Chamska sklejka":

DECLARE @SQL nvarchar(MAX)
 
SET @SQL = N'declare @i int
'
SELECT @SQL = @SQL + 'exec @i = [dbo].[pGetFooID]
insert into foo (val1, val2) values ('+ CONVERT(nvarchar(10),val1)+',@i)
'
FROM foo2
 
 
EXEC sp_executesql @SQL

Czas wykonania: 0012

Kursor:

DECLARE @I INTEGER
DECLARE @VAL INTEGER
DECLARE SH CURSOR FAST_FORWARD FOR
    SELECT VAL1 FROM FOO2  
OPEN SH
FETCH NEXT FROM SH INTO @VAL

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC @I = pGetFooID
	PRINT @I
	INSERT INTO FOO (VAL1, VAL2) VALUES (@VAL,@I)
	FETCH NEXT FROM SH INTO @VAL
END

CLOSE SH
DEALLOCATE SH

Czas wykonania: 0010

Funkcja CLR:
Tu małe wyjaśnienie, przekazuje connectionstringa po którym bede się łączył w celu wykonania procedury, istotne jest aby znalazł się w nim zdefiniowany parametr Enlist=false, w celu nie wchodzenia w kolizję z transakcją insertu:

insert into foo
select val1,[dbo].[pFooCLR](N'Data Source=nazwaserwera;Initial Catalog=nazwabazydanych;User Id=user;Password=pass;Enlist=false') from foo2

Czas wykonania: 0009

Czyli CLR poszedł najszybciej. Oczywiście niczego to nie dowodzi, a raczej tego, że narzut na czas wykonania kursora jednak jest. Zakładajac, że CLR ma również swój narzut na wykonanie, a jest szybszy => z kursorami trzeba ostrożnie ;)

Piknie. Że też ci się chciało, szacun.
A teraz włącz dla kursora:

set nocount on

i wywal ten print z SQL.
I co ci wyjdzie z wydajności? ;-)

Jest wolniej...
Żeby nie było testuje na developerskiej maszynie więc nie jest to środowisko dograne, szczególnie baza testowa
, zmieniłem kod wg wskazówek. Ten print był do testów czy dobrze robię kursora tak rzadko używam;):

set nocount on
DECLARE @I INTEGER
DECLARE @VAL INTEGER
DECLARE SH CURSOR FAST_FORWARD FOR
    SELECT VAL1 FROM FOO2  
OPEN SH
FETCH NEXT FROM SH INTO @VAL

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC @I = pGetFooID
	INSERT INTO FOO (VAL1, VAL2) VALUES (@VAL,@I)
	FETCH NEXT FROM SH INTO @VAL
END

CLOSE SH
DEALLOCATE SH
set nocount off

Zrobilem test porównawczy, za każdym razem przed puszczenie skryptu robiłem truncate na tabeli foo.
Zrobiłem dla dwóch porcji danych w tabeli foo2: dla 10 000 i 110 000 rekordów.
Wyniki:

To gdzie ta przewaga kursora ;)

Panczo napisał(a):

To gdzie ta przewaga kursora ;)

W prostocie.
Żeby wdrożyć i utrzymać taki fikuśny kod, to ja dziękuje.

Poza tym, nie truncate tylko przed każdym wywołaniem testów powinieneś wyczyścić cache serwera.
np. tak:
http://blog.sqlauthority.com/2007/05/03/sql-server-dbcc-commands-to-free-several-sql-server-memory-caches/
Restart serwera tez pomoże :P

Wydajność będzie zależała od kolejności wykonania testów, obciążenia serwera, itd. Natomiast, jak widać, znalazłeś sposób na szybsze wykonanie operacji od kursora. Tylko, czy ono jest na tyle szybsze aby był sens takich fiku-miku?
Na to pytanie każdy powinien odpowiedzieć sobie sam.

@wloochacz tak wiem, ze mogę wyczyścić cache, ale to nie jest test wydajnościowy, a raczej poglądowy...

Tak jak mówię, to jest obejście, napisanie procedury do pobierania id determinuje mozliwosci użycia silnika.

To takie podjecie rękawicy na Twoje wyzwanie, bo ja za kursory obcinam ręce ;)

Nic nie udowodniliśmy, ale zabawa przednia....