GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator
INSERTs with default values are too slow
- Dominant language
- C++
- Stars
- 334
- Forks
- 77
- Avg merge
- 8m
- Merged PRs (30d)
- 2
Description
I noticed that when inserting into a column with a default value set, omitting the column's value in the INSERT statement (so that the default value is used) results in extremely slow performance. Compared to explicitly specifying the column's value during INSERT, it seems to be more than 10 times slower, although the exact slowdown is not consistent.
```
# table
CREATE TABLE Singers (
SingerId STRING(36),
Name STRING(MAX) default (\"foo\"),
Age INT64 default (20)
) PRIMARY KEY (SingerId)
```
```
# insert pattern A (slow)
id, _ := uuid.NewRandom()
spanner.Insert("Singers", []string{"SingerId"}, []interface{}{id.String()})
# insert pattern B (fast)
id, _ := uuid.NewRandom()
spanner.Insert("Singers", []string{"SingerId", "Name", "Age"}, []interface{}{id.String(), "foo", 20})
```
Click to expand sample code
```
package main
import (
"context"
"fmt"
"log"
"os"
"strings"
"time"
"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
"cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
instance "cloud.google.com/go/spanner/admin/instance/apiv1"
"cloud.google.com/go/spanner/admin/instance/apiv1/instancepb"
"github.com/google/uuid"
_ "github.com/googleapis/go-sql-spanner"
"google.golang.org/api/option"
"google.golang.org/grpc"
"google.golang.org/grpc/credentials/insecure"
)
func main() {
ctx := context.Background()
setup(ctx)
client, _ := spanner.NewClient(ctx, "projects/emulator-project/instances/emulator-instance/databases/emulator-database", option.WithoutAuthentication())
defer client.Close()
times := 10
num := 10000
var start time.Time
// run with default values
fmt.Println("run with default values")
for i := 0; i < times; i++ {
var m []*spanner.Mutation
for i := 0; i < num; i++ {
id, _ := uuid.NewRandom()
m = append(m, spanner.Insert("Singers", []string{"SingerId"}, []interface{}{id.String()}))
}
start = time.Now()
client.Apply(ctx, m)
fmt.Printf("inserts: %d, times: %d, elapsed: %v\n", num, i, time.Since(start))
}
// run with specified values
fmt.Println("run with specified values")
for i := 0; i < times; i++ {
var m []*spanner.Mutation
for i := 0; i < num; i++ {
id, _ := uuid.NewRandom()
m = append(m, spanner.Insert("Singers", []string{"SingerId", "Name", "Age"}, []interface{}{id.String(), "foo", 20}))
}
start = time.Now()
client.Apply(ctx, m)
fmt.Printf("inserts: %d, times: %d, elapsed: %v\n", num, i, time.Since(start))
}
}
func setup(ctx context.Context) {
emulatorHost, _ := os.LookupEnv("SPANNER_EMULATOR_HOST")
// create instance admin client
instanceAdminClient, _ := instance.NewInstanceAdminClient(ctx,
option.WithEndpoint(emulatorHost),
option.WithGRPCDialOption(grpc.WithTransportCredentials(insecure.NewCredentials())))
defer instanceAdminClient.Close()
// create emulator instance
instanceReq := &instancepb.CreateInstanceRequest{
Parent: "projects/emulator-project",
InstanceId: "emulator-instance",
Instance: &instancepb.Instance{
Config: "emulator-config",
DisplayName: "Emulator Instance",
NodeCount: 1,
},
}
_, err := instanceAdminClient.CreateInstance(ctx, instanceReq)
if err != nil {
if strings.Contains(err.Error(), "already exists") {
log.Println("instance already exists")
} else {
panic(err)
}
}
// create database admin client
databaseAdminClient, _ := database.NewDatabaseAdminClient(ctx,
option.WithEndpoint(emulatorHost),
option.WithGRPCDialOption(grpc.WithTransportCredentials(insecure.NewCredentials())))
defer databaseAdminClient.Close()
// create database
dbReq := &databasepb.CreateDatabaseRequest{
Parent: "projects/emulator-project/instances/emulator-instance",
CreateStatement: "CREATE DATABASE `emulator-database`",
}
_, err = databaseAdminClient.CreateDatabase(ctx, dbReq)
if err != nil {
if strings.Contains(err.Error(), "already exists") {
log.Println("database already exists")
} else {
panic(err)
}
}
// cleanup old tables
dropTableStmt := "DROP TABLE Singers"
op, err := databaseAdminClient.UpdateDatabaseDdl(ctx, &databasepb.UpdateDatabaseDdlRequest{
Database: "projects/emulator-project/instances/emulator-instance/databases/emulator-database",
Statements: []string{dropTableStmt},
})
if err != nil {
if strings.Contains(err.Error(), "not found") {
log.Println("table not found")
} else {
panic(err)
}
}
if op != nil {
op.Wait(ctx)
}
// create tables DDL
createTableStmt := "CREATE TABLE Singers (SingerId STRING(MAX), Name STRING(MAX) default (\"foo\"), Age INT64 default (20)) PRIMARY KEY (SingerId)"
op, _ = databaseAdminClient.UpdateDatabaseDdl(ctx, &databasepb.UpdateDatabaseDdlRequest{
Database: "projects/emulator-project/instances/emulator-instance/databases/emulator-database",
Statements: []string{createTableStmt},
})
op.Wait(ctx)
}
```
logs
```
run with default values
inserts: 10000, times: 0, elapsed: 11.975785053s
inserts: 10000, times: 1, elapsed: 12.343125291s
inserts: 10000, times: 2, elapsed: 12.261675672s
inserts: 10000, times: 3, elapsed: 11.776418881s
inserts: 10000, times: 4, elapsed: 14.155175691s
inserts: 10000, times: 5, elapsed: 11.782318013s
inserts: 10000, times: 6, elapsed: 13.715314927s
inserts: 10000, times: 7, elapsed: 16.505922306s
inserts: 10000, times: 8, elapsed: 11.992866841s
inserts: 10000, times: 9, elapsed: 15.93990089s
run with specified values
inserts: 10000, times: 0, elapsed: 172.457302ms
inserts: 10000, times: 1, elapsed: 193.484206ms
inserts: 10000, times: 2, elapsed: 173.783946ms
inserts: 10000, times: 3, elapsed: 172.511135ms
inserts: 10000, times: 4, elapsed: 189.831526ms
inserts: 10000, times: 5, elapsed: 218.340877ms
inserts: 10000, times: 6, elapsed: 173.787363ms
inserts: 10000, times: 7, elapsed: 183.582263ms
inserts: 10000, times: 8, elapsed: 182.347453ms
inserts: 10000, times: 9, elapsed: 186.916689ms
```
This issue has been observed in version 1.5.24.
## Request
Is it possible to achieve the same level of performance as when specifying the default value explicitly?
I understand that since this is an emulator, it's important that it satisfies the interface, and performance is a lower priority. However, because it's also used as a test database in some cases, it would be great if the performance could be improved.
Contributor guide
Assessment
This issue has not been assessed yet.