On 07/19/2010 02:47 PM, Michael Bayer wrote:
> On Jul 19, 2010, at 1:53 PM, Adrian Price-Whelan wrote
>> Here is some more detailed information trying the query multiple ways:
>>
>> Piping the command into psql and writing to a tmp file takes 12 seconds (tmp
>> file is 241MB):
>>
>> time echo "SELECT spectrum.pk AS spectrum_pk, spectrum.ra AS spectrum_ra,
>> spectrum.dec AS spectrum_dec, spectrum.values AS spectrum_values,
>> spectrum.spectrum_header_pk AS spectrum_spectrum_header_pk,
>> spectrum.fiber_number AS spectrum_fiber_number, spectrum.inv_var AS
>> spectrum_inv_var, spectrum.and_mask AS spectrum_and_mask, spectrum.or_mask
>> AS spectrum_or_mask FROM spectrum JOIN spectrum_header ON spectrum_header.pk
>> = spectrum.spectrum_header_pk WHERE spectrum_header.filename =
>> 'spPlate-3586-55181.fits';" | psql spectradb -U postgres > tmp
>>
>> real 0m12.052s
>> user 0m2.501s
>> sys 0m0.274s
>>
>> engine.execute on the same query took ~6 seconds:
>> spectra = engine.execute("SELECT spectrum.pk AS spectrum_pk, spectrum.ra AS
>> spectrum_ra, spectrum.dec AS spectrum_dec, spectrum.values AS
>> spectrum_values, spectrum.spectrum_header_pk AS spectrum_spectrum_header_pk,
>> spectrum.fiber_number AS spectrum_fiber_number, spectrum.inv_var AS
>> spectrum_inv_var, spectrum.and_mask AS spectrum_and_mask, spectrum.or_mask
>> AS spectrum_or_mask FROM spectrum JOIN spectrum_header ON spectrum_header.pk
>> = spectrum.spectrum_header_pk WHERE spectrum_header.filename =
>> 'spPlate-3586-55181.fits';")
>>
> Call fetchall() on the result to get a better idea what's going on.
> [...]
>
Assuming fetchall() brings the execution time back to ~486 seconds, it
is likely your bottleneck is psycopg2's str-to-Decimal conversion. A big
part of this is that Python's Decimal class is written in pure Python.
You can override psycopg2's type conversion[1], but the only easy speed
increase I see is to leave the values as strings. Parsing them as floats
may help as well.
-Conor
[1] http://initd.org/psycopg/docs/faq.html#problems-with-type-conversions
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
