Gorm Query taking longer time than usual.

Query taking longer time than usual.

Hi I am using mentioned function to connect database using gorm, instrumented with apm.

DB_SSL_MODE=disable DB_MAX_IDLE_CONN=25 DB_MAX_OPEN_CONN=100 DB_MAX_CONN_AGE_IN_MINUTES=30

import (
postgres "go.elastic.co/apm/module/apmgormv2/driver/postgres"
)

var DB *gorm.DB

func ConnectDatabase() {
    if err := envconfig.Process("LIST", &cfg); err != nil {
        Util.Log.Fatalf("parse environment variables", err)
    }
    host := cfg.DBHost
    user := cfg.DBUser
    password := cfg.DBPass
    dbname := cfg.DBName
    port := cfg.DBPort
    sslmode := cfg.DBSSLMode
    dsn := fmt.Sprintf(Util.DSN, host, user, password, dbname, port, sslmode)
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Info)})
    if err != nil { 
    }

    sqlDB, err := db.DB()

    // SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
    maxIdleConnection, _ := strconv.Atoi((*Util.ConfigValues)[Util.DB_MAX_IDLE_CONN])
    sqlDB.SetMaxIdleConns(maxIdleConnection)

    // SetMaxOpenConns sets the maximum number of open connections to the database.
    maxOpenConnection, _ := strconv.Atoi((*Util.ConfigValues)[Util.DB_MAX_OPEN_CONN])
    sqlDB.SetMaxOpenConns(maxOpenConnection)

    // SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
    maxConAge, _ := strconv.Atoi((*Util.ConfigValues)[Util.DB_MAX_CONN_AGE_IN_MINUTES])
    sqlDB.SetConnMaxLifetime(time.Duration(maxConAge) * time.Minute)

        DB = db
}

func GetOffer(offersList *[]Entity.OfferEntity, country string, key string, ctx context.Context) (err error) {
    curTime := time.Now().UTC()
    queryDate := datatypes.Date(curTime)
    db := DB
    db = db.WithContext(ctx)
    if err = db.Where(
        "country = ? and status = ? and ? between start_date and end_date ",
        country, 1, queryDate).Find(offersList).Error; err != nil {
        return err
    }
    return nil
}

I have noticed sometime a simple query takes quite longer time to execute. The table contains 20-30 rows.

Query : SELECT * FROM "offer_entities" WHERE country = $1 and status = $2 and $3 between start_date and end_date ;

Sometimes query takes around 80ms and sometime returns in microseconds. Have attached screenshots of apm span of the query.

1. 

2. 

3. 

Can someone tell me is there anything I am missing in the configuration or is this normal behaviour of gorm.

Comment From: a631807682

I'm not sure what you mean by log time, but there are many factors that affect the time a query takes from send to return, which I think is normal.

Comment From: sadiqOyo

Hi, @a631807682 , but this is not happening with java JDBC. There i am getting query response within 5 ms .

Comment From: a631807682

Don't ask me why it's different from java, it's so different. logically, if you think something is wrong, you need to prove it.