Hot answers tagged

11

I suspect the difference is the implicit conversion, which can get in the way. As an example, Bar isn't touched if it has a compatible data type: CREATE TABLE [dbo].[Bar] ( [Value] int NULL, [Value2] int NULL );


10

How to avoid implicit conversion for an Integer column It is the parameter that has been implicitly converted, not the column. The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The ...


7

Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria. Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria. Once the query returns a certain ...


4

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on. When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or ...


3

Assuming your variables are always NOT NULL WHERE ( ReceiveDateTime < @DocOldestTreshold OR ( ReceiveDateTime < @DocNewestTreshold AND ReceiveDateTime < t.Treshold ) ) Is equivalent to WHERE ReceiveDateTime < @DocOldestTreshold OR ReceiveDateTime < LEAST(@DocNewestTreshold, t.Treshold) Which is ...


2

why it expects for 1 row (in Clustered index seek), when I specify recompile That is fine. Somewhat confusingly the estimated rows on the inside of a nested loops join are per execution of the operator. A seek into a primary key will indeed return 1 row (or 0 if the value doesn't exists at all). In your case you have 2,000 seeks all returning 1 row ...


2

The only way to properly answer that question is to fire up the debugger and see what choices were made by the optimizer along the way. The costs are not only IO and CPU. There are additional costs associated with a given operator that are reflected in the total cost, but are not reflected in the IO and CPU cost estimates. You can read more about some of the ...


1

Create a function like the one below; CREATE OR REPLACE FUNCTION public.explain_this ( l_query text, out explain text ) RETURNS SETOF text AS $body$ BEGIN RETURN QUERY EXECUTE 'explain ' || l_query; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY DEFINER COST 100 ...


1

Be aware - I posted this before index definitions were posted Insert in the order of the PK to keep fragmentation down INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT 2, value, 0 FROM Z_SIMULATION_0 WHERE ID >= 1 AND ID <= 500000 ORDER BY value; or INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT top (...



Only top voted, non community-wiki answers of a minimum length are eligible