New Topic

Community Topic Idea Performance

Lesley Goggin

EMEA SD Team

Eric Hagfors

Yellowfin Support Team

Assigned

Category Performance

Product Version 7.4

CAST() instead of CONVERT() for the "Date Function" for better performance

Yoav Chen shared this idea ago

Idea Logged

The YF internal date function for the reports and filters uses the T-SQL "CONVERT":

convert(datetime, convert(varchar(20), "tablename"."Date", 112), 112)

We replaced it with the ANSI "CAST":

cast(cast("tablename"."Date" as date) as datetime)

And we found that it was at least twice as fast!

So my request is for YF to use the CAST() function instead of the CONVERT() function in the Prepare screen's "Date Function" feature.

I like this idea 0

Replies (2)

Yoav Chen ●

Clarification:

The cast function is not an equivalent to the current Date conversion.

The cast function doesn't return a Date type field. It returns a timestamp, but the returned timestamp reset the time part of the timestamp, which allow the query to group days.

So, unless one specifically needs a Date type, this would improve performance dramatically, but if a Date field is needed, we still don't have an alternative solution for the Date conversion.

Thanks,

Yoav

Reply URL

Mike Sheehan ●

Hi Yoav,

I'm taking this ticket over for Big Dave as he's no longer with Yellowfin.

I don't have any updates on this as of yet, except to say that we did perform a test on this and found CAST() to be significantly quicker:

convert(datetime, convert(varchar(20), "tablename"."Date", 112), 112)

execution time = 0.159, fetch time = 6.537

cast(cast("tablename"."Date" as date) as datetime)

execution time = 0.004, fetch time = 6.096 Regards,

Mike