Welcome to
Sign in
|
Help
in
T-SQL (forum)
Romanian Section (group)
(Entire Site)
Search
Home
Blogs
External Feeds
Forums
Photos
Downloads
About Us
»
Romanian Section
»
Intrebari
»
T-SQL
»
Re: stergere recursiva controlata
stergere recursiva controlata
Last post 05-12-2010, 9:49 PM by
neo
. 8 replies.
Sort Posts:
Oldest to newest
Newest to oldest
Previous
Next
05-07-2010, 1:15 PM
8251
neo
Joined on 11-06-2007
Bucharest
sysadmin
stergere recursiva controlata
Reply
Quote
Salut,
Cum as putea realiza pe un tabel o stergere recursiva controlata care sa tina cont de incarcare (spid-uri) iar daca situatia devine critica procesul sa se opresca pe parcursul stergerii (si sa si checkpointeze...sa nu faca rollback).(sql server 2005 std edition).
Tabelul are constrangeri, chei primare insa e vorba de niste inregistrari orfane care trebuie sterse si sunt f multe zeci de milioane. Cu un simplu "delete " mi s-ar bloca aplicatiile.
Multumesc
G.
Report abuse
05-07-2010, 3:45 PM
8252
in reply to
8251
katalyn
Joined on 09-20-2006
Bucureşti
sysadmin
Re: stergere recursiva controlata
Reply
Quote
pai si un simplu delete 10000 ? eventual poti face o procedura care face delete top 1000 from .... order by PK where .... si o poti executa printr-un job pe care sa-l setezi sa se execute cand "Start whenever the CPUs become idle" ... sau poti sa rulezi stergerea noaptea .... O alta idee ar fi sa folosesti resource governor daca vrei sa te complici putin.
Cătălin D.
Report abuse
05-07-2010, 4:47 PM
8253
in reply to
8252
neo
Joined on 11-06-2007
Bucharest
sysadmin
Re: stergere recursiva controlata
Reply
Quote
pot sterge maxim o mie de linii o data si doar din script si nu trebuie sa utilizez nu din job.
G.
Report abuse
05-07-2010, 6:41 PM
8254
in reply to
8253
Diana
Joined on 03-21-2006
sysadmin
Re: stergere recursiva controlata
Reply
Quote
Sterge de exemplu 1000 de inregistrari intr-un batch, Vezi
http://www.sqlservercentral.com/articles/T-SQL/67898/
Report abuse
05-07-2010, 6:47 PM
8255
in reply to
8254
neo
Joined on 11-06-2007
Bucharest
sysadmin
Re: stergere recursiva controlata
Reply
Quote
Stergerea utilizand un "cursor", din pct de vedere incarcare este mai fiabila comparativ cu o stergere in bucla "while"?
Multumesc.
G.
Report abuse
05-07-2010, 6:58 PM
8256
in reply to
8255
Diana
Joined on 03-21-2006
sysadmin
Re: stergere recursiva controlata
Reply
Quote
Nu vad de ce ai avea nevoie de cursor.
Report abuse
05-07-2010, 7:02 PM
8257
in reply to
8256
neo
Joined on 11-06-2007
Bucharest
sysadmin
Re: stergere recursiva controlata
Reply
Quote
am zeci de milioane de inregistrari si mai multe aplicatii care ruleaza in timp real...si ma intreb care dintre variantele de stergere are planuri de executie si costuri mai eficiente pentru a nu cauza blocaj(e).
G.
Report abuse
05-12-2010, 9:11 PM
8267
in reply to
8257
xmldeveloper
Joined on 01-18-2006
Bucharest, Romania
Rank NA
Re: stergere recursiva controlata
Reply
Quote
Evitati cursoarele. In situatia descrisa de dumneavoastra un ciclu while este mult mai eficient.
Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro
Report abuse
05-12-2010, 9:49 PM
8271
in reply to
8267
neo
Joined on 11-06-2007
Bucharest
sysadmin
Re: stergere recursiva controlata
Reply
Quote
Va multumesc.
G.
Report abuse