topic Re: SQL table taking too long to store into QVD in QlikView

SQL table taking too long to store into QVD

Thu, 03 Oct 2013 13:15:18 GMT

Hi,

I have two huge sql tables with Table1 has 4213039 rows

and Table2 has 12710440 rows

when I am loading the tables into QVDs by using

Store Table1 into C:\qlikviewpath\Table1.qvd;

Store Table2 into C:\qlikviewpath\Table2.qvd;

to load the scrit execution is taking more than 14 minutes. could anyone let me know is there anyway to load data into QVD very fast.

Thanks.

Re: SQL table taking too long to store into QVD

Thu, 03 Oct 2013 13:26:39 GMT

Hello,

Probably you should optimize your query to last shorter. The row counts you mentioned are not really huge. If you have WHERE criteria in your query check if these columns have indexes.

Also if any part of your data will not be updated in the future, you could use Incremental Loading. Just store the whole table for once in a qvd file and then add the new parts to the old qvd file. This will significantly optimize your process.

Best.

Re: SQL table taking too long to store into QVD

Thu, 03 Oct 2013 13:30:17 GMT

Thanks. There is no indexes in the tables. and I have data migration so the table is updated. so, in this case how can I choose very fast load. could let me know please.

Re: SQL table taking too long to store into QVD

Thu, 03 Oct 2013 13:57:32 GMT

After writing the query run the estimated query plan -if you are using MS SQL. It will show you -if needed- a suggested index in a green line with the impact rate. You can right click to that suggestion to show missing index details and run the suggested index command.

Re: SQL table taking too long to store into QVD

Clever_Anjos — Thu, 03 Oct 2013 14:08:12 GMT

How many megabytes thats qvds are using?

Re: SQL table taking too long to store into QVD

Thu, 03 Oct 2013 14:11:19 GMT

One Qvd is 1.52 GB and another Qvd is 546 MB

Re: SQL table taking too long to store into QVD

flipside — Thu, 03 Oct 2013 14:21:26 GMT

Hi Amelia,

Is it the loading of data from SQL to Qlikview that's taking a long time, or the writing of the data from Qlikview to QVD?

flipside

Re: SQL table taking too long to store into QVD

Thu, 03 Oct 2013 14:26:44 GMT

Hi,

Both are taking too long. Any help please.

thanks.

Re: SQL table taking too long to store into QVD

dmohanty — Thu, 03 Oct 2013 14:32:33 GMT

First Check how much time its taking to fetch the data from Database when executing in your Tool (Toad, SQLAssistant,QMF whatever)
If you have '*' like this : SELECT * from Table1, replace star with all column names.
Check if are using GROUPBY properly if you are using it.

If else post your sample query, lets see to optimise!!!

Re: SQL table taking too long to store into QVD

Clever_Anjos — Thu, 03 Oct 2013 14:45:17 GMT

I my humble opinion they´re are too big.

We have a 'golden rule' here that no qvd can be more than 100 Mb.

Is there any field that you can use to split your files? (Maybe a month)

Re: SQL table taking too long to store into QVD

Thu, 03 Oct 2013 14:59:10 GMT

Hi please see below

Transactions:

LOAD TransactionID,

TransID,

ActualAccountID,

PayeeAccountID,

AccountID,

ID,

Type,

TransTypeDomain,

Category,

TransDate,

TransactionAmount,

Amount,

PendingAmount,

AvailableAmt,

BACSStatus,

CMSStatus,

PostedFlag,

RecFlag,

VoidTransactionFlag,

FailReason,

Reference,

BatchID,

PaymentPlanID,

BDDTransID,

ReturnedChequeNumber,

InvoiceNumber,

InvoiceDate,

Refund,

RecStatementID,

CMSTransID,

CreditorPaymentTransID,

FeePaymentFlag,

CreatedOn,

CreatedByID,

CreatedBy,

ChequeRunID,

ChequeRegisterID,

Number,

ChequeAmount,

ChequeRegisterCreatedon,

ChequeName,

ChequeCreditorName,

ChequeStatus;

SQL SELECT *

FROM "XX".schema."TRANSACTIONS";

Re: SQL table taking too long to store into QVD

flipside — Thu, 03 Oct 2013 15:01:51 GMT

You have two issues here, then.

Firstly, regarding the SQL query, how does it compare in time to execute this part in comparison to loading the same query on your SQL server? The lack of indexes may be part of the issue, are you using any joins or WHERE clauses? And how does your datamodel look after loading, are there any synthetic keys being created?

Secondly, I'm assuming the QVD is writing to a local drive and not a networked or shared drive? The time for this is probably just down to the speed that the server can write the file - are all the fields really necessary? Without upgrading the server you are probably left with trying to reduce the file size or change your QVD strategy.

flipside

Re: SQL table taking too long to store into QVD

greg-anderson — Thu, 03 Oct 2013 15:09:03 GMT

The query looks pretty straightforward, assuming 'TRANSACTIONS' is a table rather than a view. The lack of indexes might slow it down, but with a straightforward SELECT * and no WHERE clause, that should not be a major problem.

I see that flipside just posted what I was about to say. Without knowing more, I'm guessing the issue is the network speed between you and the database server and/or the network speed between you and the file destination.

I would look into splitting the table into at least 2 QVDs, if possible. Link them by Transaction ID.

Hope this helps.

-Greg

Re: SQL table taking too long to store into QVD

Thu, 03 Oct 2013 15:28:46 GMT

Transactions table is taking 5 mins 10 seconds in sql server and there are no indexes in this table and Table2 is taking 6 mins 11 seconds in sql server and there is one clustered index and two non clustered indexes in Table2.

Secondly I am testing the application in D drive but in different folder and as Live files are in different folder in the same drive.

So, in this how could I proceed please any help.

Thanks.

Re: SQL table taking too long to store into QVD

jagan — Thu, 03 Oct 2013 16:03:32 GMT

Hi,

Instead of select *, specify the columns you are using in dashboard in Select query, it reduces the data to load and also it reduces the QVD size also.

Regards,

Jagan.

Re: SQL table taking too long to store into QVD

flipside — Thu, 03 Oct 2013 19:28:57 GMT

Are they the times when run as queries within SQL Server? If this is the time to load data into Qlikview then that's less than 3 minutes to store data into QVDs or am I misunderstanding the facts?

Re: SQL table taking too long to store into QVD

greg-anderson — Thu, 03 Oct 2013 19:37:23 GMT

When you post the times, they don't really look bad to me. SQL Server is taking most of the time, whether in network lag, server processing, or waiting in a queue for processing (if it's a busy server).

Re: SQL table taking too long to store into QVD

rbecher — Thu, 03 Oct 2013 20:02:10 GMT

Fields with high cardinality like IDs are blowing the in-memory table and the QVD. You should really think about which fields you can omit here.

- Ralf

Re: SQL table taking too long to store into QVD

rajeshvaswani77 — Fri, 04 Oct 2013 02:51:44 GMT

Hi Amelia,

Do you see optimized load when its loading?

Also, on your development the speed could be less, on server will be much lesser.

To add more, this will be first time. At later time you will do incremental load and take only few records that have changed and concatenate to the QVD.

Guess this should be in seconds.

thanks,

Rajesh Vaswani

Re: SQL table taking too long to store into QVD

Fri, 04 Oct 2013 11:11:12 GMT

Hi,

Could you tell me how to see Optimize Load and incremental load.

Thanks.