[MSSQL/Python] INSERT snelheid verbeteren

[hertogbram]

Vraag: Beste tweakers, heel beknopt mijn vraag: hoe komt het dat een 'INSERT INTO' query relatief lang duurt zonder noemenswaardige server belasting? En hoe zou ik het kunnen versnellen?

Achtergrond:
Voor een project om een Neural Network op scam emails te trainen ben ik de scam email bodies van de website 419scam.org aan het scrapen met Python beautifulsoup4.

Geschat 1M emails in de database.

Nu heb ik een scraper zo geschreven, dat nadat een deel van het domein gescraped is, de email bodies worden weggeschreven naar de SQL database (+/- 300 rows per batch) en dan wordt de volgende pagina gescraped.

Het scrapen gaat heel vlot, maar het wegschrijven minder. De eerste 2k rows gingen in een oogwenk, daarna met nog slechts 2 tot 10 rows per seconde terwijl de server nauwelijks belast wordt. Gemiddeld 35kB/s writes en 1% processorbelasting. Ook de computer waar de python SQL client op draait zit duimen te draaien. Tijdens het scrapen zorgt python voor 10% processorbelasting dat naar <1% wegzakt tijdens het wegschrijven.

Het wegschrijven naar de database gaat via een stored procedure, zie hieronder.

Niks wijst er op dat de hardware het niet aankan, toch duurt het heel lang en is de write snelheid nog maar een fractie van de initiële write snelheid. Mijn vermoeden is dat de tabel onnodig lang locked is en dat alle processen op elkaar aan het wachten zijn. Daarom heb ik tijdens het draaien van het python script de volgende query afgevuurd:

SQL:

select max(ID) from table_name 

Execution time: 14 minuten

Dan met nolock:

SQL:

select max(ID) from table_name with(NOLOCK) 

Execution time: 0.01 seconde

Waar zou dit toch door komen? Heeft iemand een idee?


Relevante software en hardware die ik gebruik
MS server 2012 virtual machine (6GB RAM, SSD storage, 2 virtual cores op een i5-3570)
SQL service: MSSQL12.MSSQLSERVER
SQL client: python3.5 mssql object

Relevante sql tabel opbouw:

SQL:

CREATE TABLE [dbo].[scam419_raw](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [timestamp] [datetime] NOT NULL,
    [from_address] [nvarchar](255) NULL,
    [reply_address] [nvarchar](255) NULL,
    [date] [datetime] NULL,
    [subject] [nvarchar](1500) NULL,
    [body] [text] NULL,
    [url] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Stored procedure om data weg te schrijven:

SQL:

ALTER PROCEDURE [dbo].[insert_raw_scam]
    @cur_time datetime,
    @from_address nvarchar(255),
    @reply_address nvarchar(255),
    @date datetime,
    @subject nvarchar(1500),
    @body text,
    @url nvarchar(255)
AS BEGIN
    INSERT INTO scam419_raw
    (from_address, timestamp, reply_address, date, subject, body, url)
    (SELECT @from_address, @cur_time, @reply_address, @date,@subject, @body, @url);
END;

Wat ik verder geprobeerde heb:
- Python threaded gedraaid. Door meerdere threads te gebruiken kan ik de belasting op de SQL server continue houden

- INSERT INTO ... with(ROWLOCK) in de stored procedure, dit lijkt de write speed iets te verhogen en is wat constanter, maar geen drastische prestatie verbetering.

[Diamondo25]

Waarom een stored procedure en niet zelf bulk queries schrijven? Of, door middel van prepared statements sneller inladen?

Je zou trouwens met de tools van microsoft kunnen zien wat zo lang duurt, volgensmij.

[Gomez12]

Wat ik altijd bij dit soort dingen doe is :
1e poging direct naar dbase
als dat niet goed gaat dan scraper naar txt-file / csv-file laten schrijven en die later met bulkimports in de database stouwen.

Het gaat nu eenmaal eenmalig om bulk-data.

[GlowMouse]

Wellicht dat je moet wachten op fsyncs. In dat geval kan het helpen om meerdere inserts in één transactie te combineren.

Zijn er indices op de tabel gedefinieerd? Insert je in dezelfde volgorde als waarin de data wordt opgeslagen?

[ Voor 32% gewijzigd door GlowMouse op 26-04-2017 14:21 ]

[MoBi]

Het is wel slim om een primairy key op ID te plaatsen. Zodat er ook een index aan word toegevoegd.

[hertogbram]

Bedankt voor jullie reacties!

Diamondo25 schreef op woensdag 26 april 2017 @ 14:09:
Waarom een stored procedure en niet zelf bulk queries schrijven? Of, door middel van prepared statements sneller inladen?

Je zou trouwens met de tools van microsoft kunnen zien wat zo lang duurt, volgensmij.

Die tools ga ik nu induiken, ben niet heel bekend met de 'achterkant' van SQL. Ik gebruik stored procedures omdat dit heel simpel in Python pymssql te implementeren is.

Is een stored procedure met een simpele INSERT niet equivalent aan een pure INSERT INTO query?

Gomez12 schreef op woensdag 26 april 2017 @ 14:16:
Wat ik altijd bij dit soort dingen doe is :
1e poging direct naar dbase
als dat niet goed gaat dan scraper naar txt-file / csv-file laten schrijven en die later met bulkimports in de database stouwen.

Het gaat nu eenmaal eenmalig om bulk-data.

Goede tip, die gebruik ik als last resort. Ellendige is dat 't dan textfiles van enkele GB wordt.

GlowMouse schreef op woensdag 26 april 2017 @ 14:18:
Wellicht dat je moet wachten op fsyncs. In dat geval kan het helpen om meerdere inserts in één transactie te combineren.

Zijn er indices op de tabel gedefinieerd? Insert je in dezelfde volgorde als waarin de data wordt opgeslagen?

Ik ga een bulk insert proberen, dank voor de tip.
De rows worden in willekeurige volgorde opgeslagen, geen index, wel identity column.

MoBi schreef op woensdag 26 april 2017 @ 14:26:
Het is wel slim om een primairy key op ID te plaatsen. Zodat er ook een index aan word toegevoegd.

Verbetert dat de performance? Kan ik proberen maar dan moet ik de scraper stopzetten en ben ik mogelijk huidige resultaat kwijt (200K rows)

Edit: ik kon live een index op de identity column plaatsen. Dit levert een performance winst van ongeveer 50% op Bedankt voor de suggesties. Als de scraper klaar is zo ga ik 'm een bulk insert laten doen.. hopelijk wordt dan de http-request weer de beperkende factor.

[ Voor 17% gewijzigd door hertogbram op 26-04-2017 15:07 ]

[GlowMouse]

Van 10 naar 15 rijen per seconde is niet echt een goede verbetering. Dat moet makkelijk 100-1000x sneller kunnen.

[hertogbram]

Bedankt allemaal het is opgelost.

In plaats van sequentieel de stored procedure uitvoeren, liet ik een string bouwen die alle stored procedures aanroepen achter elkaar plaatst en als batch uitvoert. -> Supersnel, 0 waiting tasks. Nu is de beperkende factor weer de snelheid van de http-requests. Fingers crossed dat ik die site niet aan het DDOSen ben.

Iedereen bedankt voor het meedenken