Slowness of a query - Difference between QlikView and

[#1]

Hi Guys,

I have a problem with this query.

"SELECT O.ordernumber

                  ,magasin.VALUE as "ID Magasin"

                  ,vendeur.VALUE as "ID Vendeur"

FROM `SPC_ORDER` O

INNER JOIN `SPC_EXTRAFIELDORDER` magasin on magasin.INSTANCEKEY=CAST(O.ORDER_ID AS CHAR(20))

LEFT JOIN `SPC_EXTRAFIELDORDER` vendeur on magasin.INSTANCEKEY=vendeur.INSTANCEKEY

WHERE  O.CREATIONDT >= str_to_date('01/07/2013 00:00:00','%d/%m/%Y %H:%i:%s') 

            AND vendeur.NAME='borneCodeFRA'

            AND not isnull(vendeur.VALUE)

            AND magasin.NAME='borneIdMag'

            AND not isnull(magasin.VALUE);"

If i execute a explain plan with MySQL WorkBench or Toad Mysql ,it returns : 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	O	ALL	CREATION_IDX				1524709	Using where
1	SIMPLE	vendeur	ref	Index_InsTypKey,idx_name,Index_IK_NAME	Index_IK_NAME	124	func,const	1	Using where
1	SIMPLE	magasin	ref	Index_InsTypKey,idx_name,Index_IK_NAME	Index_IK_NAME	124	func,const	1	Using where

.

If i execute the same explain plan with QlikView, it returns :

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	magasin	ref	Index_InsTypKey,idx_name,Index_IK_NAME	Index_IK_NAME	124	WLPEM.vendeur.INSTANCEKEY,const	1	Using where
1	SIMPLE	O	ALL	CREATION_IDX				1524709	Using where
1	SIMPLE	vendeur	ref	Index_InsTypKey,idx_name,Index_IK_NAME	idx_name	102	const	2474940	Using where

To connect to the database, I use ODBC connector.

I think that QlikView misinterprets the function Cast.

What do you think?

Thank you.

[marcus_sommer]

In your case you queries your database directly with its own functionalities and limitations will be only by the odbc-driver. Often it is better and faster to query only the raw-data and make all transforming within qlikview.

- Marcus