Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
 Posted - 2005-02-02 : 03:57:14
|
| I just want to know...I have a one page SQL statement that uses purely SQL with no functions. The other one is a 3/4 page that uses many functions, let say about 35% of the statement is composed of functions. Both have the same usage for my report.Now, which is much faster between the two? 1. A 1 whole page of pure SQL or...2. A 3/4 page with 35% of it uses functions.Which is which?please state relevant issues why? TNX... :DWant Philippines to become 1st World COuntry? Go for World War 3... |
|
|
BammBamm
Starting Member
9 Posts |
Posted - 2005-02-02 : 04:05:40
|
| Depends... What kind of functions (custom DLL or out of the box) and what are they performing?In general, I would presume that usage of the supported functions in the package would be better used/optimized than the overhead required to replicate the same effect w/out using the functions. |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-02 : 07:55:54
|
If the functions are UDF, I would almost guarantee that the whole page of SQL would be faster. However, length of SQL script is largely irrelevant. What is important is what is it doing.A complex join may be much longer than a cursor that performs the same thing. But the join is always going to be faster. (never seen a counter example)EDIT: A point of interest. A few months ago, I took one of our relatively complex stored procedures (ran in about 10-15 minues) and removed all of the UDFs. It now runs in about half the time (5-7 minutes).Corey "If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2005-02-02 : 19:16:48
|
| 50% of the 35% uses built-in SQL functions. Would it be a performance issue?Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-02-02 : 19:59:06
|
| There's an easy way to figure this out.Put the query that uses the functions and the one that doesn't in the same QA window and run them. Be sure to turn on "show execution plan" so that you can see the "query cost." You should the "query cost relative to the batch). The lower the cost, the faster / better the query.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
Query performance