Tag Info

Hot answers tagged

3

Use a composite index: INDEX(confirmada, codTipoTransaccion, idTransaccionOriginal, idTransaccion) The columns can be in any order, so I recommend shuffling them to meet other need(s) and/or facilitate removing some other index from t2. If you need to discuss this further, please provide SHOW CREATE TABLE for each table. Edit For JOIN ... ON ...


3

This is not an issue, it is by design and is considered a performance improvement. If your queries do not run as expected you will need to address this in the code of your database/application. You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level. This will force it no matter what the compatibility ...


2

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


2

When limiting clauses like OFFSET are applied depends on the query, particularly what the result is ordered by. If you are ordering by columns that are also being used to filter the results (in a WHERE clause for instance) and are indexed then the limitation will often be applied early. The query planner will try to find just the first rows that match the ...


2

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments. I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it. ...


1

Thanks for all the help. I thought I would send an updated version of the above query that we found helpful. -- Search plan cache for Memory Grant issues IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp -- Collect more info about the plan here if required, eg usecounts, objtype etc, SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan, db = ...



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