topic Re: Slow xls load only in QV Server in QlikView

Slow xls load only in QV Server

cristian_av — Thu, 16 Aug 2018 15:58:36 GMT

I have a script that loads data from about 8 xls files. For some reason, it is really slow in QV Server.

When I run the script in my computer, it takes a less than a minute to load the data

When I load it in the QV Server, by openin QV Desktop on the server, it takes about 15 minutes only in the xls.

I'm always running the same file with the same data.

I've checked the CPU (2-4%), RAM (10 Gb Free) and Network (2 Mb/s usage) of the server, but it is fine.

The xls files are on a second server. To test connectivity, I've copied the xls to the QV Server, and it took only 3 seconds.

Reload in my computer: (about 3 minutes in reload all the script)

Reload in Server: (about 17 minutes in reload all the script)

-Slow only in the part that loads the xls files.

This is the part of the code that is causing the problem in the server:

SET RepositorioXLS = '\\HV-SERVER-CM\comex$\Archivos ASOEX';

CargaAsoex:

LOAD * INLINE [CampoTemporal];

FOR each File in filelist(RepositorioXLS& '\T*--*-*-* al *-*-*.xls')

CONCATENATE (CargaAsoex)

LOAD right(left(mid('$(File)',index('$(File)','\',-1)+1),5),4) as %pate_tempor,

ESPECIES,

VARIEDAD,

CAJAS,

KG.,

EXPORTADOR,

[REGION DESTINO],

[PUERTO DESTINO],

[PAIS DESTINO],

RECIBIDOR,

NAVE,

[TIPO NAVE],

SEMANA,

FECHA,

[PUERTO EMBARQUE],

FileTime('$(File)') as FechaArchivo

FROM [$(File)] (biff, embedded labels, table is Asoex$)

Where right(left(mid('$(File)',index('$(File)','\',-1)+1),5),4)>=$(vAñoInicio)-3;

NEXT File

DROP FIELD CampoTemporal;

Re: Slow xls load only in QV Server

Gysbert_Wassenaar — Thu, 16 Aug 2018 17:11:34 GMT

Sounds like a slow network connection. Have you tested with the excel files located in a directory on your QV server?

Re: Slow xls load only in QV Server

cristian_av — Thu, 16 Aug 2018 20:10:35 GMT

I've just tried it.. with no luck!

Any other idea?

Thanks!!

Re: Slow xls load only in QV Server

Gysbert_Wassenaar — Fri, 17 Aug 2018 05:07:12 GMT

What do you mean 'with no luck'? You can't put the excel files on the server? The reload fails? Or it all works, but just as slow as before?

Re: Slow xls load only in QV Server

cristian_av — Fri, 17 Aug 2018 12:48:14 GMT

Sorry!

All works, but just as slow!

Finally I've changed the format to xlsx and it works fine. Don't know why.

Acoording to this, it seems to be something tricky with xls (biff) files.

Bad Zip File Error from reading XLS files

Re: Slow xls load only in QV Server

Gysbert_Wassenaar — Fri, 17 Aug 2018 13:23:58 GMT

Glad you found a solution.