Computer resources (how to access them)

[Demonperformer]

Is there any way to force my computer to use more of its resources?

The attached screenprint was taken of my cpu/memory usage while running an intensive query (lots of records to sort through) in Access. As such, these queries take quite a long time, but I can't help thinking that this is not helped by having 90-95% of my cpu sitting there doing nothing [Total usage bottom left corner, Access usage highlighted].

I have tried adjusting the priority of the task as high as it will go. It totally rejects "Real time 24" and, although it will accept "High 13" it has a habit of resetting itself to "Background 4" for no apparent reason.

I think I could save myself a lot of "sitting around waiting" time if I could get the machine to put some of the 93% of its resources it isn't using into processing the query I have actually requested. I guess it is called winDOZE for a reason ...

Thanks.

[AngryDonkey]

Although there usually is some clever OS optimizing going on in the background, it's really up to the application (MS Access) to make full use of what's available and it's not usually possible to 'force' resources onto a specific application. The fact that CPU usage is low tells us that the bottleneck that's slowing down your processing is not related to processor performance. Other reasons for slow performance are usually related to 'not enough memory' or hitting the I/O limits of your system (i.e. the application becomes 'memory bound' or 'I/O bound').

In this case however I think it's most likely directly related to your Access query and how the database engine has to process it. The only way to improve speed would be to change the query to run more efficiently (but that's a science in itself). I've luckily not worked with Access for a while but I remember that sub-reports were a real performance killer... An even better solution would be to move your data to a real database like SQL Server. The express version is free and it's actually surprisingly easy to migrate..

[Demonperformer]

Thanks for that. I know my table has 2-3 million records (so it's never going to be mega-quick), but everything seems to take 2-3 min - even accessing the saved query (that's before I amend the search value, which then takes another 2-3 min). This may not seem like a massive amount of time, but run 10 queries and that is an hour just sitting and waiting. It has just occurred to me (while writing this) that it might even be faster to rewrite the query rather than trying to reuse a saved one ...

