|
|
Posted by -Lost on 08/21/06 08:26
"Juliette" <jrf_no_spam@jokeaday.net> wrote in message
news:44e96b66$0$9808$dbd49001@news.wanadoo.nl...
> -Lost wrote:
>> I hope that is not too vague of a question.
>>
>> I have begun taking a database class in school, which is where this question spawned
>> from.
>>
>> If I were to use the aggregate or scalar functions for example, are they preferred over
>> using code (such as PHP, VB/ASP et cetera)?
>>
>> An example of this is:
>>
>> SELECT AVG(column_name) FROM table_name
>>
>> ...whereas with PHP it would be:
>>
>> // retrieve the entire column via a query, then
>>
>> function average($_array_of_average_inputs)
>> {
>> return array_sum($_array_of_average_inputs) / count($_array_of_average_inputs);
>> }
>>
>> $avg = average($average_array);
>>
>> ...and on and on. Or something like:
>>
>> SELECT UCASE(column_name) FROM table_name
>>
>> ...whereas with PHP it would be:
>>
>> // retrieve the entire column via a query, then
>>
>> $final_result = strtoupper($ascii_results_of_query);
>>
>> So, sorry if I rambled on a bit. As per the norm I am tired as hell and its 4AM. I
>> just want to figure out (or learn) which is faster. Do the operation via SQL if
>> possible and then store it, or issue vanilla queries and do everything myself with
>> code.
>>
>> Thanks and sorry for the incoherence (if any exists)!
>>
>> -Lost
>
>
> No one answer... it depends on numerous things, amongst which are:
>
> 1) sql query complexity - I presume you just gave the above as simple examples
> 2) expected number of rows which may be returned
> 3) sql table setup - have indexes been defined and are they efficient ?
> 4) will you be doing other processing of the results within php ?
> etc etc
>
> Start by reading up on the SQL EXPLAIN syntax and meaning of the results you get with
> that. DevShed has recently published some educational tutorials about this.
>
> Next start doing benchmarks on various implementations as everything depends on how you
> implement it.
Right, I just popped those off of the top of my weary head.
Hrmm... so the amount of rows returned can affect the overall efficiency. I wonder if it
would be in favor of the SQL or in favor of code? My guess would be in favor of SQL
(being faster).
With efficient indexes in place, it sounds like it would speed up the overall query, but I
have no clue which it would favor.
Requiring the data further in the application... I am not too sure how that would make
speed apparent. Perhaps I guess if I were to use loads of stored results, then code would
be faster.
Anyway... thanks for that expedient reply! I am looking for those DevShed articles right
now (bookmarking them, and then I am heading to bed).
The benchmarks sadly will have to wait until I get a spare system to load with a few
installs of Linux. Currently stuck on my archaic laptop running Windows XP "Getting Ready
To Die" Edition.
Thanks!
-Lost
Navigation:
[Reply to this message]
|