Geek asks and answers

Executing SQL Functions and Code Functions

We are currently studying the load on our SQL server and are exploring ways to eliminate it. During my post-secondary education, I was always told that, in terms of performance, it was cheaper to get SQL Server to do the job. But is this true?

Here is an example:

SELECT ord_no FROM oelinhst_sql

This returns 783119 records in 14 seconds. The field is char(8), but all of our serial numbers are six digits long, so each of them has two empty characters. Usually we crop this field, so I performed the following test:

SELECT LTRIM(ord_no) FROM oelinhst_sql

This returned 783119 records in 13 seconds. I also tried another test:

SELECT LTRIM(RTRIM(ord_no)) FROM oelinhst_sql

There is nothing to focus on the right, but I tried to find out if there is overhead in the simple action of calling the function, but it still returns after 13 seconds.

, SQL , . , - , SQL , . ?

+3
5

, , , . , SQL . .

cast WHERE, SQL- . .

+3

(UDF), SQL , .

, , .

rtrim ltrim, SQL.

+1

, : ", ", , , ( ). , , - where, ( ). , , ( , ).

: http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

0

, , , .

:

, , , ? , 6 , - INT ( SQL Server), 4 , , , .

Fully optimize your database before looking at data outside it; this is what is intended for the database server to serve the data.

0
source

As you often find, you can count on a measurement, but I think your manager might have in mind that it was something like this.

It is usually much faster.

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

than that

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

even if the returned rows are the same

0
source

More articles:


All Articles