Qliksense: SQL query reached limit despite increasing the query timeout

[amni9412]

Hi,

I am trying to load data the table from MySQL server with 21,924,639 rows like the following:

[sales_transactions]:
SELECT `business_date`,
	cashier,
	`check_closed`,
	`check_no`,
	`grand_total`,
	`net_sale`,
	`store_code`,
	`store_id`,
	`transaction_id`
FROM `xxx_master_of_the_house`.`sales_transactions`
//LIMIT 3000000
;

STORE * FROM [sales_transactions] INTO [lib://../SALES_TRANSACTIONS.QVD](qvd);
drop table [sales_transactions];

However, I keep running into this error message below.

Error: ERROR [HY000] [Qlik][MySQL] Query execution was interrupted, maximum statement execution time exceeded
Execution Failed
Execution finished.

 I have already increased the timeout session in QMC to 999 before loading the table.

CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=mysql;host=xx.xx.xx.xx;port=xxxx;db=xxx_master_of_the_house;SSLMode=PREFERRED;UseTrustStore=false;QueryTimeout=999;"

Is there any other way that I am able to load the data without exceeding the maximum statement execution time?

[ArnadoSandoval]

Hi @amni9412 

Let's analyze-understand your problem:

1. Selecting near 22 million records certainly will take longer than 999 (no units, are they milliseconds, seconds, hours?)?
2. Have you try your query outside Qlik? Do you have a SQL console where you could run the query and time its response time? I am thinking about using Squirrel
3. Do you have any idea on how many sales transactions per months are stored in the 'sales_transactions' table?
4. It could also be records-lock; Are you running your query while users are working in the Application writing to this table?

Yes, this is not a direct solution to your issue, just asking questions to understand why the query is timing out.

Hope this helps,

[amni9412]

Hi Arnado,

I have increased the timeout value in connection string to 65535 and didn’t encounter any more of the same issue.

I have also increased the server size as well, which helped as well.

Thank you for your input.

[Arindam_Ghoshal_13]

Thanks @amni9412 

This really worked.