Mobile version of this page
Save this page in del.icio.usHonesty and advertising on Gizmodo
Next:
University results
Related blogs
PostgreSQL, MySQL or SQLiteLoadtesting this site and compare with static Apache
Adding a year in PostgreSQL
Creating a user for postgresql
Fastest way to uniqify a list in Python
Sorting transform function in PostgreSQL
Just Oracle and IBM?
pg_class to check if table exists
Running simple SQL commands on the command line
"Clever" date formatting accessibility
Date formatting in Python or in PostgreSQL (part II)
Integer division in programming languages
List of casts in PostgreSQL
ALTER TABLE patch
Quick PostgreSQL optimization story
Gzip and Slimmer optimization anecdote
rfc822() vs. rfc1123_date()
Nice date input
Related by category
Date formatting in python or in PostgreSQL
formatting, date formatting, postgresql, benchmark, timestamp, datetime, datetime module, strftime, formatted, to_char
20th of July 2004
I deviced a very simple benchmark through Zope as the web server. Two SQL Select statements that draws from a dummy database table which contains a table with loads of dummy entries with a timestamp field.
Conclusion is that Python's DateTime module is considerably slower than PostgreSQL's builtin date formatting function.
On the first experiment, I select the timestamp as a string and use Python's DateTime module to convert the date to a DateTime object then I apply the strftime() function to get the date printed in a formatted fashion (e.g. 04 July 02 20). The SQL looks like this:
FROM test_datetime_entries;
On the DTML output I apply the DateTime module with code like this:
On the second experiment I used PostgreSQL's equally useful to_char() function to do the formatting in the SQL command so that formatted format is already prepared. The SQL looked like this:
TO_CHAR(time_stamp,'YY Mon DD SS') AS time_stamp_formatted,
FROM test_datetime_entries;
and it's corresponding DTML much simpler this time:
Then I had a script that randomly stress tests either experiment. After about 5000 iterations the following average fetch times were noted:
Using DateTime module |
Using to_char() SQL function |
|---|---|
| 3.556 seconds | 1.201 seconds |
Why is this important?
Generally, using the power SQL is better than using a programming language that has to work with the data because it's faster. But as a pattern it's less good. The database is responsible for reading and writing data, not to fiddle with the presentation; that's what you have the programming language for. These simple results prove that it's worth breaking that pattern and exploiting the SQL power more.
gracias!!! me sirvio de algo este ejm