Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

SQL time out message during LOAD process (sense 3.1 SR1)

Hi there !

We are trying the Qlik sense server.

We have a Microsoft SQL server DB, where they are many rows (about 3 Millions / year).

I did the best to remove all not necessary data, unfortunately, it seems that Qlik sense refusing to Load data.

i have reduced to 1 year (VS 5 years) => same pb

3 months (about 800000 rows) => same pb

1 month (about 300000 rows) => OK

I have tried to load (display) data of a year on SQL management studio ... of course it took a while but it works.

could you help me please.

i do not see anywhere if there is any data size limitation with Qlik sense.

Also, in our project we expected to see "Live" data on Qlik , i mean we expected to make a scheduled task in order to reload data every 15 min.

(by the way, does Qlik reloads All data evrytime, or just "new" data ?)

MANY MANY thanks for all help you could provide.

i guess you understood that English is not ma native language, sorry for that ;-)

1 Solution

Accepted Solutions
Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

Hi,

Many thanks to you Stefan for your assistance and time you spend on that.

We finally fixed issue by using QVD

basically the Query is executed as usually on Qlik server but not loaded on Qlik DB

the resulat of the Queries are stored on "flat" file : QVD file

Increadibly faaaaaaast => 1 year of data faster than 1 month with "basic" loading method.

then just have to LOAD the content of the QVD...which is faster again...less than 1min for year

hope it could help anyone else in same situation.

Have anice day.

10 Replies
Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

Can you tell after how long you get the timeout?

Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

Hello,

first, thank you for your interest.

I would say less than 2 or 3 minutes (i could retry tomorrow if that need to be more accurate...no more server access today)

Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

You may want to check into the Connection String in the Qlik Data Connection - Google up on what what parameters to use (make sure to note whether the value is in milliseconds or seconds...).  It may tie to an ODBC setting you're using.  You may be able to enter the parm in the Adavanced (Name / Value) section, or if you have access to the QMC in the Data Connections (where you'll find the Connection string as something like this "CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=...").  Maybe it is as simple as adding a ";Timeout=60000" to set the timeout to 10 minutes.
Also, to get around timeouts, if the SQL statement is taking a long time, perhaps a Materialized View is something you could utilize (at least it would work until you have resolved the parms).  Basically Select the data into a MV and have the Qlik Load Script point to it.  Of course you'd have to do something to Refresh the MV(s) as needed, an extra step, but has worked for me with timeout issues in PostgreSQL.

Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

huuum, i will check that. also, maybe is just stupid as the SQL server timeout is cnfigured to short for remote query.

it look like there is a settings dedicated to remote query.

the default value look to be 600 (10 minutes)

(Settings on our test server, will check value on Live)

remotequeryTO.PNG

I will also see if something similar is required on QMC connection string.

not very familiar with VM...hope that the Timeout stuff will fix the issue.

Sorry to abuse, since it look you have experience, image the issue fixed, Qlik will load whole year data today in 5minutes, what about tomorrow ?

i mean Qlik will only load "fresh" data between today and tomorrow...or reload everything again and again ?

Many thanks for all you help.

Highlighted
Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

Hi,

I did a new tests today, it seems that Loading fails after 31sec....i did test 4 times, same result.

Error message is

The following error occurred:

QVX_UNEXPECTED_END_OF_DATA: ERROR [HYT00] [Qlik][ODBC SQL Server Wire Protocol driver]Timeout expired.

I did check on SQL server and the Remote query timeout is set to 600.

i have also tried to add

'timeout=60'

'ConnectionTimeout=60'

'SQL_ATTR_LOGIN_TIMEOUT=60'

but still same issue: same query took 54sec on SQL management studio.

Many thanks

Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

Well, I have recently had the same experience with a 30 second timeout for a Postgres DB, so it is a clue that you´re also having the same experience with SQL.  Basically, if it takes the DB more than 30 seconds to begin returning rows, something will cause a timeout.  Unfortunately I'm having trouble figuring out where/what that setting is.


To make things for work for now, I used Materialized Views (see notes above).

Hopefully someone in the community here can advise.

hic‌ may have it?

Will update if I find the reason/fix.

Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

hi,

thank you unfortunately im not familiar at all with MV, i had a quick check on Internet and i understand that the Materialized view is a "stored" view, like for any "normal" table.

In that case that will cause the db size will be multiplicate by 2.

what i did when starting this trial, is that i created a view with all data that need to be loaded into Qlik.

Interesting info, you gave me.

on SQL management studio, when i run a query on my view (for 2 month) the first rows are returned after 54Sec.

but when ru the same query but just for a Month the first rows are returned after 26Sec !

it looks like there is a 30sec barrier !

ok ok we could increase the SQL server ressources in order to make it answer faster, buuut i guess that Qlik could wait longer than 30sec ...

Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

Indexed View, what it is called in MS SQL

Create Indexed Views

So, maybe that'll work faster, and not take up much extra space (just for any added index)

Not applicable

Re: SQL time out message during LOAD process (sense 3.1 SR1)

Hi,

Many thanks to you Stefan for your assistance and time you spend on that.

We finally fixed issue by using QVD

basically the Query is executed as usually on Qlik server but not loaded on Qlik DB

the resulat of the Queries are stored on "flat" file : QVD file

Increadibly faaaaaaast => 1 year of data faster than 1 month with "basic" loading method.

then just have to LOAD the content of the QVD...which is faster again...less than 1min for year

hope it could help anyone else in same situation.

Have anice day.

Community Browser