Hej! Jag tycker inte dina datamängder ser skrämmande stora ut. En ide, som kanske krånglar till det för dig, är att behålla data i orginal tabellen och skapa kopior efter hand som det behövs. Dvs som snapshot i sql 2005 gör. Lite mer programmering men håller nere database storleken om det är ett problem område. Hej! Nu är det jag som känner mig dum! ...är nog snarare jag som är dum :) Svarar bara på en kommentar om Database Snapshot i det hela... Hade jag inte varit allt för trött skulle jag satt mig in i huvudproblemet...En fråga angående 'INSERT INTO x SELECT * FROM y WHERE ...
Jag håller på med ett program för tidsplanering och tidsrapportering. Projektledare planerar hur mycket tid som ska läggas för de olika anställda för olika aktiviteter, och de anställda rapporterar in riktig tid.
För detta ändamål har jag en tabell 'Task' som innehåller dessa poster.
En post i denna tabell tar i genomsnitt ca 150 bytes.
Beräknad tillväxt är för denna tabell ca 100 000 - 200 000 poster per år.
Med enkel matematik kommer man fort fram till att denna tabell med tiden blir ganska stor.
En funktion ska nu implementeras så att projektledarna kan skapa arbetskopior att jobba med. Dvs man kan från den riktiga planeringen skapa flera arbetskopior.
Detta som händer när en arbetskopia skapas är att ca 50 000 - 100 000 poster från den riktiga planeringen rakt av kopieras och ett fält i tabellen 'Task', 'WorkCopyId' sätts till det Id som den skapade arbetskopian får.
Det jag undrar över är den stored procedure som utför denna kopiering av poster.
Det jag tänkt att använda mig av är en konstruktion enligt följande:CREATE PROCEDURE dbo.spI_Task_WorkingCopy
(
@WorkingCopyID int,
@dtmDateCriteriaBegin datetime,
@dtmDateCriteriaEnd datetime
)
AS BEGIN
SET NOCOUNT ON
DECLARE @Return int
BEGIN TRANSACTION
INSERT INTO [dbo].[Task]
(
[Name],
[ProjectID],
[Description],
[ProcentDone],
[TaskType],
[Start],
[TimeEstimated],
[TimeReal],
[Deadline],
[Priority],
[Milestone],
[RestrictionType],
[RestrictionDate],
[Notes],
[OwnerPersonID],
[MasterTaskID],
[Status],
[WorkingCopyID]
)
SELECT
[Task].[Name],
[Task].[ProjectID],
[Task].[Description],
[Task].[ProcentDone],
[Task].[TaskType],
[Task].[Start],
[Task].[TimeEstimated],
[Task].[TimeReal],
[Task].[Deadline],
[Task].[Priority],
[Task].[Milestone],
[Task].[RestrictionType],
[Task].[RestrictionDate],
[Task].[Notes],
[Task].[OwnerPersonID],
[Task].[MasterTaskID],
[Task].[Status],
@WorkingCopyID
FROM [dbo].[Task]
INNER JOIN [dbo].[Project] ON [dbo].[Project].[ProjectID]=[dbo].[Task].[ProjectID]
INNER JOIN [dbo].[UserProfile] ON [dbo].[Task].[OwnerPersonID]=[dbo].[UserProfile].[PersonID]
WHERE [dbo].[Task].[Start] BETWEEN @dtmDateCriteriaBegin AND @dtmDateCriteriaEnd
AND [dbo].[Task].[WorkingCopyId]= '0'
AND [dbo].[Task].[Status] = 'Preplanned'
AND [dbo].[Project].[Status]= 'Active'
AND ([dbo].[UserProfile].[UserStatus] = 'Active' OR [dbo].[UserProfile].[UserStatus] = 'JohnDoe' OR [dbo].[UserProfile].[UserStatus] = 'Missing')
IF @@ERROR <> 0 GOTO ERR_HANDLER
SET @Return = @@RowCount
COMMIT TRANSACTION
SET NOCOUNT OFF
RETURN(@Return)
ERR_HANDLER:
SELECT N'Unexpected error occured creating working copy'
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN 0
END
Den riktiga planeringen har WorkingCopyId = 0
TaskID är primärnyckel. Inga andra fält indexeras i nuläget.
Min fråga är huruvida det är lämpligt med denna konstruktion?
Har läst om att problem med att transaktions loggen blir överfull på grund av allt körs som en enda transaktion och att loggen inte kan bli trunkerad förräns allt är klart.
Vad jag förstått går det inte att minimera loggningen för INSERT INTO operationer, och det är heller
inte möjligt att "stänga av" loggningen.
Många säger att man ska bryta ned operationen i mindre batcher. Hur löser jag detta, och hur stora batcher ska jag välja? 1000 poster i taget, 10 000 poster i taget?
/Med vänliga hälsningar
Stefan Johansson
Sv: En fråga angående 'INSERT INTO x SELECT * FROM y WHERE ...
Om din databasserver är bra planerad, med transloggar och data på olika diskar, tillräckligt stora och tillräckligt snabba diskar, och en tillräckligt stor translogg, så borde det gå bra så som du gör.
En sån här task-copy ska väl ändå inte göras så fruktansvärt ofta, eller?
Det är bra att tänka på potentiella prestandaproblem i förväg, men efter att man har gjort det, och eliminerat flaskhalsar som är uppenbara, är det min erfarenhet att det ofta är viktigare att man har skrivit strukturerad och lättförvaltad kod. Och din SP ser strukturerad och lättförvaltad ut.
Ofta misstar man sig i början på vad som kommer att bli flaskhalsarna, och så optimerar man på fel ställe iallafall. Resultatet blir kod med svårbegripliga optimeringar på TVÅ ställen (minst) istället för bara ett.
Ofta (inte minst när det gäller SQL Server-voodoo) får man goda råd från människor som "har läst någonstans", men som designer skulle jag inte krångla till min design baserat på generella tips och best practices.
Är du orolig, så gör en "spike". Ett snabbt test. Fyll en testdatabas (uppsatt som ovan) med skräp i rätt storleksordning. Pröva din SP. Klocka.
Lycka till!Sv: En fråga angående 'INSERT INTO x SELECT * FROM y WHERE ...
Sv:En fråga angående 'INSERT INTO x SELECT * FROM y WHERE ...
Tack för svaren.
Som du säger Göran så är inte datamängderna särskilt stora. Jag delar din uppfattning (efter att läst på lite) att om databasservern är bra planerad så lär det inte blir några problem.
Denna procedur kommer endast köras då man skapar arbetskopior, vilket inte kommer hända särskilt ofta. Skulle gissa på att den maximalt kommer köras 5-10 ggr per månad.
Ponera att jag istället för att kopiera ca 100 000 poster som max skulle behöva kopiera 1 000 000 poster, skulle en konstruktion i stil med nedanstående då vara ett bra alternativ?CREATE PROCEDURE dbo.spI_Task_WorkingCopy_Massive
(
@WorkingCopyID int,
@dtmDateCriteriaBegin datetime,
@dtmDateCriteriaEnd datetime,
@batch_size int = null -- optional. If omitted or specified as 0 or NULL, the update will be done as a single, large transaction.
)
AS BEGIN
SET NOCOUNT ON
DECLARE @msg varchar(4000)
DECLARE @Return int
DECLARE @rows_processed int
SET @batch_size = CASE COALESCE(@batch_size,0) WHEN 0 THEN 0 ELSE @batch_size END
--
-- If small batches were asked for and we're already in a transaction, hurl
--
IF (@batch_size <> 0 and @@TRANCOUNT > 0)
BEGIN
SET @msg = 'ERROR: '
+ 'Can''t update in batches. '
+ 'This stored procedure is running within an uncommitted transaction.'
RAISERROR(@msg , 16, 1)
RETURN -1
END
--
-- Do the massize update in batches, if requested. If @batch_size is 0,
-- then the first batch will be the only batch ( SET ROWCOUNT 0 says that
-- there are no limits on the number of rows to process
--
SET @rows_processed = -1
BEGIN TRANSACTION
WHILE (@rows_processed <> 0)
BEGIN
SET ROWCOUNT @batch_size -- set the batch size
INSERT INTO [dbo].[Task]
(
[Name],
[ProjectID],
[Description],
[ProcentDone],
[TaskType],
[Start],
[TimeEstimated],
[TimeReal],
[Deadline],
[Priority],
[Milestone],
[RestrictionType],
[RestrictionDate],
[Notes],
[OwnerPersonID],
[MasterTaskID],
[Status],
[WorkingCopyID]
)
SELECT
[Task].[Name],
[Task].[ProjectID],
[Task].[Description],
[Task].[ProcentDone],
[Task].[TaskType],
[Task].[Start],
[Task].[TimeEstimated],
[Task].[TimeReal],
[Task].[Deadline],
[Task].[Priority],
[Task].[Milestone],
[Task].[RestrictionType],
[Task].[RestrictionDate],
[Task].[Notes],
[Task].[OwnerPersonID],
[Task].[MasterTaskID],
[Task].[Status],
@WorkingCopyID
FROM [dbo].[Task]
INNER JOIN [dbo].[Project] ON [dbo].[Project].[ProjectID]=[dbo].[Task].[ProjectID]
INNER JOIN [dbo].[UserProfile] ON [dbo].[Task].[OwnerPersonID]=[dbo].[UserProfile].[PersonID]
WHERE [dbo].[Task].[Start] BETWEEN @dtmDateCriteriaBegin AND @dtmDateCriteriaEnd
AND [dbo].[Task].[WorkingCopyId]= '0'
AND [dbo].[Task].[Status] = 'Preplanned'
AND [dbo].[Project].[Status]= 'Active'
AND ([dbo].[UserProfile].[UserStatus] = 'Active' OR [dbo].[UserProfile].[UserStatus] = 'JohnDoe' OR [dbo].[UserProfile].[UserStatus] = 'Missing')
IF @@ERROR <> 0 GOTO ERR_HANDLER
SET @rows_processed = @@ROWCOUNT -- get the number of rows affected
SET @Return = @Return + @rows_processed
SET ROWCOUNT 0 -- restore the default batch size
END
COMMIT TRANSACTION
SET NOCOUNT OFF
RETURN(@Return)
ERR_HANDLER:
SELECT N'Unexpected error occured creating working copy'
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN 0
END
Nackdelarna med detta är som jag ser det:
1. Om databasservern är bra planerad (med tillräcklig log) så är det möjligt att denna konstruktion försämrar prestandan.
2. ACID egenskaperna bryts. Om denna rutin kopierar 10 000 poster och körs som en transaktion kommer antingen allt blir bra eller så avbryts allt. Om jag delar upp i batcher är det möjligt att andra användare påverkat data under gång.
SQL Server 2005 har jag inte hunnit titta på alls i nuläge. Snapshot har jag inte en aning om vad det är. Låter klart intressant så det skriver jag upp på listan.
Som ni båda säkert förstår så är jag relativt grön på SQL, har hållit på i ca ett år, under denna tid endast på fritiden och då jag sommarjobbat.
Alla tips välkomnas tacksamt!
/Mvh
Sv: En fråga angående 'INSERT INTO x SELECT * FROM y WHERE ...
Räcker det med att bara sätta ROWCOUNT till något och sedan upprepa din insert i en loop?
Hur vet den vid varv nummer två...n att den inte ska börja om med samma poster som den tog i första varvet?
Åter till grundfrågan;
Det ser ut som om du har rätt många fält i tabellen, som allihop måste kopieras.
En typisk "databasig" reflektion över det är ju att dina arbetskopior troligen till 99% kommer att vara lika orginalen (om man gör en arbetskopia som innehåller 100000 poster är det osannolikt att du ändrar i allihopa).
Det får mig att tycka att du skulle ha en grundtabell med "post-datat" i, och sedan en tabell för arbetskopior. Den skulle bara ha två kolumner, ett kopie-id och ett id som refererar till grundposten.
Om någon ändrar en post får man lägga in en ny post i grundtabellen, och sätta om id:t i arbetskopian.
Eftersom det kan komma att finnas poster i grundtabellen som bara hör till en arbetskopia, måste även den skarpa datamängden finnas refererad till i arbetskopietabellen, förslagsvis med arbetskopie-id 0.
Det där blev lite otydligt, men du kanske hänger med?
Det är iallafall en lösning som jag skulle gilla, om man förutsätter att det kommer att finnas rätt många arbetskopior som alla refererar till samma poster. Eller omvänt; att varje grundpost sannolikt kommer att finnas kopierad i minst en arbetskopia.Sv:En fråga angående 'INSERT INTO x SELECT * FROM y WHERE ...
Anledningen till att jag fick för mig att detta skulle funka kommer från http://www.codeproject.com/useritems/BatchUpdate.asp?msg=1355775#xx1355775xx, men det känns inte alls okej längre. Den kan ju omöjligen veta vart den ska fortsätta kopiera från.
Dessutom så kan man på microsofts hemsida läsa:
"Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL)."
Tillbaks till grundfrågan:
Mmmm, utan att jag gett dej några detaljer om hur arbetskopian ska användas så verkar du listat ut det rätt bra själv. Som du säger så kommer de vara ruskigt lika orginalen.
Lösningen ovan gillar jag sjukt mycket. Varför har jag inte tänkt så? Så... hmmm "enkelt" allt blev.
10 poäng till dej!
Med lösningen ovan känns problemet löst, och tillsammans med vad som tidigare sagts om planering av databasservern tycker jag jag fått mina frågor besvarade.
//Mvh StefanSv: En fråga angående 'INSERT INTO x SELECT * FROM y WHERE ...
Database Snapshot är INTE en lösning för detta. Ett snapshot är en ögonblicksbild på databasen som måste ligga på samma server, är read only och innehåller bara de förändrade datasidorna av databasen, dvs kopia på de sidor som förändras efter att snapshotten skapats. Du kan inte använda den för att bygga dina arbetskopior på alltså.
Arbetskopiorna måste du hantera i den riktiga databasen, helst i samma tabell, gärna med kolumndata typ status och version av status. Är du rädd för prestandaproblem kan du om du kör SQL 2005 skapa PARTITIONED TABLES och lägga delar av databasen i olika filgrupper... Eller så ska du använda en parallell tabell som innehåller arbetskopiorna med versionhantering om du vill det med...
Du skapar kopian med en procedur som gör
INSERT INTO ARBETSKOPIA (kolspec, status, version)
SELECT kolspec, 1, 1 FROM SKARPTABELL WHERE id={aktuellt id}
sedan har du en uppdateringsprocedur som gör update när du ska labba med datat.
UPDATE ARBETSKOPIA
SET version=version + 1, kol=värde, annan kol=annat värde osv
WHERE id={aktuellt id}
eller om du inte villersätta värdena utan skapa fler värden i olika versioner
INSERT...
Till sist en procedur för att aktivera arbetskopian till skarp genom att göra en
INSERT INTO SKARPTABELL (kolspec)
SELECT kolspec FROM ARBETSKOPIA WHERE id={aktuellt id}
IF OK eller hur du vill kolla av DELETE FROM ARBETSKOPIA WHERE id={aktuellt id}
Ditt behov kan lösas väldigt komplext och ge en dynamik som även blir lagringseffektiv och snabb genom lite normalisering samt korrekt indexering...
Har nu svarat utan att egentligen läst din egentliga fråga och kanske redan svarat på hur du gör...
sånt händer ibland... *ler är som sagt väldans trött och borde krypa till kojs...
mattelatte