CurrDateTime uses select getdate() ?

CurrDateTime uses select getdate() ?

  
Hi -

We're seeing millions of this query to our DB:

select getdate()

There's no other portions of the query. The documentation says that this is for getting the date/time in an Advanced Query, but none of our Advanced Queries are using it.

So... my suspicion is that CurrDateTime() uses this.

Is this correct?

J.Ja
Hi Justin,

No, the builtin function returns the datetime of thr frontend, not the database.
But there can be advance queries like that in system components. I know for sure that some screens in Service Center does that "Select getdate() from dual".
I don't think it has anything there that could justify it being called millions of times...maybe in lifetime. Do you have lifetime in that environment?

Regards,
João Rosado
Joao -

We have maybe 10 Advanced Queries in our application, and I did a "Find Text..." looking for it in all eSpaces, we are definitely not calling it in our code.

We *do* have Lifetime installed though.

J.Ja
Maybe it is for the Performance Probe?

J.Ja
Did a quick search,

LifetimeEngine espace has 49 usages on an action with that advanced query ("SELECT getdate() FROM DUAL").
I would guess that is the most likely suspect.

Regards,
João Rosado
Joao -

Thanks! This turns out to be a performance issue, it is performing table scans. I'll put in a ticket.

J.Ja
Well yes it will do a table scan, but the dual table only has 1 row.
Does it have any impact at all? or just because it's being flaged by sql server as full scan?
Joao -

It's performing 3 disk operations for every call... that's a lot of disk activity just to get the time which could/should be coming from the system. Our DBA is putting a ticket in on it so he can co-ordinate with your team. :)

J.Ja
It could be optimized to not have the "from dual" part if you are in ms sql server. It's just there because of making all our queries database independent. Would it improve your disk accesses that way?

Other than that, it actually needs database time since most of the usages cannot depend on the clocks on all front-ends being in sync.
Joao -

I *think* the "from dual" is the issue... our DBA put a ticket in so he can work directly with your team to figure out the best approach, thanks!

I knew *something* needed to use a DB-centralized time, that's why I suspected CurrDateTime(). :D

J.Ja