Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
We're experiencing a problem here where it appears that the call to the STORE <table> INTO <file.qvd> (qvd) is taking hours to perform, causing the reload job to timeout.
Here is an extract from the document log:
13/07/2014 7:45:04 PM: 0098 Tango_SPV:
13/07/2014 7:45:04 PM: 0099 LOAD
13/07/2014 7:45:04 PM: 0100 'Tango' AS [Data Source],
13/07/2014 7:45:04 PM: 0101 monthend(makedate(2014, 6, 1)) AS [Collections Month],
...
...
...
13/07/2014 7:45:04 PM: 0172 SQL EXECUTE dbo.cspQlikViewSPVExtract '30 Jun 2014'
13/07/2014 9:09:01 PM: 70 fields found: Data Source, Collections Month, ..., 2,615 lines fetched
14/07/2014 1:15:35 AM: 0173 STORE Tango_SPV INTO [QVDs\TangoCurrent\Tango 2014 06.qvd] (qvd)
14/07/2014 1:15:35 AM: 0175 DROP TABLE Tango_SPV
It's clear that the SQL stored procedure is very slow and could do with a re-write and optimisation, but what is not clear is why it takes so long to write the QVD to disk.
The file eventually does get written:
14/07/2014 01:15 AM 662,807 Tango 2014 06.qvd
It is not all that big at 648kb. There is plenty of disk space available (147Gb free), and there are no permissions issues as the file does get written successfully in the end.
Due to the prolonged disk write, the reload eventually hits the 6 hour timeout put on the reload task. This could be increased, but I'm keen to understand why this issue is happening in the first place.
It's only been happening for the last 7 days. Prior to that, the whole reloads process, including 2 QVD creations and storing actions, only took just over 2 hours. We are not aware of any changes to backup schedules on the system or other changes within the last 7 days. We've pushed the reload task back 45 mins and rebooted the system but the issue remains. The Qlikview server is managed by a third party in a virtualised environment to which we have limited access.
Am I reading the log correctly? Is it the disk write taking a long time, or is it just the SQL stored proc call?
Any suggestions would be appreciated.
Cheers
Adam
Hi,
Its your sql Stored Procedure that is taking time.
Look at belo log
14/07/2014 1:15:35 AM: 0173 STORE Tango_SPV INTO [QVDs\TangoCurrent\Tango 2014 06.qvd] (qvd)
14/07/2014 1:15:35 AM: 0175 DROP TABLE Tango_SPV
Store Started at 1:15:35 (Line 1) and it completed within a second.
and the table is dropped immediately (Line2)
All the time is consumed by SP.
Regards
ASHFAQ
Hi,
Its your sql Stored Procedure that is taking time.
Look at belo log
14/07/2014 1:15:35 AM: 0173 STORE Tango_SPV INTO [QVDs\TangoCurrent\Tango 2014 06.qvd] (qvd)
14/07/2014 1:15:35 AM: 0175 DROP TABLE Tango_SPV
Store Started at 1:15:35 (Line 1) and it completed within a second.
and the table is dropped immediately (Line2)
All the time is consumed by SP.
Regards
ASHFAQ
Of course you are right.
Time to rewrite the query.
Thanks very much Ashfaq.
Adam