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.
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.
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.
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.
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?
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).
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.
