Thanks for your reply. The length matters as I am appending 100 rows at a
time in a sql statement. It is making very fast as compared to single sql
insert in For loop.
Copied the code below for reference. Here the list size is 100
Any better way to do this? Like I read about *bind*...not sure how I can do
it in Golang.
As shown below:
func StoreFileList(db *sql.DB, fileList []File) {
sql_PathInfo := `
INSERT OR IGNORE INTO path_info(Id,FilePath) VALUES`
sql_FileInfo := `
INSERT OR REPLACE INTO file_info(
PathId,
FileName,
FileSize,
IsDir,
IsExported,
Level,
ModTime
) VALUES `
valsPath := []interface{}{}
valsFile := []interface{}{}
for _, file := range fileList {
sql_PathInfo += "(?,?),"
sql_FileInfo += "((SELECT Id FROM path_info WHERE FilePath = ?),?, ?, ?, ?,
?, ?),"
valsPath = append(valsPath, nil, file.FilePath)
valsFile = append(valsFile, file.FilePath, file.FileName, file.FileSize,
file.IsDir, file.IsExported, file.Level, file.ModTime)
}
sql_PathInfo = sql_PathInfo[0 : len(sql_PathInfo)-1]
sql_FileInfo = sql_FileInfo[0 : len(sql_FileInfo)-1]
PathInfoStmt, err := db.Prepare(sql_PathInfo)
if err != nil {
panic(err)
}
fileInfoStmt, err := db.Prepare(sql_FileInfo)
if err != nil {
panic(err)
}
defer PathInfoStmt.Close()
defer fileInfoStmt.Close()
tx, err := db.Begin()
if err != nil {
panic(err)
}
_, err = tx.Stmt(PathInfoStmt).Exec(valsPath...)
_, err1 := tx.Stmt(fileInfoStmt).Exec(valsFile...)
if err != nil || err1 != nil {
if err != nil {
panic(err)
}
if err1 != nil {
panic(err1)
}
fmt.Println("doing rollback")
tx.Rollback()
} else {
tx.Commit()
}
}
On Tue, Feb 7, 2017 at 11:56 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 7 Feb 2017, at 5:36am, Niti Agarwal <n...@ionosnetworks.com> wrote:
>
> > Need to insert close to 10 Million records to sqlite3 in around 30 mins.
>
> This number of records requires so much space the temporary data will not
> fit inside a cache. Consider using a counter so that the transaction is
> ended and a new one begun every 1000 records. Or perhaps every 10000
> records. Try both ways and see which is faster.
>
> > Currently I am saving 100 Records under one transaction with below
> settings:
> >
> > PRAGMA synchronous = NORMAL;
> > PRAGMA journal_mode = WAL;
> > PRAGMA auto_vacuum = FULL;
> >
> > I read about SQLITE_MAX_SQL_LENGTH, Is it better to tune this value?
>
> The value SQLITE_MAX_SQL_LENGTH is about the length in text of one SQL
> command. I doubt it has any impact on your problem. You should probably
> leave it as it is.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
