3 out of #100DaysOfCode - Storing the data


Written by Guillaume Moigneu

Based on the Previous post, I am now using SQLite to store my fixtures data for the attributes.

While generating a simple User entity with a Firstname and a Lastname is fast (<5ms), creating a million records is taking forever - approximately one minute - and killing my IOPS. A quick ab test on 100 entities show that I’m saturating my laptop resources at around 8 requests per second. That’s good but it can definitely be better!

This is simply due to the fact that for each entity that needs to be populated, I’m executing one SQL query for each attribute. And even if SQLite is pretty fast, it definitely slows the whole thing down.

So let’s refactor this. Instead of populating my attributes on the fly, the first step will be to load the whole SQL tables in slices and just use these to create each entity:

    // Load required locales values
	firstnames := make(map[string][]models.Firstname)
	lastnames := make(map[string][]models.Lastname)
	f := []models.Firstname{}
	l := []models.Lastname{}
	for _, locale := range locales {
		q := tx.Where("locale = ?", locale.Code)
		if len(c.Param("gender")) > 0 {
			q.Where("gender = ?", c.Param("gender"))
		firstnames[locale.Code] = f

		q = tx.Where("locale = ?", locale.Code)
		lastnames[locale.Code] = l

	// Generate the results
	users := make([]User, count)
	for i := 0; i < count; i++ {
		userLocale := locale
		if len(userLocale.Code) < 1 {
			userLocale = locales[rand.Intn(len(locales))]

		firstname := firstnames[userLocale.Code][rand.Intn(len(firstnames[userLocale.Code]))]
		lastname := lastnames[userLocale.Code][rand.Intn(len(lastnames[userLocale.Code]))]

		users[i] = User{
			Firstname: firstname.Name,
			Lastname: lastname.Name,
			Fullname: firstname.Name + " " + lastname.Name,
			Gender: firstname.Gender,
			Locale: userLocale.Code,

As you can see above, I’m using a map of value slices. This is because I need to be able to pick locale-coherent value. A French firstname should be generated along a French lastname.

Results are promising!

INFO[2020-01-06T07:39:41-08:00] /users/?count=1000000 content_type=application/json db=27.136714ms duration=2.363137643s human_size="106 MB" method=GET params="{\"count\":[\"1000000\"]}" path="/users/?count=1000000" render=2.011787486s request_id=19004ed811fc5fad3424-89f7d5f3401547412ec8 size=106079152 status=200

Generating a 100Mb response with 1 million entities takes only 2.36 seconds!

1000 entities are generated just in 24 ms!

INFO[2020-01-06T07:46:35-08:00] /users/?count=1000 content_type=application/json db=23.086011ms duration=24.673856ms human_size="106 kB" method=GET params="{\"count\":[\"1000\"]}" path="/users/?count=1000" render="888.997µs" request_id=c4e8f96795d38750c54e-ba74889b2b7150bfb26d size=106174 status=200

An ab test gives now 40 requests per second. Better than the 8 previous one but still not optimal.

But out of these 24ms, 23 are spent in the database! The next step will be to refactor my Buffalo app to not load that data on the request/action but during the init phase of my app.

See the next article: 4 out of #100DaysOfCode - Data singleton