Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Beware of Search Argument (SARG) Data Types


Beware of Search Argument (SARG) Data Types

Author
Message
DCPeterson
DCPeterson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1137 Visits: 432

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.



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

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
David Kilzer
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
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#urlFormat

Note 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
DCPeterson
DCPeterson
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1137 Visits: 432

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.



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

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
David Kilzer
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
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
Calvin Lawson
Calvin Lawson
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 102
Great article, Don! This is extremely helpful....

Signature is NULL
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 445
Great article



JP de Jong-202059
JP de Jong-202059
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 172

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
Philip Kelley
Right there with Babe
Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)

Group: General Forum Members
Points: 721 Visits: 232
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
Shawn-265447
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9

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
Alexander Yuryshev
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search