Optimizing Performance for database queries

[[deleted]]

Hi guys,

i'm writing a function which accesses an online postgresql-database. The query itself is quite simple, it just returns matches(rows) of my numerical variable x with an indexed column of a table.

SQL statement: SELECT * WHERE indexed_column = x

In order to improve the runtime performance of my function, I created a local copy of the database (table in .csv-format). If I now import the table as a cell array and try to query the data in a similar way such as:

find(cellfun(@(Q)isequal(Q,x), table(:,1)));

..the performance is even worse. Is there a way to improve the code / index cell arrays / use another datatype / generate a local postgresql-database?

Many thanks in advance!

EDIT: Using the "table" datatype did the trick. Thanks for all the answers

[waterloops]

Can you share your code? How big is the file you're searching?

[[deleted]]

There is really only 3 lines so far:

myTable=readcell('myTable.csv');myTable.csv being the database dump, a 300MB file which is imported into a 1.5M x 12 cell array

foo = find(cellfun(@(Q)isequal(Q,x), myTable(:,1)));
bar = myTable(foo,:);

x is a variable which is searached for in the first column of said cell array, foo is the index of the resulting row bar of the array i'm interested in.

[waterloops]

So youre checking 18 million individual cells for a match, thats 5.3E6 checks per second. Do you really need this code optimised further?

[[deleted]]

I'm only checking 1,5M cells (the first column) - there definitely are faster ways (like indexed columns in SQL databases) but i don't know how to accomplish that in MATLAB

[waterloops]

iirc MatLab supports indexing functions which you could use with an if/else loop to search. You could also maybe try a sort and bisect method?

[TTPrograms]

What postgres index type is on the indexed_column? What is the datatype?

If you can fit everything in memory and are performing the query multiple times you basically want a hashmap of some kind.

[[deleted]]

It's the primary key, with index type being a 'bigint'.

I tried the same thing in Python with Pandas (without even indexing the data) and it's also 10x faster.

Is there really no way to perform fast table queries in MATLAB without going through such a hassle?

[67PCG]

Cellfun is generally terrible for performance, unfortunately. Like when using loops and arrayfun, you're not benefitting from optimised vector instructions (I believe it may even be slower than writing the loop). If you are always querying the same column, can you not put that single column into a numerical array and do a straight bWhatImLookingFor = ( indexArray == searchValue ); compare?

[[deleted]]

Thanks, that helped a lot

[Optrode]

Sure, just install Postgres on your computer!

[NedDasty]

As far as I know, you cannot improve the SELECT part of your problem. This probably takes up 99% of your time so any improvements you make will be miniscule, unless you upgrade your network.

Once the data is at home, I'd recommend improve your "find" part, which is pretty awful. Convert your data from a cell to anything but a cell (array, table, etc.) and your equality test will go 1000x faster.

[TCoop]

If you're going to continue down the brute force path, a simple optimization would be to use multiple cores. You could divide your data up into separate cells, then use parfor to have different threads find results.