Darryl Collins
darryl at whittleconsulting.com.au
Tue Nov 15 20:38:09 CST 2011
Yep, That is the one I am using - 10 GB is the limit. But the 86 million rows of data are kissing the limit. 8 Access DB's pushed into a single SQL Server table. There are not that many columns of data (maybe 10 columns) in each table. Well, the one I am using to be precise is SQL Server Denali CTP3 - Express version of course. To give you an approximate idea. The 8 MS Access databases - each only contain a single table of data, no code, and a single query to ensure each table is exported the same are sized in KB (when compacted) as 1,080,608 926,160 1,343,040 998,736 1,452,932 1,300,500 1,291,344 1,287,232 ------------------ 9,680,552 Total KB ------------------ So pretty darn squeezy when pushed into a 10 GB limit :) Given that I need to add one column via the query to each table on upload you can start to see my problem. Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: Wednesday, 16 November 2011 1:19 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] New SQL Server license scheme is RADICALLLY more expensive Darryl - I don't remember you mentioning which version of SQL Express you're using. SQL Express 2008 R2 has a 10 Gb limit. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Tuesday, November 15, 2011 4:38 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] New SQL Server license scheme is RADICALLLY more expensive Hi Shamil, Initially it is just the sheer volume of records. I am working with a 'block model' which is used to map open pit mines and there are many millions of records to process (about 86 million in this case). Each block has a unique X Y Z co-ord that places it in space, these 3 co-ords are basically the key for each record as should be unique. However in this case the client said there should be 83M in the model, but when we counted all the combined data sources we had 86 million, so we suspected there were duplicates. Loading 83 million records pushed SQL Express right to the edge of the size restriction limit, it left little room to do any actual processing work. The original data came to us in 8 separate MS Access databases, each one having around 8 million rows of data. The free version of MySQL doesn't have the same size limits as SQL Server Express, and with the Free GUI's (I am currently testing Toad and MySQL Workbench) it has been a fairly simple process to get started. Today I am hoping to get into the more complicated things - it is very early days, but hey, even If you are using the full grunt paid system on a stand alone server, it is a fraction of the cost of SQL Server these days - I would seriously consider it. I love the full version of SQL server, but I can say that, as I have never PAID for it. It has always been a company expense - someone else's proble. But even so, with this new plan it is looking bloody pricey even for those with serious pockets. Now. MySQL - The Process so far: Looked at the manual: << http://dev.mysql.com/doc/refman/5.5/en/index.html>> Download the Free community edition: Easy << http://dev.mysql.com/downloads/mysql/>> Install: Fairly easy Attempt to do 'stuff' via the text window: Welcome back to 1998 - this was kind of freaky, was expecting something a lot more, ummm, polished. << http://dev.mysql.com/doc/refman/5.5/en/database-use.html>> Decided there had to be a better way: Used Google and downloaded a GUI: Much better - phew! Relief for sure. << http://dev.mysql.com/downloads/workbench/>> Or << http://www.toadworld.com/Freeware/ToadforMySQLFreeware/tabid/561/Default.aspx>> Download ODBC connector for MySQL: Painless << http://dev.mysql.com/downloads/connector/odbc/3.51.html>> Create MySQL Database: Easy - although I want to look at more 'options' on this Push or Pull data from MS Access to MySQL - Fairly easy - need to poke around a bit on the parameters. Check data types << http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html>> Today I need to look at some serious data mashing. Will keep you posted. Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov Shamil Sent: Tuesday, 15 November 2011 6:41 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] New SQL Server license scheme is RADICALLLY more expensive Hi Darryl -- I wonder, what raw (server side views, SPs, UDFs, triggers, ...) data processing/manipulation are you doing with your large dataset, which doesn't fit MS SQL Express? What data processing/manipulation is done on your client side - just conceptual view of it?... Thank you. -- Shamil 15 ?????? 2011, 06:22 ?? Darryl Collins <darryl at whittleconsulting.com.au>: > Well, > > Been an interesting couple of days. Have a very large dataset, far too large for Access so I have been playing with it in SQL Server Express, but been hitting issues with that as well - the data just fits (just) but it leaves almost no space do perform any actions on it. So now I am trying MySQL. Been a bit of a learning curve this morning, for example, it was awful until I found a decent GUI (that needs to be downloaded separately). But so far so good, I am liking it. Got it hooked into MS Access for import / export and the rest of it seems rather doable as well. Fairly intuitive so far, which I like. > > Will be a change as I have used SQL server for a few years now, but Express is getting too limiting and there is no way we can justify paying for a dedicated server. > > See how this turns out - early days yet so I am not getting too pleased with things, but an encouraging start none the less. > > Cheers > Darryl. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Salakhetdinov Shamil > Sent: Tuesday, 15 November 2011 7:52 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] New SQL Server license scheme is RADICALLLY > more expensive > > Thank you, Arthur, > > I thought you have used more powerful hardware - but if it works well for you, no problem. > I've even a bit "weaker" system than you do (and just 3GB RAM), and I can have several VS2010 sessions, as well MS SQL, MS Word, Excel and Access etc. running simultaneously. They work rather smoothly but it's becoming as you say "confusing" to have all that stuff opened on one physical box, so I'm considering upgrading my hardware to something more modern (but not as mighty as JWC has) to run development for different customers in different VMs... > > -- Shamil > > 13 ?????? 2011, 22:21 ?? Arthur Fuller <fuller.artful at gmail.com>: > > My hardware is pretty modest, Shamil, especially when compared with JWC's. > > On the OS side, I run Windows 7 Ultimate. On the hardware side, the > > workstation is a gracefully aging HP with 4GB of RAM and a few hard > > disks and a dual-layer DVD burner. Oh yeah, there's also a USB hard > > disk with a mere 250 GB. That's about it. Oh, I forgot the > > flash-memory sticks: a pair of 16 GB sticks. I'm considering > > mounting the new version of Solaris on one of them. > > > > I should add that I don't run everything all at once; what's the > > point of running Ubuntu and Mint simultaneously? The one problem > > that I have with my setup is that I don't have a Windows XP VM. > > Within the Ubuntu VM and also the base Windows 7 boot, I run Chrome > > as my browser, but each instance opens pages related to the > > particular OS. I find this approach leads to less confusion, and > > since I'm going to become 64 in two days, I have to consider confusion as a major topic LOL. > > > > A. > > > > On Sun, Nov 13, 2011 at 12:49 PM, Salakhetdinov Shamil <mcp2004 at mail.ru>wrote: > > > > > Hi Arthur -- > > > > > > > At the moment, I'm running Windows 7 as the native boot, and > > > > then running Oracle VirtualBox inside that, and inside that, I > > > > run various VMs, including Ubtuntu, Mint and Windows 8 Preview CTP 3. > > > > Everything works just ducky. > > > > ...for now this arrangement suits me to a tee. > > > > > > May I know what is your hardware to run all that stuff? > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com