/u/3490/Web_logo.png?f=community){kind=logo}
Ik ben bezig met een Com component, wat straks vanuit een ASP pagina aangeroepen gaat worden, om data uit een geupload bestand in te lezen, te bewerken en te importeren in MS SQL Server.
Het geheel werkt prima, maar het importeren / verwerken / invoeren in de DB duurt 1 uur.
Nu weet ik zeker dat dat een stuk sneller moet kunnen, dus ben ik begonnen met de optimalisatie van het geheel. Na wat uitzoekwerk, blijkt de bulk van de tijd (70%) in het laatste gedeelte te zitten. (Dus het importeren in SQL server)
Bij leeswerk over optimalisatie kwam ik overal het verhaal van Stored Procedures tegen, dus heb ik mijn queries omgezet naar Stored Procedures. Dit leverde al 10 minuten tijdswinst op.
Hieronder heb ik een stukje van de gebruikte VB code (De totale applicatie bevat 5 van dit soort constructies, die met net iets andere data en tabellen werken), de Stored Procedure en het SQL script om de tabel te maken neergezet, want ik zit nog met een paar dingen.
En het SQL Script (Door SQL Server gegenereerd, dus niet echt optimaal) Deze tabel bevat ongeveer 28000 records
En de gebruikte Stored Procedure
Zoals gezegd, werkt het bovenstaande prima, maar omdat hij binnen een transactie in een lus door alle records loopt (Ongeveer 5000) creeert ie voor elk record een bijbehorende row-lock in de tabel. Wat de performance volgens mij niet ten goede komt.
Wat ik dus wil weten is het volgende:
- Is het beter om een exclusive table-lock te gebruiken, want ten tijde van de import kan de rest van de applicatie toch niet benaderd worden (Zo ja, hoe doe ik dit dan?)
- Zijn er nog andere dingen die ik kan doen om dit geheel te optimaliseren?
Het geheel werkt prima, maar het importeren / verwerken / invoeren in de DB duurt 1 uur.
Nu weet ik zeker dat dat een stuk sneller moet kunnen, dus ben ik begonnen met de optimalisatie van het geheel. Na wat uitzoekwerk, blijkt de bulk van de tijd (70%) in het laatste gedeelte te zitten. (Dus het importeren in SQL server)
Bij leeswerk over optimalisatie kwam ik overal het verhaal van Stored Procedures tegen, dus heb ik mijn queries omgezet naar Stored Procedures. Dit leverde al 10 minuten tijdswinst op.
Hieronder heb ik een stukje van de gebruikte VB code (De totale applicatie bevat 5 van dit soort constructies, die met net iets andere data en tabellen werken), de Stored Procedure en het SQL script om de tabel te maken neergezet, want ik zit nog met een paar dingen.
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| Dim teller As Long
Dim raf As Integer
Dim com As New Command
Dim param As Parameter
Dim pbsArray
[Hier stond een heleboel code, die niet van belang is voor dit probleem]
Set com.ActiveConnection = conn
com.CommandText = "CRIS_PBS_Insert"
' Creeer alle parameters
Set param = com.CreateParameter("naam", adVarChar, adParamInput, 40, "")
com.Parameters.Append param
Set param = com.CreateParameter("rnr", adVarChar, adParamInput, 25, "")
com.Parameters.Append param
Set param = com.CreateParameter("tijd", adVarChar, adParamInput, 255, "")
com.Parameters.Append param
conn.BeginTrans
' Doorloop alle personen
For teller = LBound(pbsArray, 2) To UBound(pbsArray, 2)
' Set alle parameters
com.Parameters("naam") = pbsArray(0, teller)
com.Parameters("rnr") = pbsArray(3, teller)
com.Parameters("tijd") = huidigeTijd
' Voer de persoon in, indien zijn RNR nog niet voorkomt.
com.Execute raf, , adCmdStoredProc
Next
conn.CommitTrans |
En het SQL Script (Door SQL Server gegenereerd, dus niet echt optimaal) Deze tabel bevat ongeveer 28000 records
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| /****** Object: Table [dbo].[personeel_gegevens] Script Date: 10-10-01 13:40:43 ******/
CREATE TABLE [dbo].[personeel_gegevens] (
[naam] [varchar] (40) NOT NULL ,
[rnr] [varchar] (25) NOT NULL ,
[pid] [varchar] (32) NOT NULL ,
[tijd] [varchar] (255) NULL
)
GO
ALTER TABLE [dbo].[personeel_gegevens] WITH NOCHECK ADD
CONSTRAINT [PK_personeel_gegevens] PRIMARY KEY CLUSTERED
(
[pid]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [CRIS_RNR] ON [dbo].[personeel_gegevens]([rnr]) ON [PRIMARY]
GO
GRANT SELECT , INSERT , DELETE , UPDATE ON [dbo].[personeel_gegevens] TO [CRIS]
GO |
En de gebruikte Stored Procedure
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| CREATE PROCEDURE CRIS_PBS_Insert (@naam varchar(40), @rnr varchar(25), @tijd varchar(255)) AS
DECLARE @pid varchar(32)
SELECT @pid = Replace(Replace(Replace(newid(),'-',''),'}',''),'{','')
UPDATE personeel_gegevens
SET naam=@naam, tijd=@tijd
WHERE rnr=@rnr
IF @@ROWCOUNT<>1
BEGIN
INSERT INTO personeel_gegevens (pid, naam, rnr, tijd)
VALUES (@pid, @naam, @rnr, @tijd)
END |
Zoals gezegd, werkt het bovenstaande prima, maar omdat hij binnen een transactie in een lus door alle records loopt (Ongeveer 5000) creeert ie voor elk record een bijbehorende row-lock in de tabel. Wat de performance volgens mij niet ten goede komt.
Wat ik dus wil weten is het volgende:
- Is het beter om een exclusive table-lock te gebruiken, want ten tijde van de import kan de rest van de applicatie toch niet benaderd worden (Zo ja, hoe doe ik dit dan?)
- Zijn er nog andere dingen die ik kan doen om dit geheel te optimaliseren?
No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.
/u/31230/crop64b060d01fd80_cropped.png?f=community)
. 'hopen dat het goed gaat' is geen oplossing. WETEN dat het goed gaat, is essentieel, vandaar ook die transactie. Je gaat dan niet achteraf primary keys zetten. Wellicht runt hij deze update op een bestaande tabel... Databases is geen vak waarbij je even wat kunt knoeien. Datamodel ontwerpen -> tables designen -> tables implementeren -> keys definieren -> initiele indices en constraints definieren -> foreign keys definieren -> triggers definieren -> get/set stored procs definieren -> manipulate stored procs definieren -> higher level code implementeren. 
)