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

Yoav Chen shared this idea 22 months 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.

Comments (2)

photo
1

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

photo
1

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