I will look into SQL Server express. This project is massive enough that even saving 5% on total time it would be worth spending time now learning a bit about a new program and doing the migration (or even, as I've only put in about a week, starting again from scratch).

Thanks.

[JimothyC]

I would second the suggestion of moving over to SQL Server express. Although you might want to check your data has appropriate primary and foreign keys and you should also look into indexing. Adding these in will help Access deal with the large number of records more efficiently. 

[LightBucket]

I would doubt that sorting through those record is really a CPU problem, more like RAM and hard drive, that’s where you would need to improve...

[PaulM]

2/3 million rows isn't a lot of data.

Have you indexed the table and is the query using the indexes, are you querying just one table or several, if several again make sure you setup appropriate indexes on the foreign keys

Be aware that SQL Express has limitations so make sure these are ok for you if you are planning to migrate the data over

[Rob]

I assume you are running 64 bit?. How much RAM are you running?. What CPU cores/hyperthreads do you have. When I'm using my work machine with SQL Server Management Studio v17.9 I crunch a lot of MIS data.. My i5 4 core performs  well. I do have 16Gb Memory mind you

Rob

[Synchronicity]

From your screenshot Access isn't using much RAM at all. That means lots of delays while it reads from and writes to disk.

It might be worth searching to see if there's a way to allocate it more memory. What version of Access is it? 64bit should be faster if you can use it.

Michael

 

[Demonperformer]

Thanks for all the input.

It's Access 2000 running on a 64-bit machine with 2GB of RAM (yes, I am aware that this is not a lot, but adding more is not a route I will be travelling atm).

I've installed a copy of SQL Server 2014 (the latest version that will run on Win7 according to the download site) and spent the morning going through a couple of online tutorials. I'm not around for most of the rest of the day, but will have a play with some live data tomorrow morning and see how it behaves. Worst case scenario: I go back to using Access and have only lost a couple of mornings' crunching.

Thanks.

[Rob]

DP, As you have identified the 2GB will hold you back big time!. If the motherboard only has 2 x Dimm slots for memory. I dare say each bay will have a max 2/4 Gb per bay. I would look to max out each bay and upgrade. I know you not keen to go down this road, but worth it

I assume its likely to be DDR2, so cost should not be too great.

You will notice a huge performance boost on everything the PC/laptop will do from there.

Best Rob

[stash_old]

You are only as fast as your slowest component - Its not Windows problem - that's why IT D/B Pro's are paid extra as even with optimum hardware and software if the query is a "bummer" then it wont make a difference in hell.  A bit like saying go to the North Pole starting in Iceland and the person goes via the South Pole - still get there but a long journey.

Read this (just the first item from a Google search      https://www.ibm.com/support/knowledgecenter/en/SSZLC2_7.0.0/com.ibm.commerce.developer.soa.doc/refs/rsdperformanceworkspaces.htm

What the other threads have advised you are spot on!

[IanL]

Access doesn't support multithreading even in the latest version, never mind a version from 18 years ago. Regardless of any other bottlenecks, it will only use one half of one core at most; assuming a four core hyperthreading CPU, maximum utilisation reported by Windows could never go above 12.5%.

[Demonperformer]

Well, the cure was worse than the disease.
Firstly, SQL totally failed to import an ascii file properly - When I set up the columns, it showed it perfectly, but when I hit preview before importing, it had screwed up the columns so the data was gibberish.
Secondly, it added ten minutes to my computer's startup time - even though I was not even running SQL, it used literally hundreds of thousands of Kb and tied up 40-50% of the CPU for ten minutes during startup.

Those two things together led me to the decision to dump it, which is when I went into control panel and discovered that the installation process had added no less than 20 programs to my system. TWENTY!!

Well, I ran "uninstall" on all the programs that said SQL in the title, which left five, all installed yesterday. I tried doing an uninstall on one of those and got the message that I was going to mess up my system if I uninstalled it. The only thing I installed yesterday was SQL so they must have been installed as part of that installation. Why would I mess up my system removing something that was installed by SQL when I no longer have SQL??

It seems to me I have three options.
(1) Just accept the waste of resources that these programs will inflicting on my system until the end of time [not fond of that idea].
(2) Just delete them anyway and hope microsoft are bluffing [not particularly keen on that idea either].
(3) Perform a system roll back [not sure when my last rollback point was created and I've had problems with them before with the system telling me I'm using a pirated version of Windoze (which I'm not)].

So can anyone advise which (if any) of these programs will genuinely screw up my system if I do uninstall them (although I can't see why they should as they were only installed as part of SQL)?
* Microsoft Visual Studio 2010 Shell (Isolated) - ENU
* Microsoft.NET Framework 4 Multi-Targeting Pack
* Visual Studio 2010 Prerequisites - English
* Microsoft Visual C++ 2010 x86 Runtime - 10.0.40219
* Microsoft Help Viewer 1.1

Thanks.

[cjdawson]

You should be ok to uninstall all of the above programs.    If you do screw something up, it won't be a killer, and just be aware of what you removed so that you can reinstall if needed.

 

As for the bigger problem of the performance.

I think you would be better using a proper RDBMS at least one that isn't MS Access.  it's not designed to run parallel queries.    So rather than looking at the total CPU, take a look at just one core and use that to figure out if you have any resources left.

on top of that check that your query is optimised in terms of indexing and lookup efficiency.   (This is an art unto itself)

I've not been following the thread, so don't know what went wrong with SQL Express.    For personal use, you can get SQL Server developer edition for free - it's for creating and testing software, which it sounds like you are doing to me.

you can use SQL Management studio to connect to your Access database and copy all the data across to SQL Server without needing to go via export files.

[PaulM]

I'd do a full backup and then uninstall what you installed with SQL, the above should be ok to uninstall

Another option is;

If SQL is still installed open services under control panel and for the SQL services set to automatic change the startup type to manual so they don't startup on bootup and see what that looks like in terms of bookup time and resource usage, but this still leaves a lot of installed software you aren't going to use

[Dr_Ju_ju]

To be honest, I personally wouldn't try & run SQL anything on Win7.... As for SQL developer, that will install even more stuff you won't need. 

If you can, run a virtual Windows 2013 server & install the SQL developer onto that. At least if anything goes wrong you can just delete the VM, with no impact on the local host.

[Demonperformer]

Thanks to everyone for the feedback. It's good to know that, if/when I uninstall these progs, I shouldn't encounter BSOD and that it will be recoverable if I run into problems!

As to the original speed problem, I have found the solution ... indices. I was aware that these should make things faster, but the problem was I could not install a "primary key" due to data duplicates and wasn't sure how to set any other index in Access. Google was my friend! Now queries are running (and opening) at a MUCH faster speed. And CPU usage has risen to about 50% during running. My guess would be that the system was spending most of the time finding the records rather than processing them and that finding was not CPU-intensive but processing is ... or maybe that's complete rubbish! One way of the other, it is now performing at what I consider to be a reasonable speed, so I am happy with the result.

Thanks.

[IanL]

1 hour ago, Demonperformer said:

Thanks to everyone for the feedback. It's good to know that, if/when I uninstall these progs, I shouldn't encounter BSOD and that it will be recoverable if I run into problems!

As to the original speed problem, I have found the solution ... indices. I was aware that these should make things faster, but the problem was I could not install a "primary key" due to data duplicates and wasn't sure how to set any other index in Access. Google was my friend! Now queries are running (and opening) at a MUCH faster speed. And CPU usage has risen to about 50% during running. My guess would be that the system was spending most of the time finding the records rather than processing them and that finding was not CPU-intensive but processing is ... or maybe that's complete rubbish! One way of the other, it is now performing at what I consider to be a reasonable speed, so I am happy with the result.

Thanks.

Glad you sorted it, but yeah, tables without primary keys are the DBA equivalent of being the apprentice sent out to find a left-handed spanner. If you've got duplicates in a field, by definition it cannot be a primary key. It's a dreadful hack and I should probably hand in my Comp Sci degree for telling you this, but in the real world (vs. Codd-land) in that situation just add a field at the start of the table with an integer type large enough to encompass the maximum number of rows you expect and make that the primary key. It's not ideal but it will avoid the performance issues you hit here.

You should also add indexes to any other columns that are used intensively in queries - I can't remember but I think Access had some optimiser tools that suggested what you needed based on analysing the queries you built.

[Demonperformer]

16 minutes ago, IanL said:

add a field at the start of the table with an integer type large enough to encompass the maximum number of rows you expect and make that the primary key. It's not ideal but it will avoid the performance issues you hit here.

I understand the principle, but am unsure how it would help. Surely to speed queries it needs to be indexed on that field. For example, the tyc2 database has a field Bmag, which has a value to 3dp, so there are considerably fewer options than records. Indexing a completely different (independent) field will surely not help speed a query when the criteria are based on Bmag, will it?

[han59]

It is an interesting experiment to try to use MS-Access for accessing millions of stars.

I don't know what your trying to achieve, but by mapping, you should divide the celestial sky in area's and write a file for each section sorted in magnitude from bright to faint. Then you can fill a map in maybe a second or so even if you have 200 million stars on your hard disk. For all other queries you have to go through the whole database which will be time consuming. Dividing the celestial sphere can be also a challenge and worth a long study. 

To fill a star database, I found it is easier to extract them in CSV format from the Vizier server using a query rather then peeking in the original format. At least that is what I did for Gaia up to magnitude 18 and in steps of 0.1 magnitude.

Han

 

 

 

[Demonperformer]

I'm not working on the hundreds of millions at the moment. Basically I am working on all the stars that appear in any of the three Tycho2 tables and for TYC data I used an ascii file downloaded from vizier and imported. Maybe csv would have been better, but ascii works pretty well on access - and i have done this a lot so know what I am doing. The problem with ucac4 is that, as far as I can see, I cannot select a range of ucac4 stars using the tyc2 number as a selection criterion. If I could use something like "4527*" to give me all the stars in that zone, that would dramatically increase the efficiency of the extraction process for me (or, even better, "is not null"). In fact, I have even failed in my attempts to select a ucac4 zone from vizier ("900-*", etc) If you are aware of anything like that, I would be most grateful for any pointers. Without that, having set up a table with Tycho data + ucac4 number (extracted from the u4xtycho file), the best option I can see is to download each of the ftp files, create a table from that and then perform a query to update my table with fields from that. Maybe not the fastest way, but I am open to suggestions.

As regards dividing the sky, I did consider that. 4 declination zones (90>30, 30>00, 00>-30, -30>-90) gives equal areas of sky and splitting the last one (-30>-60, -60>-90) gives vaguely equal numbers of tycho stars in each zone (very vaguely!). The problem is then having to set up a more complicated query to extract the stars I want from the entire database. While queries are running on the entire thing quite quickly (now I've introduced an index), I think I will be better off with a single table.

Thanks.

[wornish]

Can you explain what you are trying to achieve when you have the stars in your access database ?  What will that data be used for?

[IanL]

14 hours ago, Demonperformer said:

I understand the principle, but am unsure how it would help. Surely to speed queries it needs to be indexed on that field. For example, the tyc2 database has a field Bmag, which has a value to 3dp, so there are considerably fewer options than records. Indexing a completely different (independent) field will surely not help speed a query when the criteria are based on Bmag, will it?

Not necessarily, but I had assumed you were trying to join two or more tables in your query, and that is a handy way to create a primary key for tables that aren't properly normalised (simplistically normalised means every table has a primary key and all other columns in the table are fully dependent on it. E.g. Naïvely you have a table with employee names and addresses in it, composite primary key of forenames and surname. To normalise you first realise that more than one employee may live at the same address, so split it in to employee and address tables as the address columns are not dependent on the employee. Address table has composite primary key of house name/number and postcode (should be unique), which you also add as a foreign key to the employee table so you can join them in queries. Next you realise that some families work for you and like to name their children after themselves so your employee primary key is invalid. Create a unique payroll number for each employee and use that instead).

In this case you have a query on a single table for this data, and the primary key should be the catalogue number; if these are duplicated then you need to dedupe the data first as clearly some errors exist.  Adding indexes to other columns used as criteria in queries is the next step.  There is an overhead for creating, maintaining snd using indexes so you shouldn't add them to every column just in case, but less of a concern here the contents of the table are static so it's a one-time process rather than an ongoing one for a table with frequent updates, inserts and deletions,. Your main issue is whether all the indexes can be held in memory rather than on disk.

For example, look at the minimum magnitude in your data set. Say it's 12.999, instead of crunching through 3 million records, an index could reduce it to 230 records (3,000,000 / 12,999). That's assuming a perfectly even distribution of magnitudes across the dataset and looking to match one value.

Your mileage will vary and it's not quite as simple in terms of resources saved due to many other overheads but the gains are huge. Optimising database and query performance is a huge subject. Professional DBAs make very lucrative careers out of their experise for a reason.

[Demonperformer]

2 hours ago, wornish said:

Can you explain what you are trying to achieve when you have the stars in your access database ?  What will that data be used for?

Well. it started as a simple cross-reference project. Most of my stars in my version of CdC are displayed as UCAC4 numbers, and I wanted to be able to identify them as something else. Most stars in SIMBAD do not list the ucac4 number. This led me to the Access cross-reference project. But, like many of my projects, it has grown as it has progressed!

I am currently using the data to resolve the 887 (so far!) errors I have identified in the UCAC4 catalogue. I find it hard to believe that no one has yet found these, which means that either no one has bothered to report them to USNO (which I will do when I have finished compiling them) or (perhaps more worrying) USNO have not been bothered to correct the information that they make available through vizier (about which I can do nothing). Either way, once complete, I will make my solutions available to SGL members (probably as an ascii (.txt) file which can then be imported into members' favourite programs should they wish).

It keeps me off the streets!

[han59]

35 minutes ago, Demonperformer said:

I am currently using the data to resolve the 887 (so far!) errors I have identified in the UCAC4 catalogue. I find it hard to believe that no one has yet found these

 

The UCAC4 picked up fake star streaks, the UCAC3 didn't have. The USNO didn't show interest in cleaning the UCAC4.  The UCAC5 was a release where they improved the star positions using Gaia DR1 but unfortunately they choose again a different format so I didn't bother using the UCAC5. All this is now superseded by Gaia dr2.  All other star catalogues are now in principle obsolete. Stars don't have a designation anymore. You could identify stars using IAU style coordinate-based designation.

If somebody is interested, I have also compiled a list of the missing brights stars in Gaia dr2. I checked up to magnitude 8 against Tycho2 but it was difficult. 

Han