Beware of Search Argument (SARG) Data Types

Question

Post reply

Beware of Search Argument (SARG) Data Types

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

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Ian Yates

SSCoach

Points: 19738

July 13, 2006 at 11:42 pm

#649137

Great article

JP de Jong-202059 SSCommitted Points: 1674 More actions · Answer 2

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

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 3

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

Shawn-265447 Grasshopper Points: 15 More actions · Answer 4

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.

Alexander Yuryshev Old Hand Points: 390 More actions · Answer 5

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?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

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

DCPeterson SSCoach Points: 15266 More actions · Answer 7

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

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

DCPeterson SSCoach Points: 15266 More actions · Answer 8

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

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

DCPeterson SSCoach Points: 15266 More actions · Answer 9

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

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

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)

Dan Skutt SSC Rookie Points: 31 More actions · Answer 11

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

Thanks,

Dan

DCPeterson SSCoach Points: 15266 More actions · Answer 12

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

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

David Kilzer SSC Rookie Points: 35 More actions · Answer 13

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

DCPeterson SSCoach Points: 15266 More actions · Answer 14

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

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

Dan Skutt SSC Rookie Points: 31 More actions · Answer 15

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

Thanks again,

Dan