3 ms·
Not only that, but I have been working with some queries that contradrict this. We had some UDFs that are hyper-simple fetchers of config values from a KVP con
by Pxtl 10y ago
Not only that, but I have been working with some queries that contradrict this. We had some UDFs that are hyper-simple fetchers of config values from a KVP config table.
I tried optimizing them out. Nothing. Looked at the query plans - 0%. SQL server had reasonably noticed what they were and done a bang-up job handling them.
That's the infuriating thing about SQL server "advice" - it's such a black box that so many of the rules are cargo-cult stuff.
- timlin 10y agoThe biggest problem with scalar UDFs is not the IO performance. It is that they force the query to serial (non-parallel). On queries that return a non-trivial quantity of data, that hurts. https://www.brentozar.com/blitz/blitz-result-user-defined-functions-slowing-down-sql-server/ https://www.brentozar.com/blitz/blitz-result-user-defined-fu... http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forci...
- Pxtl 10y agoI'm curious, how do you spot this "forces parallel" on a query plan? Or is this one of those magic things you have to just know? Would it make more sense to have a precompiler go through and inline my UDFs? They seem pretty amenable to a macro-expansion.
- swasheck 10y agoIt's not a "black box" but what ends up happening is that the recommendation is handed down/out to others without full explanation. Additionally, data volumes truly matter, for the reason(s) outlined above vis a vis table scanning due to (non)determinism.