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.