Excel VS SQL Database - Qlik Community

Report Inappropriate Content · ‎2010-09-28

Hi,

Can anyone help me out?

I currently have a nice and stable QVW in which it has a lot of records in it..currently this records are being read by qlikview from excel spreadsheets and like my reload time in the server takes about 1.5 hours to finish. Now thats a very long time to wait for my records be updated in the server. My question is would my reload time be ay faster if i were to convert all my excel spreadsheets into a one big database?

Can you guys give me like a Pro's and Con's of having a database and having non?

hope to get some feedbacks from the comunity.

Thanks in advance!

Anonymous · ‎2010-09-28

Database is a system specifically designed to store data, while Excel is not. I don't think there are any disadvantages, except that the database support and maintanance requires different skills.
Reading data into QV application - I didn't do any testing, but my impression is that it is fatster from a database. Plus, you can write complex SQLs against datbabase and can't do the same in Excel.

nathanfurby · ‎2010-09-28

FYI - I have noticed that my Excel sheets load a lot faster when they are in the older .xls format comared to the newer .xlsx (XML format). Saved me a lot of time converting the files to older format.

Report Inappropriate Content · ‎2010-09-29

Hi Michael,

Thanks for your insights regarding this, I'm just not sure yet of how fast a reload can be if in terms of qlikview just reading records from excel or databse.

Hi Nathan,

This is a great additional information for me..Thanks!

Thanks Guys though i would need more infos regarding this matter..

rwunderlich · ‎2010-09-29

My untested guess is that reading from excel would usually be faster than reading form a database. The performance is more likely dueto the network link and the speed of the file system where the data resides. With xls files, it's easier to locate the file on a fast file system, for example, on the same disk where the reload is running.

Database systems tend to have more overhead with locks, logging and multiple databases and users.

Where are your xls files located relative to where the reload is running and what is the network speed between those two machines?

-Rob

Report Inappropriate Content · ‎2010-09-29

Hi Rob,

Thanks for your opinion and it makes sense if you put it that way..Just an additional question because i have a huge amount of records like 4 years of history having almost 2000 employees and 120 teams..currently what i have is using an incremental load which my historical data would no longer reload so that already saves time and what is currently being reloaded is the current month in which all 120 teams has their own xls. my reload time in the server takes about 1.5 hours to be done..would this seem be an average reload time for my records or is this a long time?

My whole motive is to make my reload time faster so im finding ways on how to achieve this but if this is the fatest that i can go with all my records then im okay with it.

rwunderlich · ‎2010-09-29

So you are loading 120 xls files. How many columns and how many total rows?

How much of the 1.5 hours is spent reading the spreadsheets and how much time is spent updating the historical QVD? You should be able to determine this from the log fie.

Are you updating a single QVD? That can be slow. If so, you can improves the process by writing a new QVD for each month or some other logical segmentation like yearly.

-Rob

Report Inappropriate Content · ‎2010-09-30

Hi Rob,

This 120 xls files are not standard in size and length but in an average it has like 12 sheets having 200 rows and like 50 columns..

what i did was there are qvds per month of each of these teams and i think it takes only 15minutes in reading the current months of excel files for these teams and the rest is just the qvds being read and some other qvds being read so practically its almost all just the qvds being read..

should i be okay with the reload time i am getting right now? or this is very long? because i do have so much records like each employee has their daily values since 2006 and i do have almost 2000 employees in my qlikview file.

rwunderlich · ‎2010-09-30

Sounds like your 2000 files are being loaded resaonably fast. And then you are updating 2000 QVDs? Are you getting optimized loads on the QVDs?

That could be a reasonable reload time, hard to say without seeing your log file. Back to your orginal question, I think you are getting a reasonable load time for the 2000 xls files.

-Rob

Report Inappropriate Content · ‎2010-09-30

As Rob has indicated, I wonder if your QVDs are loading 'optimized'. I have a few pointers with QVDs that I have noted with using them (only based on experience and not definitive!)

LOAD * when only a small subset of columns is needed has an overhead; specifically naming columns still allows a QVD load to be optimised
An optimised qvd load then a load resident to transform the data is faster than transforming the qvd directly
'Where' affects QVD optimisation but may still be quicker than optimised load under certain circumstances
Note that 'exists' in a QVD file does not affect optimisation but only if there is 1 only.
General rule: Always load a qvd without any 'where' or data transformations, but can still rename columns without affecting the optimisation

Regards,

Gordon