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?
Mass quering from SQL files (or DB)
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Mass quering from SQL files (or DB)
Hi,
One option is to use the \timing meta-command.
Example:
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Mass quering from SQL files (or DB)
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)?
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)?
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Mass quering from SQL files (or DB)
How about creating cascading resource pools? You can cascade slow running queries into slower resource pools.
Checkout vertica.tips for more Vertica resources.