The reason is that the query optimizer does not do a very good job at costing user-defined functions. It decides, in some cases, that it would be cheaper to completely re-evaluate the function for each row, rather than incur the disk reads that might be necessary otherwise. SQL Server’s costing model does not inspect the … Read more
I shudder whenever I see comments that full tablescans are bad and index access is good. Full table scans, index range scans, fast full index scans, nested loops, merge join, hash joins etc. are simply access mechanisms that must be understood by the analyst and combined with a knowledge of the database structure and the … Read more
Due to PostgreSQL MVCC, an UPDATE is effectively much like a DELETE plus an INSERT. With the notable exception of toasted values – see: Does Postgres rewrite entire row on update? (And minor differences for heap only tuples – DELETE + INSERT starts a new HOT chain – but that has no bearing on the … Read more
The profiler trace puts it into perspective. Query A: 1.3 secs CPU, 1.4 secs duration Query B: 2.3 secs CPU, 1.2 secs duration Query B is using parallelism: CPU > duration eg the query uses 2 CPUs, average 1.15 secs each Query A is probably not: CPU < duration This explains cost relative to batch: … Read more
Basically yes – parameter sniffing (in some patch levels of) SQL Server 2005 is badly broken. I have seen plans that effectively never complete (within hours on a small data set) even for small (few thousand rows) sets of data which complete in seconds once the parameters are masked. And this is in cases where … Read more
If the query planner makes bad decisions it’s mostly one of two things: 1. The statistics are inaccurate. Do you run ANALYZE enough? Also popular in it’s combined form VACUUM ANALYZE. If autovacuum is on (which is the default in modern-day Postgres), ANALYZE is run automatically. But consider: Are regular VACUUM ANALYZE still recommended under … Read more
There are a number of methods of obtaining an execution plan, which one to use will depend on your circumstances. Usually you can use SQL Server Management Studio to get a plan, however if for some reason you can’t run your query in SQL Server Management Studio then you might find it helpful to be … Read more