Disappointing SQL update performance - no stored procedures?

[Ryan Trollip <ry...@gmail.com>]

Hey all

For structured data read access is great, but large transactional updates
to data are very not great, for us. We are working on a single node larger
machine, so nothing fancy at all.
In working to trouble shoot this, our dev team has tried a large number of
different configurations over the past weeks and made good progress turning
performance, but frankly, it's still nowhere near what I would expect from
a standard SQL Server database with stored procedures, and it's using far
more memory.

One major issue is the back and forth with the client application. I've
done some reading up on using compute grid instead but found this older
article where they test the performance and for large inserts is many times
slower than a SQL stored procedure.
https://cs.ulb.ac.be/public/_media/teaching/ignite_2017.pdf

Is this still the case? or is there a better way to approach this that we
are missing? Would code written in a compute grid solve these performance
issues? are compiled stored procedures planned for the future?

Thanks
Ryan

Re: Disappointing SQL update performance - no stored procedures?

[Ryan Trollip <ry...@gmail.com>]

StephenThanks, the team is going through all of the performance guides for
a second time. We also understand that this operates on a version of the H2
database and are looking at the documentation around that also.
The performance bottleneck is for large transactions which comprise of,
what in a SQL DB would be, a number of select into statements. Selects with
joins, and an insert of a few thousand rows per transaction, with a dozen
of concurrent transactions.
We are running the client in spring boot, in the same JVM but not running
Ignite as compute node. Would that make any difference?
What we are trying tomorrow is bulk insert or "streaming" features, to see
if we can get better performance from that.
After a considerable amount of profiling, we also noticed garbage
collection resource challenges for 2nd and 3rd level cleanup casing large
delays, now and again, and are looking into forcing that in areas and
ensuring all objects are ready for cleanup as soon as possible.

On Thu, Jan 21, 2021 at 2:39 AM Stephen Darlington <
stephen.darlington@gridgain.com> wrote:

> This is the General Performance Tips guide: general-perf-tips
> <https://ignite.apache.org/docs/latest/perf-and-troubleshooting/general-perf-tips>
>
> There’s no one-size-fits-all solution, but as a general point, Ignite is
> optimised to work as a cluster. Operating a single server node isn’t where
> you get the best performance relative to other solutions.
>
> The compute grid *is* Ignite’s stored procedure. Using colocated compute
> is one of the key mechanisms for getting the best performance as it avoids
> copying data across the network.
>
> Regards,
> Stephen
>
> On 20 Jan 2021, at 19:23, Ryan Trollip <ry...@gmail.com> wrote:
>
> Hey all
>
> For structured data read access is great, but large transactional updates
> to data are very not great, for us. We are working on a single node larger
> machine, so nothing fancy at all.
> In working to trouble shoot this, our dev team has tried a large number of
> different configurations over the past weeks and made good progress turning
> performance, but frankly, it's still nowhere near what I would expect from
> a standard SQL Server database with stored procedures, and it's using far
> more memory.
>
> One major issue is the back and forth with the client application. I've
> done some reading up on using compute grid instead but found this older
> article where they test the performance and for large inserts is many times
> slower than a SQL stored procedure.
> https://cs.ulb.ac.be/public/_media/teaching/ignite_2017.pdf
>
> Is this still the case? or is there a better way to approach this that we
> are missing? Would code written in a compute grid solve these performance
> issues? are compiled stored procedures planned for the future?
>
> Thanks
> Ryan
>
>
>
>

Re: Disappointing SQL update performance - no stored procedures?

[Stephen Darlington <st...@gridgain.com>]

This is the General Performance Tips guide: general-perf-tips <https://ignite.apache.org/docs/latest/perf-and-troubleshooting/general-perf-tips>

There’s no one-size-fits-all solution, but as a general point, Ignite is optimised to work as a cluster. Operating a single server node isn’t where you get the best performance relative to other solutions.

The compute grid is Ignite’s stored procedure. Using colocated compute is one of the key mechanisms for getting the best performance as it avoids copying data across the network.

Regards,
Stephen

> On 20 Jan 2021, at 19:23, Ryan Trollip <ry...@gmail.com> wrote:
> 
> Hey all
> 
> For structured data read access is great, but large transactional updates to data are very not great, for us. We are working on a single node larger machine, so nothing fancy at all. 
> In working to trouble shoot this, our dev team has tried a large number of different configurations over the past weeks and made good progress turning performance, but frankly, it's still nowhere near what I would expect from a standard SQL Server database with stored procedures, and it's using far more memory. 
> 
> One major issue is the back and forth with the client application. I've done some reading up on using compute grid instead but found this older article where they test the performance and for large inserts is many times slower than a SQL stored procedure. 
> https://cs.ulb.ac.be/public/_media/teaching/ignite_2017.pdf <https://cs.ulb.ac.be/public/_media/teaching/ignite_2017.pdf>
> 
> Is this still the case? or is there a better way to approach this that we are missing? Would code written in a compute grid solve these performance issues? are compiled stored procedures planned for the future? 
> 
> Thanks
> Ryan