HDFStore.select slowed by decode even when using columns=

[wabu]

I realized when profiling a slow select (200% more wall-time as direct pytables call and high memory usage) that most of the time is spend inside bytes.decode called by _unconvert_strings_array, even when selecting only int64 columns. It seems spend time and memory to decode string that are never returned.

I'm using python 3.3 and latest pandas (commit 2d2e8b5).

I gladly get back with more details if needed.

[jreback]

can you provide a reproducable example?

[jreback]

try making the columns that you are using date_columns=list_of_columns_I_want when you append; that way ONLY those will be selected, see here

[wabu]

Thanks for your reply!

I tested it with and without data_columns, nothing changes. Here's an example:

h5 = pd.HDFStore('test.hdf5')
data = pd.DataFrame(np.random.rand(1000000,2), columns=['a', 'b'])
data['b'] = data['b'].apply(str)
del h5['test']
h5.append('test', data)
%prun h5.select('test', columns=['a'])

just the existence of a string column will make it slow because of the decode, even if the decoded data is not needed. Also using data_columns = ['a'] and with a query on a, nothing changes about the 100000 calls to decode :/

[jreback]

pytables is row-oriented, so it returns a full row when selected; this is then reindexed with the columns, so this is the expected behavior.

however, can be easily fixed, give this PR a try:
#5448

The slower method is used when the utf-8 decoding fails (which for example non-ascii like data, e.g. stuff that str can convert).

Here are several other ways to deal with this:

• If you are selecting the entire table and not using query capabilities, then use a 'fixed' store instead.
• you can use read_column to only read the column that you want from the table

[wabu]

Thanks, this helps allot.

• the conversion takes have the time now
  Furthermore, I had a look at the following options
• select_column/read_column also is a good options, except that it does not allow a where clause, but it's super fast compared to the read.
• I also try out using append_to_multiple/select_from_mutliple to split the table up for some column
• Furthermore a lookup table for strings is an option here for us, so the main table only uses ints ...

[jreback]

see also #4454 this is the best way to do this
but will require some effort to build (welcome collab on this!)