utcgetdate - stop a query after x time

[Former Member]

Hi

Im looking for a way to stop a query after a given period of time or a specific timestamp, like

select * from bigtable where getutcdate() < "2015-06-19 15:00:00"

In Sybooks, the utcgetdate function mentions that :

Returns a date and time where the value is in Universal Coordinated Time (UTC). getutcdate is calculated each time a row is inserted or selected.

But, when running the following command, even for a long time, the timestamp value doesn't change at all...  not even microseconds. Looks like it get cached 

select top 50000 convert(char(26),getutcdate(),109),* from bigtable

I can guess that this may incur a reasonable overhead since time function may requet a spinlock to get the clock value, so , i was planning to wrap up this time check in a function and pass a number (probably the sequential numeric key at hand)  and get the modulus of this number by 1000

The idea is also only use this construction on very specific queries which may be triggered by the end of the day and the results expected on the next day, but they should not get into business hours....

Someone have some ideas to share?

Thanks!

[former_member182090]

Mark, I hate to say this, but your solution actually doesn't work in terms of forcing the getutcdate() call to be evaluated for every row. This trick only works when the table column is an argument to the function. In this case, the dateadd() is forced to be evaluated for every row, but the getutcdate() call is still evaluated only once.

(Okay, I guess that puts the onus on me to show my solution  in full... see above)

[Mark_A_Parsons]

Rob, you're right ... that's what I get for doing a drive-by shooting ...*arg*

---------------

Hmmmm, and rolling this into a where/SARG clause it looks like the query engine does halt processing of rows once the time limit has been hit.

====================

-- let run to completion

select convert(varchar,dbo.f_getdate(so.id),109)

from sysobjects so, syscolumns

go

     200K rows, cpu = 7500ms, logical IOs = 1,830

     table/index accesses = ~400K each

-- let run for 2 seconds

select convert(varchar,dbo.f_getdate(so.id),109)

from sysobjects so, syscolumns

where dbo.f_getdate(so.id) <= dateadd(ss,2,getdate())

go

     55K rows, cpu = 2000ms, logical IOs = 200

     table/index accesses = ~110K each

====================

[former_member182090 · Accepted solution]

The getutcdate() built-in function is evaluated once for the query, so it its value is treated as a constant value in the query results. The only way to force evaluation of the built-in for every row in the result set is to specify a column from a table in the query as an argument to the function (as explained in my book "Tips, Tricks and Recipes for Sybase ASE", www.sypron.nl/ttr).

However, this builtin does not accept any arguments. This can be worked around by creating a SQL user-defined function instead, but these do not allow these non-deterministic built-in functions to be called (unless you're on 15.7 SP134 and you enable TF 11001).

If you're not on SP134, that issue can again be worked around by replacing the body of the function by a stored procedure call -- but the procedure name must start with 'xp_'.

So, there are some challenges, but it's possible -- I've done it.

[former_member182090 · Accepted solution]

Here's the full code and example output:

create procedure xp_f_getdate (@d datetime output)
as
  set @d = getdate()
go

create  function f_getdate (@i int)
returns datetime
as
   declare @v datetime
   exec xp_f_getdate @v output
   return @v
go

1> select convert(varchar,dbo.f_getdate(so.id),109)
2> from  sysobjects so, syscolumns, sysindexes
3> go
[...]
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:520AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:523AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:526AM
Jun 20 2015  1:35:27:530AM
Jun 20 2015  1:35:27:530AM
Jun 20 2015  1:35:27:530AM
Jun 20 2015  1:35:27:530AM
Jun 20 2015  1:35:27:530AM
[...]

[kevin_sherlock · Accepted solution]

One could also just create a table with a virtual computed column  datetime column:

create table d_datetime(zero int not null, d compute getdate() )

insert into d_datetime(zero) values (0)

create  function f_getdate (@i int)

returns datetime

as

   declare @v datetime

   select @v = d from d_datetime where zero=0

   return @v

go

select convert(varchar,dbo.f_getdate(so.id),109)

from  sysobjects so, syscolumns, sysindexes

...

[Former Member]

Thanks folks for the tips given

Its hard to use resource governor since customer does have a big environment and to get it active need a great effort that i and the end user can't afford to wait for actually (Hopefully i could charge for implementing it....).

Oh my... i should have bought Tips and tricks,,,  Have bought the 2 quickreferences (ASE and Rep) and they are the last books i really enjoyed to buy in quite some time 

Ill try first Mike proposal...  hopefully i can bear the overhead of getting system datetime every row collected...  last time i checked about sytem date time functions theres some kind of lock/flag/spinlock added to the internal routine to avoid getting trashed data on the fly...

[former_member188958]

Perhaps a poor man's solution? 

Open the connection for the report, get it's spid, and start the query.  Don't plan on using this connection for any other subsequent activity.

Open a second connection and execute a "waitfor time <dropdeadtime>" followed by a "kill <report spid>"

Leave both sessions running overnight.

-bret

[c_baker]

Have you looked into Resource Governor?

Chapter 1: Limiting Access to Server Resources

The description from the docs:

Resource limits

A resource limit is a set
of parameters specified by a system administrator to prevent queries
and transactions from individual logins or applications from monopolizing
server resources.

Resource limits are bound to time ranges, allowing the system administrator
to define precisely when they should be enforced. When the system
administrator modifies a resource limit, all users logged in see
the change, including the system administrator

The set of parameters
for a resource limit includes the time of day to enforce the limit
and the type of action to take. For example, you can prevent huge reports
from running during critical times of the day, or kill a session
whose query produces unwanted

Chris