On Jul 19, 2010, at 3:55 PM, Adrian Price-Whelan wrote:
> First off, thanks for your quick replies!
>
> I will look into this, but I can tell you that the arrays are strictly
> numbers and the array columns are type numeric[]
so its going to be doing that somewhat inefficient "isinstance(list)" thing you
see below, this appears to be how it handles arrays of arbitrary numbers of
dimensions. This could be optimized if the ARRAY type accepted some clues as
to how many dimensions are present. Seems a little suspicious that the
slowdown would be 8000% though.
>
> Thanks again,
> Adrian
>
> On Jul 19, 2010, at 3: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.
>> Here's the source of the ARRAY type:
>>
>> def result_processor(self, dialect, coltype):
>> item_proc = self.item_type.result_processor(dialect, coltype)
>> if item_proc:
>> def convert_item(item):
>> if isinstance(item, list):
>> return [convert_item(child) for child in item]
>> else:
>> return item_proc(item)
>> else:
>> def convert_item(item):
>> if isinstance(item, list):
>> return [convert_item(child) for child in item]
>> else:
>> return item
>> def process(value):
>> if value is None:
>> return value
>> return [convert_item(item) for item in value]
>> return process
>>
>> as you can see, if your ARRAY is of a Unicode type or similar, convert()
>> must be called on each item (only happens during fetch). This is a likely
>> cause of the slowdown and you should consider what kind of converters you're
>> using on your ARRAY members.
>>
>>
>> --
>> 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.
>>
>
> --
> 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.
>
--
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.
