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

Mark Mumy markdmumy at gmail.com
Fri Nov 3 06:07:34 MST 2017


IQ is a bit more forgiving than ASE in this respect.  But forgiving isn’t the same as it’s not an issue.  I’m with David R.  Proper coding practices and principals should be enforced.  I like using a big stick, but that’s just me.  :)  Regardless of database, every little bit helps with performance.  Why would you knowingly want to slow the system down?  When the modelers don’t follow best practices and accepted standards, remind them that they are the ones that are slowing the system down.  As DBAs, we have to manage what the app folks give us.  Somehow the app folks forget that poor performance is generally tied back to their product and not something the DBA does.

Mark

> On Nov 3, 2017, at 06:25, Louie, David <David.Louie at blackrock.com> wrote:
> 
> Yes agree.  To David R’s point I’ve seen too many issue with queries with mismatches but will get out the big stick.
>  
> Thanks guys!
>  
> From: Baker, Chris [mailto:c.baker at sap.com <mailto:c.baker at sap.com>] 
> Sent: Thursday, November 02, 2017 7:03 PM
> To: Rittenhouse, David <d.rittenhouse at sap.com <mailto:d.rittenhouse at sap.com>>; Louie, David <David.Louie at blackrock.com <mailto:David.Louie at blackrock.com>>; IQ Users Group <iqug at iqug.org <mailto:iqug at iqug.org>>
> Subject: RE: How bad in IQ are mismatched datatypes in join cols
>  
> 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 <https://urldefense.proofpoint.com/v2/url?u=http-3A__www.sap.com_&d=DwMGaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=obYH8mmN_dVO4PEP4w_HoF7MmiKhCWoA92xJFnuFFnQ&s=xT0hFKRUO1rG0z3z-piskVTMnfkss3NclfYezuuwOds&e=>
>  
> https://sap.na.pgiconnect.com/I826572 <https://urldefense.proofpoint.com/v2/url?u=https-3A__sap.na.pgiconnect.com_I826572&d=DwMGaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=obYH8mmN_dVO4PEP4w_HoF7MmiKhCWoA92xJFnuFFnQ&s=DlAihDGzTvP10nczsK5993at8VEjEkdGwItDfqOMu9w&e=>
> 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> [mailto: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 <mailto:David.Louie at blackrock.com>>; IQ Users Group <iqug at iqug.org <mailto: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 <https://urldefense.proofpoint.com/v2/url?u=http-3A__www.sap.com_uk&d=DwMGaQ&c=zUO0BtkCe66yJvAZ4cAvZg&r=hvVkP5y30w3_fHBwXsoZ2pqbwQXietniE01Z666b0tE&m=obYH8mmN_dVO4PEP4w_HoF7MmiKhCWoA92xJFnuFFnQ&s=WCvoxJY9k2YjDcEZKiviV1KwsNDdY6N6Xo3cW4Ca-bw&e=>
> 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 <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. Seehttp://www.blackrock.com/corporate/en-us/compliance/email-disclaimers <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 <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 <http://www.blackrock.com/corporate/en-us/about-us/contacts-locations>.
> 
> © 2017 BlackRock, Inc. All rights reserved.
> _______________________________________________
> IQUG mailing list
> IQUG at iqug.org <mailto:IQUG at iqug.org>
> http://iqug.org/mailman/listinfo/iqug <http://iqug.org/mailman/listinfo/iqug>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20171103/f64e7847/attachment-0001.html>


More information about the IQUG mailing list