Comments (10)
This would be awesome, when i think on this one i'm thinking in something like:
err := tx.BatchSave(&records)
...
I would love to discuss if this sounds good, i don't usually do batch insert but wanted to add those 5 cents to the discussion
from pop.
just curious as to the status of this. I'm not sure if the examples below are things already considered - but I would like to help(or try to help) move this forward. The suggestions given below currently only apply to dialect_postgres
- but I would have to look in both dialect_cockroach
and dialect_mysql
to see if this is possible.
// Upsert - using on conflict clause of pg
db.Upsert(&records)
// RAW SQL follows the format of
// INSERT INTO %s(%s) VALUES %s ON CONFLICT (%s) DO UPDATE %s WHERE %s
// Updates
db.Update(&records)
// RAW SQL follows the format of
// UPDATE %s SET %s FROM (VALUES%s) AS %s(%s) WHERE %s
of course we won't have anything to compare it to - if we can't benchmark and improve the single Create
calls currently implemented - so that would come first. I could work on a prototype so that it can be considered as a feature or not and people have a clear idea of it.
from pop.
Hi @vincentchu
Yes, the issue is still open, and I think it is definitely nice if we improve it!
I quickly read your gist and I like the approach. The method may work for all supported engines. Just one concern is the eager mode. Currently, the eager mode is supported for
Create()
(not fully stable) but I think improving the method for the bulk inserting with eager mode support could be challenging.One possible way to integrate the gist into Pop could be making the method switch using the eager mode, so it uses the existing way for eager mode and uses your improvement for non-eager mode.
Any idea?
@sio4 Thanks for these notes. At least internally here in my company, we distinguish between two use cases:
.Create()
which supportsEager
, etc.. and we typically use for creating small (1-50) numbers of itemsbulkinsert.Insert(...)
which supports fast insertion of many (50+) but does not support anyEager
functions
We could do something similar with pop
by adding a pop.BulkInsert
or pop.BulkCreate
method where Eager
was explicitly not supported and the tradeoff being much faster inserts. I think this makes sense because Eager
will be really hard to support for bulk inserts- when we do bulk insertion of nested models, we've typically used bulk inserts to INSERT the parent models, then aggregate the nested models before bulk inserting those.
from pop.
Related issue from the previous repo: https://github.com/markbates/pop/issues/185
Here some points to work on it:
- https://godoc.org/github.com/lib/pq#hdr-Bulk_imports
- For others DBs, we can improve this case reusing prepared statements.
from pop.
The linked doc looks like it will work for the CockroachDB implementation as well. (https://godoc.org/github.com/lib/pq#hdr-Bulk_imports)
(CockroachDB docs for reference: https://www.cockroachlabs.com/docs/stable/insert.html#insert-multiple-rows-into-an-existing-table )
from pop.
Hi, at gobenchorm, I used following code for benchmarking BulkInsert:
ms = make([]PModel, 0, 100)
for i := 0; i < 100; i++ {
ms = append(ms, *NewPModel())
}
if err := popdb.Create(&ms); err != nil {
fmt.Println(err)
b.FailNow()
}
Bu it was so time consuming that I had to disable benchmarking for BulkInsert. How can I make it better?
from pop.
@derkan great question! I think it's time we, as a community, start looking at the performance of Pop and trying to improve it. Here's how you can help. Start by opening an issue saying Pop needs benchmarking and profiling so it's performance can be improved.
Then, encourage someone out there, or if you want to do it even better, start adding some benchmarking and profiling results to a new ticket that says "this method needs to be improved, here are the metrics".
Then, people can come along, grab a ticket, work on that one method, and before we know it Pop is humming along.
from pop.
Since it's not explicitly mentioned being able to do a bulk delete should also be part of this.
from pop.
Is this still an open issue? In my app I wrote a small add-on library that will do bulk inserts using a prepared statement that would insert multiple records at a time. It leads to a pretty big improvement in INSERT
times, especially for large numbers of records.
Though it's in our app currently as a library external to pop
, it does use certain pop
concepts like Model
to introspect on the passed in rows to create the db query.
n, err := bulkinsert.Insert(tx, rowsToInsert) // n is the number of records inserted
A brief sketch is here (excludes tests):
https://gist.github.com/vincentchu/e56da652f012b357779a544c1e3831d1
If this is interesting to people I could use some help and pointers on how to get this into the actual pop
library.
from pop.
Hi @vincentchu
Yes, the issue is still open, and I think it is definitely nice if we improve it!
I quickly read your gist and I like the approach. The method may work for all supported engines. Just one concern is the eager mode. Currently, the eager mode is supported for Create()
(not fully stable) but I think improving the method for the bulk inserting with eager mode support could be challenging.
One possible way to integrate the gist into Pop could be making the method switch using the eager mode, so it uses the existing way for eager mode and uses your improvement for non-eager mode.
Any idea?
from pop.
Related Issues (20)
- feature proposal: add support to oracle DB HOT 1
- bug: transaction panic recovery swallows panic message HOT 3
- feature: log transaction/connection ID to track SQL executions for a request
- feature: Read/Write routing to different endpoints HOT 2
- feature: improve performance if the element of the Value of *Model does not implement the AfterFindable interface. HOT 4
- bug: DB connection locks up under load calling DB.Create(), regression in 6.1.0 vs 6.0.8 HOT 8
- feature: parallelize association loading HOT 2
- process: update supported database versions
- bug: sqlite3 testing on Windows + go 1.17 failing sometimes HOT 3
- bug: incorrect error handling
- Need a way to update stale pop connecton configs before pop reconnections a failed connectionfeature: HOT 6
- feature: upgrade jackc/pgx to v5 HOT 2
- feature: Dialect for Cassandra HOT 3
- feature: DB connection timeout HOT 2
- bug: "cannot parse UUID" on models.DB.Create HOT 1
- feature: context plumbing HOT 2
- bug: SQL connection leak when using EagerPreload HOT 4
- Slack Channel Not Working HOT 3
- is this project dead? HOT 5
- bug: Need to wrap error HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pop.