1

I have an application that runs on a MySQL database, the application is somewhat resource intensive on the DB.

My client wants to connect Qlikview to this DB for reporting. I was wondering if someone could point me to a white paper or URL regarding the best way to do this without causing locks etc on my DB.

I have searched the Google to no avail.

Skyboard
  • 51
  • 2
  • select statements are lockless, however I'd suggest that for resource isolation you use a replication slave for intensive reporting. A requirement called Qlikview is a little broad - maybe you can use more words for those not familar with the application. Maybe you can ask some questions to make the application less intensive too. – danblack Feb 26 '19 at 09:28
  • Hi, has my answer helped? – Hubert Dudek May 07 '19 at 23:58
  • Yes it has thank you. – Skyboard May 17 '19 at 09:37

2 Answers2

0

Qlikview is in-memory tool with preloaded data so your client have to get data only during periodical reloads not all the time. The best way is that your client will set reload once per night and make it incremental. If your tables have only new records load every night only records bigger than last primary key loaded.

If your tables have modified records you need to add in mysql last_modified_time field and maybe also set index on that field.

last_modified_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

If your fields are get deleted the best is set it as deleted=1 in mysql otherwise your client will need to reload everything from that tables to get to know which rows were deleted.

Additionally your client to save resources should load only data in really simple style per table without JOINS:

SELECT [fields] FROM TABLE WHERE `id` > $(vLastId);

Qlikview is really good and fast for data modelling/joins so all data model your client can create in QLikview.

Hubert Dudek
  • 1,666
  • 1
  • 13
  • 21
0

Reporting can indeed cause problems on a busy transactional database.

One approach you might want to examine is to have a replica (slave) of your database. MySQL supports this very well and your replica data can be as up to date as you require. You could then attach any reporting system to your replica to run heavy reports that won't affect your main database. This also gives you a backup (2nd copy) and the backup can further be used to create offline backups of your data also without affecting your main database.

There's lots of information on the setup of MySQL replicas so that's not too hard.

I hope that helps.

Paul Jowett
  • 6,513
  • 2
  • 24
  • 19