#StackBounty: #c# #.net #web-crawler #sql-server-express #pc What causes the slow down in crawling from a laptop program?

Bounty: 50

I have a project that I have managed to save from a server that was outsourced and managed to get most of it working on one of the laptops I have at home. It has OS Win 8.1r, VS 2017, SQL Server Express 2017, and I wrote the DLL I use in my app in C# .NET version 4.6.1.

I am currently every night at midnight manually running some stored procs that fill some stat tables due to no MS Agent existing in SQL Server Express, then runs an index maintenance proc that either REBUILDS or DEFRAGS the indexes plus rebuilds stats before I manually restart the BOT from a command prompt at just after midnight.

However I have noticed if I leave the laptop on for 3-5 days the time each run takes to get (on average), 40 races and 5-20 runners per race going through proxies gets slower and slower. I just rebooted now as last night it took from 1am to 11am to crawl, scan with regex to get info and save to the DB races and runners.

However if I look at the CreateDate times I store on every new race I can see a pattern..

Yesterday took 10 hours to do 40 races and runners,
Saturday took 4 hours to do 50 races and runners
Friday 3 hours 49 races
Thursday 5 hours 42 races
Wednesday 5 hours 32 races
Tuesday 1 hour 36 races

Obviously over time more and more races & runners are stored in the DB, so retrieval times from indexes, storage etc gets longer but after a reboot it is quick Harry, I just restarted it tonight, rebuild the indexes then let it go and its already done 7 races in 7 minutes.

Obviously I haven’t got a server to put this on, the last attempt resulted in an old boss putting it on a French server that doesn’t allow access to online betting sites and my BOT uses the Betfair API.

It runs on my laptop ok apart from
-the speed of getting all races and runners into the DB lengthens over time. The longer I leave it on the longer it takes, despite all the clean up operations I do nightly (Delete old log messages, locks, and rebuild stat tables before a reindex/defrag job).

-For some reason the logfile I output debug messages to for after the fact debugging e.g I look for SQL Errors, Connection Errors, Proxy Issues, RegEx errors and I output this through the console app I am using the DLL with at the moment to a logfile in C:programdatamyprojlogfile.txt – it has permissions as it writes to the file however once the job is over if I try and open it up in my standard Editor – Editplus, it just opens up a blank document. If I open it in Notepad first I can see all the debug then I can copy n paste it to a blank Editplus document.

It never did this before on my Work PC, Permissions are okay, the file is being written to and I don’t get any "permission denied" or other I/O errors when opening the logfile up, it’s just empty if I don’t open it in Notepad.

So I’d like to know what sort of actions are happening to slow this job down over time that a reboot fixes. I know the old saying we used to get from our techies when we had a bug or issue with our PCs at work "have you tried turning it on and off again" – which does for some reason fix so many issues.

I’d just like to know what sort of issues could be happening to slow it down over days that I could maybe automate a clean up job so it doesn’t happen. I used to run the exact same code on my Work PC connected remotely to a server every day for months before forced to do reboots due to Windows Updates. So it never used to do it with my bad practice at work of leaving my PC on all the time.

Is the disk getting fragmented – and why wouldn’t it require a disk defrag to solve it after rebooting.
The registry? What could get worse and worse over time that a reboot fixes.
Or is it the fact I am using MS SQL Express 2017 and there is some I/O issue with the files it writes to that slows down over time.

I would just like to be able to leave my laptop on with this BOT running at specific times during the day and not worry about it taking 11 hours to complete the first import job.

It is now 37 mins past, been running for 20 mins and it has imported 15 races and runners, about a quarter of the total, so should be finished in about an hours time tonight, and I have JUST re-started my laptop, nothing else, and it has speeded it up from 10 hours yesterday night?

What could be slowing it down lover time, and can I fix it at all?

Get this bounty!!!