- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Qliksense: SQL query reached limit despite increasing the query timeout
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @amni9412
Let's analyze-understand your problem:
- Selecting near 22 million records certainly will take longer than 999 (no units, are they milliseconds, seconds, hours?)?
- 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
- Do you have any idea on how many sales transactions per months are stored in the 'sales_transactions' table?
- 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,
A journey of a thousand miles begins with a single step.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @amni9412
This really worked.