0

I have a web app which through a .dll ( vb6 dll, YES VB6, please don't ask why - lol ) connects to an ms access file where it makes some queries and presents data to the user.

the db file ( ms access ) is simple: it has 1 table with about 8 columns, and ~300.000 entries and some stored queries. So it is a medium size database.

Now, since most of the queries are kind of long ( 1 - 3 seconds ) What I'm looking for is, if there is some other FASTER database solution with SQL syntax. All queries are READ-ONLY.

requirement: must be able to connect to it through vb6

BONUS POINT: if there is no need to run a daemon ( as in a server ) but it's just a simple file ( sqlite maybe, mongo ? )

thanks in advance!

MirrorMirror
  • 186
  • 8
  • 36
  • 70
  • 1
    300,000 entries is not too many, even for Access. What sort of queries are them? Maybe you are missing a simple index. – Kobi Jan 14 '13 at 18:42
  • @Kobi, I'm also worried about access, what happens if users hit the file simultaneously (~10 simultaneous users). It would be slow. Also I'm not an expert on DB but I think access would have a problem if a second query to the file was to happen, while a first query was taking place. – MirrorMirror Jan 14 '13 at 18:45
  • @MIrrorMirror Access is NOT a database server. It's a desktop database. So you should think about the [concurrency updates](http://stackoverflow.com/questions/694921/ms-access-mdb-concurrency)....more.. BTW what have you tagged it as Mysql and SQL Server? :) – bonCodigo Jan 14 '13 at 18:49
  • @bonCodigo I know, I have inherited the app from previous devs. Btw there are no UPDATES. the queries are all READONLY – MirrorMirror Jan 14 '13 at 18:52
  • @MIrrorMirror I take my word on `updates`. If you think many users will access these queries, then you may want to have `temp table`.. – bonCodigo Jan 14 '13 at 18:54

2 Answers2

1

Try SQLite : http://www.sqlite.org/about.html and ODBC driver. Has read-only mode. Also, what you might like - may read data from a CSV file.

Or JavaDB and JDBC-ODBC driver.

Ondra Žižka
  • 43,948
  • 41
  • 217
  • 277
  • 1
    The OPs problem was the slow performance of the existing queries. Neither SQLite nor a CSV file will help with that. I can't speak to JavaDB, but it seems odd to install Java for this purpose - unless of course you are using Java in your product already – Sebastian Meine Jan 14 '13 at 19:14
  • 2
    Believe me, anything is faster than Access. – Ondra Žižka Jan 14 '13 at 19:19
  • Besides, java can be distributed along the way, no need to install. It's ~30 MB so no big deal. – Ondra Žižka Jan 14 '13 at 19:21
1

You should look into SQL Server Express: http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx SQL Server Express gives you access to the full SQL Server database engine with a few restrictions (e.g. database size < 4GB, number of CPUs used = 1) for free.

The 2012 version also has a "LocalDB" option that does not require a "server". There seem to be a few articles out there that explain how to connect to one of those from VB6, (e.g. http://piecesoeight.blogspot.com/2012/04/using-vb6vbscript-to-connect-to-sql.html) but non of them seems to be complete, so you will have to try that out.

Sebastian Meine
  • 11,260
  • 29
  • 41