Hot laptop slowing Excel?

IronWolf

Hi all

Ive got an Excel file that uses VBA to grab data from a SQL database and save it off into individual files. Im running this on a 5 year old laptop, intel i5 processor, 500gb SSD and 8Gb ram.

When I first turn on my laptop it takes roughly 1s per file, but after an hour or two this slows down to about 5s. This is increasing the run time of the macro considerably from about 3 hours to over 15 hours.

My laptop is capable of doing the job but for some reason is slowing after a while. The spreadsheet itself is not changing size during the macro.

Could this be due to the heat and the processor getting too hot? When I first boot up the laptop it is always so much faster, but if I try after its been on a while then its considerably slower.

I dont really want to have to pay out for a new desktop with good processor cooling, is it possible to buy a laptop cooler that will be good enough to keep it fast? Most just seem to be a couple of fans which I dont think will be enough given the high processing its needing to do.

windup

memory leak, check task manager stats, or inefficient code possibly, virus scanner?

how many files, where is the source data, how big is each file, how big is the pagefile, what data and why

IronWolf

Its using about 1.5gb ram, sql server is using 4gb. Only 80% used though so has spare. The files are only about 50kb each. The code loops, so there should be no difference between the time taken for the first task and the last task.

The sql database is stored locally, and the query only takes 0.2s when first turned on. I need to split it down into individual files as well as rework the format with pivot tables, but the code loops so speed shouldnt change.

GunJack

sounds like you may need to take the fan & heatsink out, give them a really good cleanout (separate the fan & heatsink for this, there's probably a furry slug sitting between the two. Might need to renew the heatsink compound (sometimes called thermal paste) between heatsink and CPU/GPU when rebuilding.

Someone may mention blowing into the vents with a can of compressed air - DON'T, that just forces any built-up crap back into the lappy's innards. Use something like SIW to monitor your core temperatures:-
Download SIW Standalone Version PCWorld (PCW the US magazine, not our much-hated UK shop )

If it's still doing it after that lot, then maybe look deeper... how much stuff do you have:-
1. running at boot and
2. set to automatically update?

these would be the two areas I'd look at first software-wise..

IronWolf

Ahha I found the problem, Id left all the connections to the database open so as it looped through it was accumulating thousands of active connections. Once i closed them all its back to under 1s now.

I cleaned out the fan like you suggested GunJack after a quick youtube tutorial, there was a load of dust clogging it up so hopefully that will help too. Thx

IronWolf

Ah I spoke too soon. That sped it up at first, now after 6,000 interations its back taking 5s or so. No idea why :S

GunJack

IronWolf wrote: »

Ah I spoke too soon. That sped it up at first, now after 6,000 interations its back taking 5s or so. No idea why :S

it has to be the interaction between excel and db...can you add any script to close each connection once the data's been transferred on a transaction-by-transaction basis?

goldieds

A little tip i found to help with cooling was using beer caps to raise the back of my laptop and allow some air circulation underneath.

IronWolf

GunJack wrote: »

it has to be the interaction between excel and db...can you add any script to close each connection once the data's been transferred on a transaction-by-transaction basis?

Yes I have set it to close all connections at the start of each loop, but still seems to be slowing

Geodark

IronWolf wrote: »

Yes I have set it to close all connections at the start of each loop, but still seems to be slowing

Are you disposing of the connections as well as closing them? Any chance of sticking your code up here - might be someone on here who could help. I more .NET side of coding, but have dabbled in VBA in the past.

IronWolf

Geodark wrote: »

Are you disposing of the connections as well as closing them? Any chance of sticking your code up here - might be someone on here who could help. I more .NET side of coding, but have dabbled in VBA in the past.

Not sure, just using the delete function. This is the VBA I quickly grabbed from the web to do it


Dim xConnect As Object
On Error Resume Next
For Each xConnect In ActiveWorkbook.Connections
If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete
Next xConnect