vsql slower than Squirrel SQL

Question

I'm executing a SQL like below in a Vertica database (running on a single node) on a table which has about 258 million rows.

I get the output in about 17 secs in SQuirrel SQL, but it takes several minutes to get the output of the same query in vsql in command-line mode.

select avg(item_chr_val_id) from table1

Any idea, what could be the reason for this slowness just in vsql?

I suspect a difference in the queries executed. Are the differences in the explain plans? What does the `vertica.log` show during each execution? Are there differences in the ACTUAL query executed (as seen from the log)? Are the same number of records returned for each? How does performance compare if you wrap each query in a `SELECT COUNT(*) ()t;`? Vertica will not need to materialize the records which will potentially eliminate a chunk of disk IO. — QuinnG, Jul 30 '12 at 15:35

score 0 · Answer 1 · answered Jul 28 '12 at 16:49

0

What versions of Vertica ande the JDBC jar are you using? I wonder if somehow you're lucking into the right execution plan with JDBC. Though it seems weird and unlikely.

answered Jul 28 '12 at 16:49

kimbo305

662
3
11

Vertica Community edition 6.0 JDBC jar: vertica-jdk5-6.0.0-0.jar – museshad Jul 30 '12 at 07:55

score 0 · Answer 2 · answered May 17 '13 at 18:21

The delay is becouse of the formting operation. In you SQuirrel SQL i maybe have a limit of 100 or 1000 as your first formated data !! In vsql the default value is 1000 but you can chenge !! To see the execution time one option is to do as follows

\timing
\o \dev\null
select avg(item_chr_val_id) from table1;

This might get an acutare result as the data will not be formated on the screan

vsql slower than Squirrel SQL

2 Answers2