Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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;
Sounds like a slow network connection. Have you tested with the excel files located in a directory on your QV server?
I've just tried it.. with no luck!
Any other idea?
Thanks!!
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?
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.
Glad you found a solution.