2

Sometimes I use MS Access 2017 to perform database tasks: Union, Filter, Join ...
From time to time I receive data which I need to process, these are different, short, ad-hoc tasks, I need a tool where I can quickly import data and perform my queries.

I feel I've reached it's limits, my queries based on limited size data take minutes to run. (the same data is processed in no time in Spotfire, however that's not a database tool).

I'm looking for a faster tool now, which could do it more efficiently.

  • I'm familiar with SQL, use Oracle's SQL developer and MS SQL Server Management studio, however they need a connection, can't connect to local databases
  • I've a bit of understanding of R, a package there might be good too
  • looking the internet SQLite seemed promising, however it's good for application development, I didn't see the way how could I do easily something simple in it.
  • I've also found LibreOffice / OpenOffice suggested as similar tools, but I'm not sure those would have better performance.

(my data:

  • 4-8 tables
  • 1-10 MB each as txt files
  • 10-300 k rows each

my Pc:

  • HP EliteBook 840 G3, Intel i5-6300, 16 GB RAM
  • Windows 7 Professional 64 bit, Office Professional Plus 2013 32 bit

what I've tried (without big improvement):

  • embedding all data, adding indexes)
  • 1
    So a plain, file-based SQL database would fit your needs? Like SQLiteStudio? – Izzy Nov 23 '17 at 7:51
  • Yes, seems like a solution!! (I check it before give a definite answer, but it is really something I need). – Máté Juhász Nov 23 '17 at 8:06
  • Hasn't all the functionality of MSAccess, so I was unsure if that's what you're after. Let me know if it fits, so I make it a full answer. I use it myself, btw ;) – Izzy Nov 23 '17 at 8:09
  • It just knows what I need, please add it as an answer. Based on it, I've also tried DB Browser for SQLite which is available through portableapps platform, it can create a table by csv import which I couldn't do in your tool. Otherwise SQLite studio seems to be more user friendly / interactive – Máté Juhász Nov 23 '17 at 8:21
  • The Firefox addon Sqlite Manager is better. Besides it's not something to do with your database development application. Are you sure you did database normalization? If it didn't work I recommend you to use an archive manager to compress files, if searching or filtering text is not a problem. – onurcano Nov 25 '17 at 19:31
2

As you just confirmed, SQLiteStudio is what you're looking for:

  • it works with local database files, which are "portable" (you can just copy them to another machine, and even use them with other software)
  • it can deal with the data sizes you've mentioned just easily (I use it with databases several Megabytes in size – other projects use SQLite databases even with several hundreds of Megabytes)
  • you already found SQLite "promising", but obviously only lacked the GUI SQLiteStudio provides
  • *SQLiteStudio is available cross-platform, so it works with your setup
  • it's leight-weight and (compared with LibreOffice which you named) has much faster startup and a much smaller "footprint"
  • it comes with an integrated database browser as well as with a query editor

SQLiteStudio SQLiteStudio
Screenshots: database browser, query editor (source: SQLiteStudio; click images for larger variants)

I'm using SQLiteStudio myself, and am quite happy with it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.