Open Source Database Recomendation

You can try Firebird. This is fully featured yet light

http://www.firebirdsql.org/

SQLite is a great single file backend db. Both MySQL and PostgreSQL are full db server installs. If you have a db system (SQL Server, Oracle, MySQL, PostgreSQL, etc.) already set up, you will get better performance and more features. However, it doesn't sound as if that backend infrastructure is currently available and would require significantly more effort for you than SQLite at this point.

Lee

Thanks...

My specific problem is that I manage a large precalculated table (a cube i guess) in Access that now has >800k rows and 42 columns.

My users manage the data in Excel Pivot tables, but Pivot Tables load the ENTIRE Access table into memory when accessing the data, so my pivot tables are now > 1.6GB in memory and are crashing peoples machines.

So what I need is an application that delivers data in a way that is more digestible by Excel, for ex, Sql loads just what you ask for in what i think are pages.

Does that sound like a good candidate process for Firebird?

thanks again

lee555J5:
Actually, backend infrastructure is not a problem. I can implement just about any solution that'll work. The problem is being certain enough of my choice to then push for IT to actually help me.

I run a bunch of tables every 2 hrs out to a network location from PeopleSoft (Oracle back-end), and then use a VBA routine running off of the crappy Timer Function in Access (cant use Windows Scheduler on my Desktop) to link the tables to Access for processing.

When I suggested Sql Server, they said that it requires regular maintenance a Sql Server management team, and it would probably be overkill given the cost that would be charged to my department. I suppose I need to get an actual cost and make a decision.

BUT, I want full control over the data. It won't help me if I need to go through a QA process every time I want to rename a field.

Then they suggested a virtual machine to get the processing from the updates (every 2 hrs) off of my desktop...and I can use wndows scheduler to kick it off, but the windows server team has had the ticket for 2 months and I feel like I'm going to have to make a major big deal out of it to get some attention. I was thinking with a VM I'd have admin rights and could install and run any application i want.

So, I can ask my dept head to go to the head of IT and ask nicely that this gets done, but I won't ask for that kind of help unless I know exactly what I'm talking about.

Hence the question...

btw, I'm an accountant and Excel guru, who loves VBA and data problems, but not having a formal database or programming background, I'm struggling a bit with this decision.

Thanks again.

What if Excel loads the data in the exact same way (The entire table) with your DB alternative?
This seems to be an Excel issues, not one with the RDMS

"1.6GB in memory and are crashing peoples machines."
How much memory is installed on their machines?
I'd hate to go through all the trouble of switching the RDBS if all the users needed was one more stick of Ram...
;-)
(The cheapest Dell Inspiron comes with 4GB installed)

JeffCoachman

Mysql or mssql express can both easily be installed without much knowledge on a workstation.
For mssql By default only localhost can connec so it's not even a security risk

Thank you all.

Aaron: thanks for the tip. I'll check these out.

Boaq: We all have 4gb Ram. But when I've had to connect to Sybase or SqlServer from Excel, i found they delivered the data in pages. I suppose because they're fast enough to retrieve more data when needed. So, I'm thinking that probably all the more robust RBMS do this.

"MS Access databases that have outgrown the application."
Sorry, I'm not clear on what the actual limitation with Access in your situation?

"Access that now has >800k rows and 42 columns."
But how big is the MDB ?

"I run a bunch of tables every 2 hrs out to a network location "
What does that mean ? 'run' ?

mx

- Firebird
- PostgreSQL

Looking for a database? This handy database feature comparison matrix and glossary let you compare and contrast the supported features of four popular open source databases: Apache Derby 10.1, MySQL 5.0, PostgreSQL 8.1, and One$DB 4.0.  

http://www.devx.com/dbzone/Article/29480

Hope it is of help?

Oh my GOD!! You Guys are Awesome!! Reading through this thread makes me proud of what EE has built. Thank you for your feedback!

To answer your questions:

DatabaseMX:
Q: "Sorry, I'm not clear on what the actual limitation with Access in your situation?"
A: 1. My limitation is that Excel Pivot Tables (the front end) load the entire Access cube on open causing the Excel files to be 1.6gb in RAM. I want a DB that causes Excel to not do this.
2. I use three databases in a kind of ETL framework. (A) One to link to the data files and run the queries, which output a final table to (B) a second DB, which I then use to (C) completely overwrite my production database because every time someone's pivot table hits the data, I lose Exclusive Access and cant update it. So I just paste the updated version over the old one, and for some reason the users are unaffected and their data connections are maintained.

Q: "Access that now has >800k rows and 42 columns." But how big is the MDB ?
A: If it were all in one Access file it would prob be 1.5Gb (when compacted).

