Access -> SQL Server. Any benefit on a single PC?

[Killer42]

Hi all.

I've been considering the use of SQL Server for a fairly large-ish database that I work with a bit. Mainly for performance reasons; it's working fine in Access, but some operations take longer than I like.

What I'd like to know is, given that this database lives on my local drive and is only used on the single PC, would I actually see any benefit by moving to SQL Server? And is there much work involved in the change? (I don't have SQL Server at present, but I gather there's a free version I can install).

[NeoPa]

I would say yes.
SQL Server is better designed to handle large databases. SQL Server works just fine on a local connection.

How much work involved? That's a hard one for me as I don't have much experience there. Ensure that you have the security tied down carefully before you start (obviously). It's more complicated in SQL Server, although more logical in a way - maybe easier even than Access security.

Most field types have a simple and direct association with the ones in Access, even if they are somewhat different.

For someone of your calibre, Killer, easy enough I would think. Certainly worth a go to see how much benefit it yields. The experience will be good for you too I would think.

[ADezii]

Hi all.

I've been considering the use of SQL Server for a fairly large-ish database that I work with a bit. Mainly for performance reasons; it's working fine in Access, but some operations take longer than I like.

What I'd like to know is, given that this database lives on my local drive and is only used on the single PC, would I actually see any benefit by moving to SQL Server? And is there much work involved in the change? (I don't have SQL Server at present, but I gather there's a free version I can install).

Hello Killer!
Since it is working fine in Access, the migration is primarily for performance reasons, it is a Single User configuration, and enhanced Security Measures do not appear to be an issue, why not make an attempt to Optimize those operations that are apparently sluggish. Obviously, I do not know the specifics of your DB, but migration to SQL Server may be comparable to killing a mosquito with a howitzer. Just for curiosity, how large is fairly large-ish, what is the Schema of your Database, and which Operations are sluggish and what exactly do they consist of? Just a few questions you may want to consider before you jump in to something as complex as SQL Server.

[Killer42]

Thanks for the input. A few responses...

• I'm inclined to agree that it will be a worthwhile exercise just for the experience.
• "Fairly large-ish" translates to around 75 million records, growing by perhaps 50,000 per day.
• Schema? No idea what you mean. This is a "quick and dirty" database I've thrown together. I find programming quite easy, but have virtually no skills on the design side, so database is probably a mess.
• I have tweaked the database (and VB code) to improve performance in a number of areas, and will probably be looking for others. In fact, one process which used to take a minute or less in VBA now takes ages in compiled VB6. The database structure and end result are the same, so there's obviously some scope for improvement there.
• The problem areas generally seem to be simply a matter of the sheer volume. Daily processing dumps 50,000 records into the main table, and has to update tons of statistical information in other tables. This is done so that I don't have to wait 3 hours each time I ask for some stats later.
  I have some other questions in relation to this, which I'll put in another thread. I basically want to try and find the fastest way, working with Access (or SQL Server) from VB6, to dump a bunch of records into a table. Or to do a bulk update. But as I said, the details can wait for their own thread.
• Security is not an issue in this case - not using any.
  Though of course it will be worth learning about the security features. Just as soon as I've had time to learn SQL Server, install and learn VB 2005 or 2008, etc etc etc. :(

[Killer42]

