Qlik Community

QlikView Performance

Discussion Board for collaboration on QlikView Performance.

Not applicable

SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

Hi,

I would like to select all rows from table (from a MariaDB database), and store them into a .qvd file.

My script looks like this :

ODBC CONNECT TO [MariaDB odbc];

SQL SELECT * FROM `my_table`;

STORE "MY TABLE" INTO C:\(...)\data.qvd;

Unfortunately, this table contains more than 4 millions of rows. And selecting all of them with Qlikview is very slow.

I let this query, SELECT *, running for more than 18 hours : it selected only 1,8 million of rows.

Do you happen to know some ways to optimize the query ? Letting this script running for a week to select 4 millions of row is not a solution.

------------

Here are some technical informations :

OS → Windows Server 2012 R2

MariaDB → ver. 10.2.6, is localhost

Qlikview → ver. 12.0.20200.0 SR3 64-bit Edition

ODBC driver → tested with ver. 2.0.15 and 3.0.0 (alpha), but that's the same / 64_bit

7 Replies
mwoolf
Honored Contributor II

Re: SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

Have you tried OLEDB?

roharoha
Valued Contributor III

Re: SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

do you read the whole table each time? why?

tomasz_tru
Valued Contributor

Re: SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

How long does it take to run this query in shell?

krishnacbe
Valued Contributor III

Re: SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

Avoid using Select * option, Pull the fields that you are using in Dashboard.

Not applicable

Re: SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

Well, I didn't know enough about OLE DB to try it, but it was worth trying after your post.

Now, it takes "only" 2 minutes and 40 seconds to scan the whole table ! Thanks !

By the way, I use "Microsoft OLE DB Provider for ODBC Drivers".

Not applicable

Re: SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

It takes less than 3 seconds to SELECT * using MariaDB CLI

Not applicable

Re: SELECT * through ODBC connector (MariaDB) is very slow - how to optimise ?

Unfortunately, the database already contains only needed informations. In other words, every field is useful.

Community Browser