hi, one of our sister division's erp extract view is going thru at least one mod to do the equivalent of a coalesce on a column that if null brings down our dw load job.
since they were doing that i asked if they could do the same on all columns that had never be null. their response was that they were worried about the query running longer. granted the underlying joins are complicated and perform poorly but if you picture all that as a subselect (derived table), the list of fields in the main select is about 60. the rdbms happens to be mysql. does the community have a general feel for the overhead involved when throwing coalesces or isnulls on columns? in incremental processing, we extract maybe 20,000 records. on a full load maybe 1.4 million records. full loads may not be necessary more than once a week.
The first thought would be adding a NOT NULL constraint on any column that should not contain any NULLs, and removing any tests from the code.
😎
Not certain how MySQL implements COALESCE, but in my experience, it has a small impact. ISNULL in MySQL is a boolean function that returns 1 or 0, not applicable for replacing COALESCE. IFNULL behaves like COALESCE with the second parameter replacing any NULL entries. NOTE: the data types must match, which can be troublesome if there is any implicit type conversion!
May 19, 2025 at 12:19 pm
thx eirikur. this is a query we use for extracting and loading. so specifying not null on a "table column" wouldn't really apply even on their side. they cant afford to apply such a constraint on their upstream data source. but you answer was heard.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply