Beware of Search Argument (SARG) Data Types

  • Great article

  • Great article!  

    I've noticed this behaviour as well on one of my production databases 🙁

    There is a good explanation on MS site: http://support.microsoft.com/kb/271566/ and a related article about a fix in SP4. Suppose you can't change the application to insert the correct data type to avoid the table scan, then you can add an index, right? Well, in SP3 and earlier this can lead to incorrect results: http://support.microsoft.com/default.aspx/kb/899976

    JP

     

  • I really wish you'd written this article a few months ago. Our system was performing poorly, and I only stumbled across the "...N'stringValue' queries leading into skipped indexes and tables scans after logging a few days' worth of Profiler entries for reads > 12800 [10M of data]. Thanks for working out the SARG angle, I didn't get that far.

    Hibernate was quickly disqualified as the stealth "Unicodifier", and we focused on JDBC. A switch was found that, when set, would stop with the Unicode conversions already... but it didn't work as advertised. We eventually moved over to jTDS, got it configured right, and once it went live our (data) Disk Utilization Percent stopped plateauing at 100% during regular business hours.

    I don't know all the ins and outs of java connectivity layers, but JDBC seems to be one to avoid just now.

    Philip

  • ORMs DO NOT force your Database to reflect your Domain Model / Object Oriented Application Architecture. It’s quite opposite. They allow you to MAP your normalized relational, Foreign Keyed data to your objects....A table is NOT an object, nor should it be. Relation Data contains a different model than a domain/business model object (Business Logic should be wrapped up in a Domain Model on the Application side or work in conjunction with a solid OO Developed Domain layer that serves application Entities). This is where ORMs come in. They grab your relational data and Map it to your business Entity. So that you can have your backend DB modeled anyway you want.

     

    While Hibernate or NHibernate do not map to stored procedures, IBatis or IBatis.NET do, which are not “True” (Speaking Purist ORM here) ORMs, but functionally equivalent.

     

    Now, if a person is using a code generator, then YES, they are going to try to make their tables Look their Business object so they can auto generate all Business objects and Data Access Layer classes and any Crud SQL...But while this is fast and easy, it is not the right way to go, Which is why I prefer IBatis. Don’t confuse Code Generation with ORM.

     

  • Great article!

    >>Function based indexes are something Oracle has had for years, I'd like to see MS do a bit of catching up there.

    Indexes on calculated fields?

    I have never use them. What's your opinion?

  • Hi,

    Classic ORM is Object Role Modelling, not Object Relational Mapping.

    Everything else in this article about SARG is brilliant.

    Here are the references to the source on ORM:

    http://www.orm.net/pdf/springer.pdf#search='terry%20halpin%20object%20role%20modeling'

    Object-Role Modeling (ORM/NIAM)

    by Terry Halpin

    http://msdn.microsoft.com/library/?url=/library/en-us/dv_vstechart/html/vstchvsea_ormoverview.asp

    Object Role Modeling: An Overview

    Terry Halpin

    Visual Studio Team

    Microsoft Corporation

    November 2001

    Regards,Yelena Varsha

  • BTW I was able to confirm that the new driver is indeed the JTDS driver.  Seems to be working fine and provide a small, but noticable improvement over the MS JDBC driver.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • Thanks.

    Indexes on computed columns are an option, but ultimately they are not quite as useful as function based indexes IMO.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • No, Hibernate does not explicitly force your data model to reflect the object model, however the net result is nearly that, at least in my experience.  Admittedly this is a very small sampling that provides only anecdotal evidence and may not fully support the statement in the article.  However, the development team has had direct access to a "Hibernate expert" and they are reportedly following his recommendations.

    There are some pretty severe limitations that we have run across as well.  Every object referenced in your Hibernate queries must actually exist in the database so derived tables, CTEs and temporary tables are out of the question.  Hibernate also chokes on varibles so any kind of more complex queries and cursors are out too.  Hibernate can apparently make use of stored procedures but at the cost of greatly diminished pagination and sorting functionality.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • I loved the examples... nice and simple.  Great job, DC.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Great article.  What setting was changed to stop Hibernate from sending Unicode?

    Thanks,

    Dan

  • It was actually in JBoss... 

    In the apollo-ds.xml file you need to add the following in the <datasources> section.

    <xa-datasource-property name="SendStringParametersAsUnicode">false</xa-datasource-property>

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • The sendStringParametersAsUnicode is a property of the JDBC driver being used. The jTDS driver supports this parameter, as does one of the commercial SQL Server 2000 drivers (although I don't recall which one).

    http://jtds.sourceforge.net/faq.html#urlFormat

    Be sure to check your JDBC driver documentation for supported properties.

    Dave

  • Dave,

    Thanks for the clarification.  After consulting with the configuration team, it is indeed a jTDS setting.  I knew that we were using that particular JDBC driver, but their emails initially indicated that it was a JBoss setting...

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • Thanks for the feedback!  Any ideas on how to prevent unicode when NHibernate and the native SQL drivers are used?

    Thanks again,

    Dan

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply