Slow Queries Compared to FlameRobin and isql-fb

[ghost]

So, I'm getting weird results with a query. When I run this query in FlameRobin, it takes about 400ms, but when I run it using this library in golang, it takes up to 5-6 seconds (the query alone - this doesn't include calling Scan). I don't really know what's causing this big difference.

The Query:

SELECT r.id, r.url, r.urlhash, r.scheme, r.domainid, r.contenttype, r.charset, r.language, r.title, r.prompt, r.size,
r.hash, r.feed, r.publishdate, r.indextime, r.album, r.artist, r.albumartist, r.composer, r.track, r.disc,
r.copyright, r.crawlindex, r.date_added, r.hidden
FROM (
    SELECT internal.*, (SELECT MAX(RANK) FROM keywords WHERE pageid=internal.id AND keywords.keyword LIKE ?) as searchrank
    FROM PAGES internal
) r
WHERE r.searchrank IS NOT NULL 
OR (replace(lower(url), 'gemini://', '') LIKE ? OR lower(title) LIKE ? OR lower(artist) LIKE ? 
OR lower(album) LIKE ? OR lower(albumartist) LIKE ?)
ORDER BY r.searchrank DESC

[bat22]

May be you execute already prepared query in flamerobin. Can you separate prepare and exec time for this query?

[ghost]

I did the query in the terminal sql tool (isql-fb) and it was very quick as well, on first run and subsequent runs. The preparing of the query should be handled under the hood by golang's sql library, afaik.

[ghost]

Oh, also, flamerobin shows the prepare happened in .013 seconds, and the execute happens in .400 seconds.

[nakagami]

The driver only implements methods that are predetermined, so it will be difficult to make it faster.

[bat22]

@krixano What FB version do you use? What type of server: classic server or super server? How big is the table? Have you tried to execute query 2 times in a row on the same connection? Any difference between 1st and 2nd query?

code for last question:

    db, err := sql.Open("...", "...");
    conn, err := db.Conn(context.Background())
    query := "..."
    args := []interface{}{...}

    t := time.Now()
    rows, err := conn.QueryContext(context.Background(), query, args...)
    fmt.Println("1st:", time.Since(t))
    rows.Close()

    t = time.Now()
    rows, err = conn.QueryContext(context.Background(), query, args...)
    fmt.Println("2nd:", time.Since(t))
    rows.Close()

[nakagami]

It does not appear to be an effect of driver. This is an old issue so, it will be closed.