Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Beware of Search Argument (SARG) Data Types Expand / Collapse
Author
Message
Posted Thursday, July 13, 2006 10:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:16 AM
Points: 1,035, Visits: 410

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



*****************/
Post #294189
Posted Thursday, July 13, 2006 10:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 13, 2007 9:21 AM
Points: 3, 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
Post #294191
Posted Thursday, July 13, 2006 11:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:16 AM
Points: 1,035, Visits: 410

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



*****************/
Post #294193
Posted Thursday, July 13, 2006 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 13, 2007 9:21 AM
Points: 3, 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
Post #294195
Posted Thursday, July 13, 2006 2:50 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 21, 2011 10:03 AM
Points: 577, Visits: 102
Great article, Don! This is extremely helpful....

Signature is NULL
Post #294257
Posted Thursday, July 13, 2006 11:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Great article


Post #294312
Posted Friday, July 14, 2006 4:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:05 AM
Points: 122, Visits: 167

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

 

Post #294388
Posted Friday, July 14, 2006 9:34 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 9:23 AM
Points: 649, Visits: 206
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



Post #294515
Posted Friday, July 14, 2006 9:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2007 2:33 PM
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.

 

Post #294629
Posted Monday, July 17, 2006 3:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 24, 2007 2:53 AM
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?
Post #294750
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse