SQLServerCentral / Discuss Content Posted by Don Peterson / Article Discussions / Article Discussions by Author / Beware of Search Argument (SARG) Data Types / Latest Posts

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Sun, 05 Dec 2010 12:30:59 GMT

Thanks, and your're welcome. I'm glad to know that the article was useful.

RE: Beware of Search Argument (SARG) Data Types

Evil Kraig F — Sun, 05 Dec 2010 01:27:08 GMT

I think this is one of those things in the back of my head I always knew, and just never really appreciated just how intrusive the problem could be.Awesome article, great presentation, and thank you. You may have just fixed something for me on Monday. :-D

RE: Beware of Search Argument (SARG) Data Types

Jeff Moden — Sat, 04 Dec 2010 09:54:01 GMT

I agree with many of the others. This is a "hidden" performance problem that a lot of people don't even consider because they don't know about things like data-type precedence. It's one of the primary reasons why some folks think Tally Table functions are slow and why some cursors appear to be faster than certain setbased queries.Very well done, DC... this should be required reading not only for those learning SQL, but for those teaching it, as well.

RE: Beware of Search Argument (SARG) Data Types

wwallace-692342 — Tue, 30 Nov 2010 19:42:46 GMT

This was a great article. I was researching why DevExpress XPO was doing this on the back end as well. Keep in mind, this is derived from the system.data.sqlclient class and most of the ORMs inherit that class to build on top for SQL server.We realized a 8X difference, on average, from changing the datatype. I will say we have high performance SANs and multi-proc servers to handle the storage and CPU load, but this kept me from having to go clustered and deal with updating multiple servers and increased performance like night and day.This article was a God send. I really appreciate the effort!Wallew

RE: Beware of Search Argument (SARG) Data Types

Dave Ott — Fri, 13 Jul 2007 08:10:00 GMT

Great Stuff

Thanks

RE: Beware of Search Argument (SARG) Data Types

Dave F-425609 — Fri, 13 Jul 2007 06:51:00 GMT

Great article. Very informative.

RE: Beware of Search Argument (SARG) Data Types

Dan Skutt — Mon, 23 Oct 2006 15:39:00 GMT

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

Thanks again,

Dan

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Mon, 23 Oct 2006 07:48:00 GMT

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...

RE: Beware of Search Argument (SARG) Data Types

David Kilzer — Sat, 21 Oct 2006 08:30:00 GMT

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#urlFormatBe sure to check your JDBC driver documentation for supported properties.Dave

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Sat, 21 Oct 2006 08:22:00 GMT

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>

RE: Beware of Search Argument (SARG) Data Types

Dan Skutt — Fri, 20 Oct 2006 13:55:00 GMT

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

Thanks,

Dan

RE: Beware of Search Argument (SARG) Data Types

Jeff Moden — Sun, 08 Oct 2006 11:59:00 GMT

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

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Wed, 19 Jul 2006 18:41:00 GMT

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.

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Wed, 19 Jul 2006 18:20:00 GMT

Thanks.

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

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Wed, 19 Jul 2006 18:16:00 GMT

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.

RE: Beware of Search Argument (SARG) Data Types

Yelena Varshal — Mon, 17 Jul 2006 10:49:00 GMT

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 HalpinVisual Studio TeamMicrosoft Corporation

November 2001

RE: Beware of Search Argument (SARG) Data Types

Alexander Yuryshev — Mon, 17 Jul 2006 03:23:00 GMT

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?

RE: Beware of Search Argument (SARG) Data Types

Shawn-265447 — Fri, 14 Jul 2006 21:56:00 GMT

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.

RE: Beware of Search Argument (SARG) Data Types

Philip Kelley — Fri, 14 Jul 2006 09:34:00 GMT

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

RE: Beware of Search Argument (SARG) Data Types

JP de Jong-202059 — Fri, 14 Jul 2006 04:59:00 GMT

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

RE: Beware of Search Argument (SARG) Data Types

Ian Yates — Thu, 13 Jul 2006 23:42:00 GMT

Great article

RE: Beware of Search Argument (SARG) Data Types

Calvin Lawson — Thu, 13 Jul 2006 14:50:00 GMT

Great article, Don! This is extremely helpful....

RE: Beware of Search Argument (SARG) Data Types

David Kilzer — Thu, 13 Jul 2006 11:15:00 GMT

Regarding ORM and Hibernate, my company is currently using Hibernate 3.0.x (started with 2.x, soon to upgrade to 3.1.x) with our new Java-based web applications (migrating slowly from VB6 desktop apps). For simple CRUD (create, read, update, delete) operations, Hibernate makes the developer's job trivially easy to do. There is no need for developers to write any SQL in these cases. In fact, they shouldn't ever have to write SQL anymore, although Hibernate has its own query language called HQL that is an object-oriented variation of SQL that is useful to solve some issues.Most of the problems that I've seen arise when you want to use Hibernate to do reporting, or ywhen ou run into performance problems because the developers don't fully understand what Hibernate is doing when it converts their configuration information and Java code into actual SQL. Hibernate is a lot more complex that it may look on the surface, and there are definitely best-practices that should be used and anti-patterns to avoid. It is also very flexible, though, and you will find that the developers have actually put a LOT of thought into the features and default behaviors of the tool.Debugging performance issues can be very time-consuming until you have experience doing it, although Hibernate's "show_sql" feature can help a lot (along with profiling on the database). I highly recommend the "Hibernate in Action" book by Manning Publishing, whether you're a developer or a DBA. http://www.manning.com/bauer/My company is also practicing agile development methodology (yet another topic for another day), so in addition to using Hibernate, the developers actually "evolve" the database schema as they need to implement features. (I know, shudders just went through every DBA reading this!) It's not as bad as it seems, though. There are definitely some pitfalls that they've run into, but when our DBAs catch them, there has always been a way to fix the problem using the additional features that Hibernate provides.I think a best-practice for us was to have a DBA work with (or even "pair" with) the development team part-time to prevent some of these issues. Learning to use Hibernate effectively (and efficiently) is definitely more of a journey than a destination for all parties involved.I am interested to hear Don's arguments for using stored procedures since I like the idea of providing abstraction layers to the database (which Hibernate also does, but more from a developer's perspective).Dave

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Thu, 13 Jul 2006 11:06:00 GMT