Q: "I run a bunch of tables every 2 hrs out to a network location "
What does that mean ? 'run' ?
A: I run 7 or 8 scheduled, recurring queries in PS Query that output to network folders that I've set up

LSM Consulting: LOL! Have you considered doing standup? I’d like to book you for the next IT event that comes up :)

Nick67: Sound advice. Thanks.

simonpaul64
Actually, I have great respect for my colleagues in IT, and many of them are brilliant.

To answer your heartfelt question, its because we need a dataset that will allow us to scan 15 months of balances across the entire firm; by account, department, or legal entity, and be able to quickly drill-down (in 1 sec) not only to the ledger details, but right down to the subledger details.

There are many reasons why I developed an offline solution, but I basically created an ad-hoc version of Essbase. That means Union Queries of the ledger and subledgers, with multi-use fields depending on the source.

Initially, only one or two people used it to survey account-balances during the close to make sure entries were not missed and to quickly analyse the transactions that made up any balances requiring a closer look. Now there are 20 people on two continents using it.

Nick67 - You and simonpaul have a point that IT should be able reproduce what my application does. But, they just have bigger fish to fry. I've found that people like me develop user-specific solutions in the gaps between the recognition of a departmental need, and the final roll-out of the enterprise solution that "by the way will address your need" from IT, which in some cases can be years.

So, if I tally the votes,
3 for MS Sql Express
2 for Firebird
2 for renewing my vows with IT and seeing what they suggest.
1 for MySql, SqLite and PostGres

Thanks Lee555j5 for the file-based SqlLite.

Thanks to all so much for your suggestions!

"I want a DB that causes Excel to not do this."
Guess I don't get why the DB would be causing this ?

"If it were all in one Access file it would prob be 1.5Gb (when compacted)."
Ok ... but how big is the biggest single db?  And will this continue to grow ?

So, I may have missed this ... but is there any 'front end' involved in this process ? Or is this Access table the 'front end' ... so to speak.

And I would vote for SQL Express also, so that's 4.

For sure ... IT peeps HATE Access.  Our 1500+ IT Dept 'supplies' Access to us via Office (2010 now), but they do not 'support' Access.  That really works for me, cuz I totally have that covered. So, for the most part ... Access is under the radar ... and I can do whatever I want.  

The cool thing IT does do is ... supply our group with a 1Gbit fiber optic connection (Read: WAN) between our location and our shared drive/server.  This is a 60 mile round trip.  Because of this, when the user clicks a 'Submit' button ... I can open a table in a backend, find one record out of 700K, update a fields, close the db, come back and update the local mimic copy of that table (minus all the 'completed records') ... in approx 0.2 seconds!  And I am *not* making this up.

mx

"This isn't always true--but often enough--and sometimes with good justification."
ok.  But 99.314 % of the time :-)

mx

Sorry for the delayed response...

DbMX and Nick67: Thanks for taking the time to share your perspective.

Many of the comments here reminded me that "the right thing to do" would be going through proper channels to let IT solve the problem. So I devoted the better part of a day to that, and (...dreamy music and screen goes wavy)...

I asked A (mid-level) why would the windows team not respond to my ticket for a VM. She said they were short on drive space and were ordering more, but B could expedite it, so I send him an email, then printed it, and walked over and handed it to him.

10min later C from windows team calls. VM's only get 2Gb of disk space and since my estimate is up to 5Gb, I should check with D (a dba) to see if I can share space on an existing box running Sql Server. D said no space available so we'd need to purchase a server (from the price he quoted me it had to be a 9 series PowerEdge).

I bring this to my dept head E, who said I should check with F who runs the budget. F tells me the same thing he told me years ago...you cant have a GL outside of the GL and I need to shut down my database. Except now, I know its been designated as critical by E, so I go back to A fuming that I just want help, so she sends me back to B (who's a stellar guy and very supportive) who tells me of course our division has boxes running apps and I should follow up with him next week.

Here we go round the mulberry bush, So early in the morning

So, your perspectives helped me to focus on "Doing the right thing", which will (i hope) lead to the better solution. So thank you for that, and for the time and energy you invested in this.

to answer some of your questions:
-I'm using Office 2007 with 800k rows of data in Access. Excel Pivot Tables are the Front End.
-turns out that Excel needs to connect through Sql Server Analytical Services to recognize its connecting to a cube and thereby not load the entire source table into memory which I think rules out Sql Server Express.
-I did segment it, but some users still need to see everything.

thanks again.

Nick:

The evaluation server idea and your explanation is priceless. You definitely "get" where i'm coming from. Clearly, its sometimes difficult for users and IT people to understand each others priorities. But, that said, I'll assign points and I'll post a followup comment when I get to some kind of resolution.

Thanks again.

Strong participation and sound advice did more than answer my question...it changed the question altogether.