Page 1 of 1

Mass quering from SQL files (or DB)

Posted: Thu Feb 25, 2016 4:40 pm
by linux
Hello,

is this possibile to make shell script which will be quering, for example, a lot of .sql files (or DB records with SQL queries) and make log file with execution time of these queries?

Re: Mass quering from SQL files (or DB)

Posted: Thu Feb 25, 2016 5:10 pm
by JimKnicely
Hi,

One option is to use the \timing meta-command.

Example:

Code: Select all

[dbadmin@s18384357 ~]$ cat run1.sql
\timing
select count(*) from dual;

[dbadmin@s18384357 ~]$ cat run2.sql
\timing
select count(*) from dual;

[dbadmin@s18384357 ~]$ cat run3.sql
\timing
select count(*) from dual;

[dbadmin@s18384357 ~]$ cat run.sh
/opt/vertica/bin/vsql -f /home/dbadmin/run1.sql >> /home/dbadmin/run.out
/opt/vertica/bin/vsql -f /home/dbadmin/run2.sql >> /home/dbadmin/run.out
/opt/vertica/bin/vsql -f /home/dbadmin/run3.sql >> /home/dbadmin/run.out

[dbadmin@s18384357 ~]$ ./run.sh

[dbadmin@s18384357 ~]$ cat run.out
Timing is on.
 count
-------
     1
(1 row)

Time: First fetch (1 row): 12.089 ms. All rows formatted: 12.166 ms
Timing is on.
 count
-------
     1
(1 row)

Time: First fetch (1 row): 10.920 ms. All rows formatted: 10.988 ms
Timing is on.
 count
-------
     1
(1 row)

Time: First fetch (1 row): 11.957 ms. All rows formatted: 12.006 ms

Re: Mass quering from SQL files (or DB)

Posted: Fri Feb 26, 2016 10:42 am
by linux
Thanks,

now I have First fetch time and All rows formated time.

Is this possibile to check this time and make some exeptions? (if,case)
For exampe: If Time > 3 minutes then break. For breaking slow queries?
Is this possibile to save this times to log file?

Secound question:
do You know how to run script frequently (every 3,5,10 minutes)?
And make log files for every run (with only times of running scripts and status: ok, warning,error)?

Re: Mass quering from SQL files (or DB)

Posted: Fri Feb 26, 2016 4:56 pm
by NorbertKrupa
How about creating cascading resource pools? You can cascade slow running queries into slower resource pools.