Recently, when communicating with a big guy, I mentioned the problem of SQL query.

His idea is to query data with the most primitive database driver without ORM. The benefits of doing so are twofold

• Express SQL semantics clearly for later optimization. Especially in report, data analysis and other complex SQL.
• Reduce useless encapsulation and improve program performance.

I didn't have any feelings at first. After all, ORM is friendly to programmers. It will be easier for me to use ORM, but I don't know the specific differences. So I did a simple test, using sqlx and db.v3 respectively (many people may not have heard of this DB package, which is completely personal preference).

Prepare region table jz_places with 3600 pieces of data

CREATE TABLE `jz_places` (
  `placeid` mediumint(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sx` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `order` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Data creation time',
  PRIMARY KEY (`placeid`),
  KEY `name` (`name`),
  KEY `order` (`order`),
  KEY `idx_updatetime` (`updatetime`)
) ENGINE=InnoDB AUTO_INCREMENT=49987 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

sqlx code

func sqlxQuery() error {

	db, err := sqlx.Connect("mysql", "root:123456@tcp(127.0.0.1:3306)/mall?charset=utf8")
	defer db.Close()
	if err != nil {
		log.Fatalln(err)
	}
	var placesList []places
	return db.Get(&placesList, "SELECT * FROM jz_places WHERE name like ?", "Tricyclic%")

}

func BenchmarkSqlxQuery(b *testing.B) {
	b.N = 3000
	for i := 0; i < b.N; i++ {
		_ = sqlxQuery()
	}
}

db.v3 code

func dbQuery() error {
	setting := mysql.ConnectionURL{
		User:     "root",
		Password: "123456",
		Host:     "127.0.0.1",
		Database: "mall",
		Options:  map[string]string{"charset": "utf8"},
	}
	var placesList []places
	db, err := mysql.Open(setting)
	defer db.Close()
	if err != nil {
		log.Fatalln(err)
	}
	return db.Collection("jz_places").Find("name like ?", "Tricyclic%").All(&placesList)
}

func BenchmarkDBQuery(b *testing.B) {
	b.N = 3000
	for i := 0; i < b.N; i++ {
		_ = dbQuery()
	}
}

Let's look at the final result

goos: windows
goarch: amd64
pkg: mall/cmd
BenchmarkSqlxQuery-6   	    3000	   2118054 ns/op	    8850 B/op	      64 allocs/op
PASS
ok  	mall/cmd	6.695s
Success: Benchmarks passed.

goos: windows
goarch: amd64
pkg: mall/cmd
BenchmarkDBQuery-6   	    3000	   3535622 ns/op	   30020 B/op	    1054 allocs/op
PASS
ok  	mall/cmd	10.966s
Success: Benchmarks passed.

The difference between each connection query and closing is about 1ms. I think gorm and xorm will have the same situation. If you are in a high-performance scenario, using only SQL is also a good choice.