Thanks. Originally we were using the Microsoft JDBC driver. After this problem was resolved, several developers wanted to try a different driver, I'm not exactly sure which one(s) they tried. The performance was a little better (about 10%), not much, but enough to decide to do more testing.

I'll check to see which driver they are using.

RE: Beware of Search Argument (SARG) Data Types

David Kilzer — Thu, 13 Jul 2006 10:59:00 GMT

In the article, you never mentioned which JDBC driver you were using, nor which options you were setting on the driver's connection string. This can make a big difference in performance.If you haven't tried it, I would encourage you to test the jTDS driver with your application, with the sendStringParametersAsUnicode property set to 'false'. Hibernate makes it trivial to switch JDBC drivers, if only for testing: http://jtds.sourceforge.net/ http://jtds.sourceforge.net/faq.html#urlFormatNote that the jTDS driver still supports connecting to SQL Server 6.5 or 7 databases, unlike Microsoft's latest JDBC driver. (DISCLAIMER: I am a contributor to the project. I implemented the feature that let the driver use named pipes to talk to 6.5 databases that weren't using TCP/IP connections.)Dave

RE: Beware of Search Argument (SARG) Data Types

DCPeterson — Thu, 13 Jul 2006 10:57:00 GMT

Oops. A bit of an oversight there. Those two queries are not logically identical. If you make them <= and >= they are the same.

However, the point I was trying to illustrate still stands. The first one cannot use an index regardless of the projection list. The reason it can't use an index is that you are performing a function (conversion of the data into a new form) on the data and the index is not organized according to the function.

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

RE: Beware of Search Argument (SARG) Data Types

Rick Harker — Thu, 13 Jul 2006 09:25:00 GMT

Good article, but I disagree with the DATEDIFF/DATEADD statement "The first query can't possibly use an index and performs the DATEDIFF on every row in the table." used on the DateOfBirth example.

I believe this is because of the "select *", and not because of the DateDiff/DateAdd differences. (Also as a side note, both return a different amount of rows in my testing so I'd say they are not interchangeable). If you reduce the number of fields needed (maybe down to just CustomerID ) and they are also in an index ( DateOfBirth, CustomerID ), then that index can be used in both types of queries.

RE: Beware of Search Argument (SARG) Data Types

Brian Munier — Thu, 13 Jul 2006 08:53:00 GMT

Excellent.

I will even forward a link to this to the folks at Learning tree who developed their high performance DB course, and while they covered SARGs, they did not cover anything like this.

Thanks again.

RE: Beware of Search Argument (SARG) Data Types

ALZDBA — Thu, 13 Jul 2006 07:46:00 GMT

And probably INTEGER would be mutch faster and preferable because you have precision 0.

RE: Beware of Search Argument (SARG) Data Types

Paul Smith-221741 — Thu, 13 Jul 2006 07:22:00 GMT

Nice info to know. Fell foul of the conversion rules with SP4 when trying to use a variable devalred as Decimal(16,0) against a Primary key column defined as Decimal(12,0).

It managed to persuade SQL to do a table scan instead of an Index seek!

RE: Beware of Search Argument (SARG) Data Types

FritzDotNet — Thu, 13 Jul 2006 07:14:00 GMT

I have NEVER posted a reply to any article before. But this one deserves some praise.

I'm a developer and constantly bugging my dba for ways to make my apps runs faster and jump higher.

He has probably already read this article, but I'm going to go sit and show him that developers can be taught if properly beaten...

RE: Beware of Search Argument (SARG) Data Types

Bob Sellers — Thu, 13 Jul 2006 06:13:00 GMT

This was just the article I needed to print out roll up and beat my development team with! Thanks for the effort! I am looking forward to your views concerning ORM and Hibernate. I am not a big fan either. Currently fighting with the dev group about how they are wanting to use it.

RE: Beware of Search Argument (SARG) Data Types

ALZDBA — Thu, 13 Jul 2006 06:04:00 GMT

btw precedence has been changed with sql2000 sp4

Let's hope they don't do that again

RE: Beware of Search Argument (SARG) Data Types

Grant Fritchey — Thu, 13 Jul 2006 06:00:00 GMT

Great article. Thanks for all the work. While I was aware that avoiding implicit converts resulted in better query plans, I didn't know about the data precedence. That's a great piece of information the next time (and there will be one) I have the same issue (read: argument) with a developer who can't seem to understand why the crazy dba's want you to explicitly declare the correct data types in stored procedures instead of using strings for everything (because, after all, strings are eaiser, aren't they? And SQL Server will just implicitly convert them, so I don't have to worry about data types...).

RE: Beware of Search Argument (SARG) Data Types

ALZDBA — Thu, 13 Jul 2006 04:58:00 GMT

add this one to "10 things i wish my developers knew about sql"

Make sure you use the datatype of your columns ! because you avoid your dbms to translate it for you (each and every time again and again)

Beware of Search Argument (SARG) Data Types

DCPeterson — Tue, 27 Jun 2006 13:17:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpeterson/bewareofsearchargumentsargdatatypes.asp