SetTimout to terminate the sql execution if takes too long

Moderator: NorbertKrupa

Post Reply
naidug
Newbie
Newbie
Posts: 2
Joined: Sat Sep 01, 2012 3:37 am

SetTimout to terminate the sql execution if takes too long

Post by naidug » Sat Sep 01, 2012 3:42 am

We are trying to find how to set timeout to the connection/Statement to prevent sql taking minutes/hours.
It looks like Vertica driver doesn't support setTimeout (sec), what is the alternate? Anyone solved the problem?
Vertica takes minutes/hours occassionally to execute a SQL causing to accumulate too many sqls in the queue causing outage to the tomcat.
We want to kill the execution if it takes more than 60 seconds.

We are using Vertica Analytic Database v5.0.4-0
We are using Vertica JDBC driver V4.1

Thanks in advance,
Naidug

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: SetTimout to terminate the sql execution if takes too lo

Post by JimKnicely » Sat Sep 01, 2012 10:47 am

Hi naidug,

Welcome to the forums!

First thing I want to ask is do you really have SQL that is running for "minutes/hours"?!?! I have yet to experience a query that runs more than a minute in Vertica. However, we're running Cognos queries and those appear to take a long time to run because it takes forever to transfer millions of rows across the network from our Vertica cluster to the Cognos cube environment. If I run the queries locally in vsql they're super fast.

Anyway, check out the RUNTIMECAP parameter. Using it you can limit the amount of time a query can run...

For instance:

SET SESSION RUNTIMECAP '1 minute';
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

scutter
Master
Master
Posts: 301
Joined: Tue Aug 07, 2012 2:15 am

Re: SetTimout to terminate the sql execution if takes too lo

Post by scutter » Sat Sep 01, 2012 8:36 pm

Does a single SQL statement really block all other statements from executing? If yes - is that single SQL taking all of the memory in the resource pool, file handles, threads…

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

naidug
Newbie
Newbie
Posts: 2
Joined: Sat Sep 01, 2012 3:37 am

Re: SetTimout to terminate the sql execution if takes too lo

Post by naidug » Fri Sep 07, 2012 9:07 pm

knicely87 wrote:Hi naidug,

Welcome to the forums!

First thing I want to ask is do you really have SQL that is running for "minutes/hours"?!?! I have yet to experience a query that runs more than a minute in Vertica. However, we're running Cognos queries and those appear to take a long time to run because it takes forever to transfer millions of rows across the network from our Vertica cluster to the Cognos cube environment. If I run the queries locally in vsql they're super fast.

Anyway, check out the RUNTIMECAP parameter. Using it you can limit the amount of time a query can run...

For instance:

SET SESSION RUNTIMECAP '1 minute';
Thanks so much Jim, it worked perfect and able to use it.

Post Reply

Return to “JDBC”