[Question] Slow execute

[mercurykd]

I use putty wth ssh-tunnel(Port Forwarding). Simple query is executed in 0.2 seconds, but the same query in console is executed in 0.002 seconds. Why?

[tkopets]

Each "execute" is actually spawning a new process and invokes the DB CLI, which in turn establishes a new DB connection and only then executes the query. My best guess is that establishing a new DB connection takes most of that time.

[mercurykd]

PhpMyAdmin works as well?(0.001 in pma)

[tkopets]

SQLTools does not support PhpMyAdmin, as it is not a command line interface (CLI). Please be more specific and elaborate on your questions.

[mercurykd]

PhpMyAdmin is also a script, and each time it calls a connection to the database, but it works many times faster.

[tkopets]

I have almost identical time on my machine (MacOS & PostgreSQL). I tried to execute the simples query possible - SELECT 1.

Command line

time echo "select 1" | psql -h localhost -d kop 
 ?column? 
----------
        1
(1 row)

Time: 0.474 ms

real    0m0.024s
user    0m0.008s
sys 0m0.009s

Here Time: 0.474 ms (0.0005 s) is the time it took to execute the query itself - without establishing the connection and other overhead. And real 0m0.024s(0.024 s) is the time it took to invoke a DB CLI, establish DB connection, execute a query, and output the result.

SQLTools

With setting "show_query": "bottom".

 ?column? 
----------
        1
(1 row)

Time: 0.517 ms
/*
-- Executed querie(s) at 2018-03-20 13:10:26 took 0.024 s --
------------------------------------------------------------
select 1
------------------------------------------------------------
*/

Here Time: 0.517 ms (0.0005 s) is the time it took to execute the query itself - without establishing the connection and other overhead. And took 0.024 s(0.024 s) is the time it took to invoke a DB CLI, establish DB connection, execute a query, and output the result.

Comments

There is already an explanation why it takes extra time in SQLTools to execute a query.

Feel free to improve the performance of SQLTools by submitting a pull request.

[mercurykd]

my situation

[tkopets]

Unfortunately, I was not able to reproduce this slowdown on my other Windows machine. However, I have one idea why it may take longer. I suspect that finding the path to DB CLI location (mysql.exe) may take some extra time, but I'm not sure this is your case.

In your SQLTools User settings do you have the full path set to your mysql CLI, or it just says mysql?

I'm referring to this section in SQLTools settings (ST: Settings in command palette):

{
    /**
     * If DB cli binary is not in PATH, set the full path in "cli" section.
     * Note: forward slashes ("/") should be used in path. Example:
     *  "mysql"   : "c:/Program Files/MySQL/MySQL Server 5.7/bin/mysql.exe"
     */
    "cli": {
        "mysql"   : "mysql",
        "pgsql"   : "d:/dev/tools/pg/9.6/bin/psql.exe",
        "mssql"   : "sqlcmd",
        "oracle"  : "sqlplus",
        "sqlite"  : "sqlite3",
        "vertica" : "vsql",
        "firebird": "isql",
        "sqsh"    : "sqsh"
    }
}

If you have just "mysql" set (without the path), can you try to:

1. set the full path to your mysql.exe binary location
2. restart sublime editor
3. try to execute the query again to see if the timing is improved

[mercurykd]

did not help, maybe it's due to port forwarding, I'm connecting to a remote machine via putty, maybe it's just a network connection delay

[tkopets]

Your time command executed pretty fast time echo "select 1" | mysql ...... (0.009s = 9ms). You executed that against the same remote DB as SQLTools, right? If yes, it should be something else. I will try to test against MySQL myself to see if that has something to do with MySQL specifically.

[tkopets]

My timings on Windows machine are very poor (even though I connect to localhost MySQL instance). I run the time command via Windows git shell and here are my results:

time echo "select 1" | mysql -h 127.0.0.1 -u<CUT> -p<CUT> <CUT>
mysql: [Warning] Using a password on the command line interface can be insecure.
1
1

real    0m0.216s
user    0m0.046s
sys     0m0.062s

I'm not even sure how you managed to get such a great result (9ms) vs (216ms my time). And also I don't have an idea where to look next.