[IQUG] How bad in IQ are mismatched datatypes in join cols

Baker, Chris c.baker at sap.com
Thu Nov 2 16:03:04 MST 2017


I agree with David R.

Using the same datatypes for JOIN and RI columns (as well as the same indexing of those columns) is one of the basic principles of schema design and tuning.  This is not just an IQ thing – it applies to ANY DBMS.

(Beat them harder.)

Chris

Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>

https://sap.na.pgiconnect.com/I826572
Conference tel: 1-866-312-7353,,9648565377#<tel:1-866-312-7353,,9648565377%23>

From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Rittenhouse, David
Sent: Thursday, November 2, 2017 6:44 PM
To: Louie, David <David.Louie at blackrock.com>; IQ Users Group <iqug at iqug.org>
Subject: Re: [IQUG] How bad in IQ are mismatched datatypes in join cols

Hi David,

You should beat them aggressively.  ;-)

IQ will convert the smaller datatype to the larger datatype for every entry in the column.

For small tables you may not notice the impact, but if you have billions of entries it could be a significant performance impact (potentially orders of magnitude.)

Hope you get them to see the light..

David


David Rittenhouse
Senior Consultant, SAP Data & Technology Services
Service & Support
SAP (UK) Limited
Clockhouse Place, Bedfont Road, Feltham, TW14 8HD Middlesex, UK
E:  d.rittenhouse at sap.com<mailto:d.rittenhouse at sap.com>
M: +44 (0) 7899 948 295
www.sap.com/uk
Please consider the environment before printing this email.



From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of Louie, David
Sent: 02 November 2017 21:52
To: IQ Users Group <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: [IQUG] How bad in IQ are mismatched datatypes in join cols

I know that in ASE it’s a big problem.  Is it the same magnitude in IQ?  I would think so but want to know how aggressively I should be beating our developers :D

The index advisor is finding a lot of datatype mismatches on join cols in our queries.

Thanks
David


This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for further information.  Please refer to http://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.

© 2017 BlackRock, Inc. All rights reserved.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20171102/97b4d072/attachment-0001.html>


More information about the IQUG mailing list