Performance of SQL functions vs. code functions

We're currently investigating the load against our SQL server and looking at ways to alleviate it. During my post-secondary education, I was always told that, from a performance standpoint, it was cheaper to make SQL Server do the work. But is this true?

Here's an example:

SELECT ord_no FROM oelinhst_sql

This returns 783119 records in 14 seconds. The field is a char(8), but all of our order numbers are six-digits long so each has two blank characters leading. We typically trim this field, so I ran the following test:

SELECT LTRIM(ord_no) FROM oelinhst_sql

T开发者_运维知识库his returned the 783119 records in 13 seconds. I also tried one more test:

SELECT LTRIM(RTRIM(ord_no)) FROM oelinhst_sql

There is nothing to trim on the right, but I was trying to see if there was any overhead in the mere act of calling the function, but it still returned in 13 seconds.

My manager was talking about moving things like string trimming out of the SQL and into the source code, but the test results suggest otherwise. My manager also says he heard somewhere that using SQL functions meant that indexes would not be used. Is there any truth to this either?

---

Only optimize code that you have proven to be the slowest part of your system. Your data so far indicates that SQL string manipulation functions are not effecting performance at all. take this data to your manager.

If you use a function or type cast in the WHERE clause it can often prevent the SQL server from using indexes. This does not apply to transforming returned columns with functions.

---

It's typically user defined functions (UDFs) that get a bad rap with regards to SQL performance and might be the source of the advice you're getting.

The reason for this is you can build some pretty hairy functions that cause massive overhead with exponential effect.

As you've found with rtrim and ltrim this isn't a blanket reason to stop using all functions on the sql side.

---

It somewhat depends on what all is encompassed by: "things like string trimming", but, for string trimming at least, I'd definitely let the database do that (there will be less network traffic as well). As for the indexes, they will still be used if you're where clause is just using the column itself (as opposed to a function of the column). Use of the indexes won't be affected whatsoever by using functions on the actual columns you're retrieving (just on how you're selecting the rows).

You may want to have a look at this for performance improvement suggestions: http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

---

As I said in my comment, reduce the data read per query and you will get a speed increase.

You said:

our order numbers are six-digits long so each has two blank characters leading

Makes me think you are storing numbers in a string, if so why are you not using a numeric data type? The smallest numeric type which will take 6 digits is an INT (I'm assuming SQL Server) and that already saves you 4 bytes per order number, over the number of rows you mention that's quite a lot less data to read off disk and send over the network.

Fully optimise your database before looking to deal with the data outside of it; it's what a database server is designed to do, serve data.

---

As you found it often pays to measure but I what I think your manager may have been referring to is somthing like this.

This is typically much faster

SELECT SomeFields FROM oelinhst_sql
WHERE
  datetimeField > '1/1/2011'
  and
  datetimeField < '2/1/2011'

than this

SELECT SomeFields FROM oelinhst_sql
WHERE
  Month(datetimeField) = 1
  and
  year(datetimeField) = 2011

even though the rows that are returned are the same