Why NonClustered index scan faster than Clustered Index scan?
sql,sql-server,indexing,query-execution-plans,full-table-scan
SQL Server indices are b-trees. A non-clustered index just contains the indexed columns, with the leaf nodes of the b-tree being pointers to the approprate data page. A clustered index is different: its leaf nodes are the data page itself and the clustered index's b-tree becomes the backing store for...
How to get total number of db-hits from Cypher query within a Java code?
neo4j,cypher,query-execution-plans
ExecutionPlanDescription is a tree like structure. Most likely the top element does not directly hit the database by itself, e.g. a projection. So you need to write a recursive function using ExecutionPlanDescription.getChildren() to drill to the individual parts of the query plan. E.g. if one of the children (or sub*-children)...
SQL “find missing index” script suggests missing index that already exists? Query in top 10 CPU list
sql,sql-server,performance,indexing,query-execution-plans
Well, what indexes are there? Hopefully one on userId, status INCLUDE (Id). Nothing else is required (and any additional column will slow down this query). If Id is not null or part of the CI anyway you don't even need to include it. Probably, you meant and should write COUNT(*)...
Exists Vs IN Vs =
oracle,oracle11g,oracle10g,query-optimization,query-execution-plans
No, they are different from the first query. The first query is checking two conditions: IM2.CD_ID = :B2 IM2.ID = A.DESC while remaining two queries are checking three conditions: M2.CD_ID = :B2 IM2.ID = A.DESC A.DESC = IM2.DESC Please take a look at conditions #2 and #3, and their implications.......
How to create a query, which produce this explain plan? (oracle sql)
oracle,oracle-sqldeveloper,query-execution-plans,explain-plan
Creating a precise explain plan is difficult and depends on the query, version, parameters, and undocumented hints. In this case the main hint is probably the undocumented USE_HASH_AGGREGATION, but it must also be used in combination with a DISTINCT or GROUP BY. But it also depends on which column is...
How can a stored proc have multiple execution plans?
sql,sql-server,query-execution-plans
Depending on the nature of the stored procedure (which wasn't provided) this is very possible for any number of reasons (most likely not limited to below): Does the proc use a lot of if this then this select, else this select/update Does the proc contain dynamic sql? Are you executing...
Incorrect indication for column store index in execution plan SQL Server 2012
sql-server,sql-server-2012,query-execution-plans
If it used columnstore the node would show the right icon and also would be called "Columnstore Index Scan" instead of "Index Scan". Could you check that you really do not have a regular non-clustered index on that table? (Just refresh and expand the Indexes node under dbo.Test_PDMTable in the...
mysql execution plan explanation
mysql,sql,query-execution-plans,mysql-5.5
The documentation says: Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index....
Update statement taking too long (7 minutes)
sql,sql-server,sql-update,execution-time,query-execution-plans
I suggest this approach from both a readability and performance standpoint. update yourTable set field2 = someValue where whatever and field1 in (select field1 from yourTable where whatever except select field1 from yourTable where whatever and somethingElse) where whatever should be the same every time....
Performance when adding a parameter to where clause
sql,sql-server,tsql,query-execution-plans,sqlperformance
I think you should rethink this query. Try and avoid the NOT EXISTS() for starters - as thats generally quite inefficient (I usually prefer a LEFT JOIN in these instances - and a corresponding WHERE x IS NULL - the x being something in the right hand side) The main...
Performance functions inside queries
sql-server,sql-server-2008,tsql,query-execution-plans
SQL performance is a very complex art. The cost of functions usually isn't the function itself(unless complex) but usually the overall impact on the query. For example using a Table Valued function can easily cause stats loss meaning SQL will plan poorly for the number of returned rows. Using a...