Forum: Building VoltDB Applications

Post: Index for JSON field - performance for INSERT

Index for JSON field - performance for INSERT
Anton
Apr 22, 2015
Hi all

I created simple test for performance (many operators INSERT within Java StoredProcedure) and found out following (VoltDB version 5.1 Enterprise - trial):
For example, the test with JSON field is executed 25 seconds. JSON contains many fields - 360. Documentation says that if we have several hundreds columns performance will be bad. Ok - I use JSON.
But if I add 5 indexes for 5 fields in JSON (for searching) my test lasts 100 second.

I tried to use wide table (360 columns) - the tests lasts about 65 seconds (more than JSON-version). But if I add even 10 indexes - I get the same time - 65 seconds.

Is it normal and I have to accept such behaviour or there are some tricks for JSON ?

Thank you.
bballard
Apr 23, 2015
Hi Anton,

It's hard to say without testing a specific case whether it's better to use JSON or regular columns. If you have several hundreds of columns, ideally the most-used or most-searched columns could be regular columns, and the least-used columns could be collected in a JSON field.

The I/O and parsing tasks can be more resource-intensive with so many columns. Using regular columns, you pay this price more upon insertion. Using JSON reduces the parsing. But if you are using JSON functions in indexes, then you pay the price for that upon insertion, which could actually be higher than the cost of inserting regular columns. It's hard to say without testing a specific scenario.

Also, the client can be a bottleneck when sending requests that have several hundreds of parameters. I have found that while testing narrow tables a single-threaded asynchronous java client can push hundreds of thousands of requests per second, but with very wide tables it may be a lot slower and benefit much sooner from multiple threads, multiple voltdb.client.Client instances, and also running multiple processes.

If you would like help on a proof of concept or evaluation, you can contact sales@voltdb.com and we would be happy to help in your testing and collaborate to help you find the optimum design.

Best regards,
Ben
Anton
Apr 24, 2015
Hi Ben,

I thought about the same shema as you, but wanted to make sure that it is normal situation with index for JSON. Thank you for answer.