I guess a lot of this really boils down to one question. It's a fair assumption that I'm not doing things as efficiently as I could be in Access (and I'll be looking into this). But, is it likely that the same inefficient things would happen faster in SQL Server? :)

[ADezii]

I guess a lot of this really boils down to one question. It's a fair assumption that I'm not doing things as efficiently as I could be in Access (and I'll be looking into this). But, is it likely that the same inefficient things would happen faster in SQL Server? :)

Sheer size and volume would seem to dictate that a move to SQL Server would be an excellent option. I'd be willing to guess that you are, or currently will be, pushing the limits of Access anyway, and with a 50,000 influx of Records daily, a migration to SQL Server will be imminent. Good luck in your venture!

[Killer42]

Sheer size and volume would seem to dictate that a move to SQL Server would be an excellent option. I'd be willing to guess that you are, or currently will be, pushing the limits of Access anyway, and with a 50,000 influx of Records daily, a migration to SQL Server will be imminent. Good luck in your venture!

Heheh... way past the limits already. I had to split it ages ago into multiple back-end MDBs, with their tables linked to the front-end MDB. I'm adding a new MDB every quarter.

As far as I can recall (haven't been fiddling with it for a while), some of my code works with the linked tables, while other parts work out which MDB/table is required and work with it directly to cut out the middle man. Where I need to work with all or a bunch of them combined, I UNION them. Generally speaking, I'd prefer to be able to use one big table. Especially since Access seems to pretty much forget about indexes in certain circumstances, leaving you to wait while it scans tens of millions of unnecessary records.

This is not a critical application. I'm just thinking it might be "fun" to use as a test case to convert to SQL Server, and the slow parts are annoying me.

It'll also be a chance to refresh my memory on how it all works. Haven't been doing anything much with VB/Access for a while.

[Jim Doherty]

Heheh... way past the limits already. I had to split it ages ago into multiple back-end MDBs, with their tables linked to the front-end MDB. I'm adding a new MDB every quarter.

As far as I can recall (haven't been fiddling with it for a while), some of my code works with the linked tables, while other parts work out which MDB/table is required and work with it directly to cut out the middle man. Where I need to work with all or a bunch of them combined, I UNION them. Generally speaking, I'd prefer to be able to use one big table. Especially since Access seems to pretty much forget about indexes in certain circumstances, leaving you to wait while it scans tens of millions of unnecessary records.

This is not a critical application. I'm just thinking it might be "fun" to use as a test case to convert to SQL Server, and the slow parts are annoying me.

It'll also be a chance to refresh my memory on how it all works. Haven't been doing anything much with VB/Access for a while.

The fun ones are always the best! you can automate a lot of what you might need to do too... like loading (bcp...bulk insert) the data scheduling on a 'job', rebuilding indexes following load, truncating (super swift) view performance issues via execution plans, tracing, profiling. Throwing the data around more powerfully in SQL more join capabilities, temp tables, table variables, stored (pre compiled) procedures, user defined functions.

Making it efficient and fast though is the fun part. Speed tip: look at 'lock hints' in the context of where/how you can/maybe/should use them

SELECT * FROM tblMyTable WITH(NO LOCK)

overrides locking on the table during the select (speed gain as you might imagine - that you might want to benchmark yourself in the playtime with your huge datasets)

Regards

Jim :)

[ADezii]

Heheh... way past the limits already. I had to split it ages ago into multiple back-end MDBs, with their tables linked to the front-end MDB. I'm adding a new MDB every quarter.

As far as I can recall (haven't been fiddling with it for a while), some of my code works with the linked tables, while other parts work out which MDB/table is required and work with it directly to cut out the middle man. Where I need to work with all or a bunch of them combined, I UNION them. Generally speaking, I'd prefer to be able to use one big table. Especially since Access seems to pretty much forget about indexes in certain circumstances, leaving you to wait while it scans tens of millions of unnecessary records.

This is not a critical application. I'm just thinking it might be "fun" to use as a test case to convert to SQL Server, and the slow parts are annoying me.

It'll also be a chance to refresh my memory on how it all works. Haven't been doing anything much with VB/Access for a while.

The more you tell me, the more I'm convinced that the move is definately warranted. (LOL).

[ADezii]

The fun ones are always the best! you can automate a lot of what you might need to do too... like loading (bcp...bulk insert) the data scheduling on a 'job', rebuilding indexes following load, truncating (super swift) view performance issues via execution plans, tracing, profiling. Throwing the data around more powerfully in SQL more join capabilities, temp tables, table variables, stored (pre compiled) procedures, user defined functions.

Making it efficient and fast though is the fun part. Speed tip: look at 'lock hints' in the context of where/how you can/maybe/should use them

SELECT * FROM tblMyTable WITH(NO LOCK)

overrides locking on the table during the select (speed gain as you might imagine - that you might want to benchmark yourself in the playtime with your huge datasets)

Regards

Jim :)

Thanks for joining in Jim, if you didn't I was gonna request that you do so. (LOL). You definately seem to have a 'pulse' on SQL Server.

[jaxjagfan]

You may want to take a look at SQL Server Express as it is free to start with. It has most of the same functionality as SQL Server. When going to SQL Server it has several cost structures but Small Business Edition may give ya the best bang for the buck.

You will want to look at system requirements as SQL Server is designed to run on a "database server" vice "file server". You may have to upgrade the hardware before installing.

If you work in a corporate environment, there may be SQL Servers that you can use to build your database and table structure on. There may be SQL Server DBA's available to give you a hand.

I use SQL Server DTS (Data Transformation Services) to move and transform data from and to multiple sources every day.

SQL Server has built-in feature to import your current structure from an Access database.

You may want to take a look at your table structures. It sounds like you are creating summary tables to make reporting quicker. If using Access MakeTable queries with large datasets try switching to Append queries - use a delete query to delete all data (its the same as truncate) and then append the data. This also gives you more control over your data types.

You are "crunching and compiling" the data - are there other users of the data (IE - report viewers)? If so then you will want the SQL Server set up on a network server. And if so you will have to consider licensing (Per CPU, Per Connection, Per User, etc).

FYI: Oracle has a free version as well if your corporate environment is Oracle.

[Jim Doherty]

Thanks for joining in Jim, if you didn't I was gonna request that you do so. (LOL). You definately seem to have a 'pulse' on SQL Server.

Thanks Dez....which version Killer goes for is a matter for him I have no idea what facilities are open to him 'corporately as Jax points too. If his company has enterprise wide licensing then he could hook into an existing server and use client tools. alternatively if standalone is the only way to go then one is down to msde or SQL Express which are cut back versions tools and management wise, but none the less powerful on the engine side. Heres a rather helpful blog that concisely describes some options

http://arcanecode.wordpress.com/2007...ing-a-version/

Jim :)

[Killer42]

Thanks for all the info. I'll definitely bookmark this for reference. It may be a week or two before I have a chance to get started.

I will say now though, "if it ain't free, it won't happen".

Oh, and automating the load probably won't happen. It's already "automated" in the sense that a VB program generates the data and loads it. Producing the info is quite a complex task; I'm scanning log files and picking out details such as when things started and ended. Some of them even span the end of a file and the start of the next one, which I don't get until the next day.