Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS): sp_who sp_lock Also, in SSMS, you can view locks and processes in different ways: Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu … Read more
The reserved prefix that exhibits this behaviour is sp_. Not sp. A stored procedure called spAddUser will be resolved in the normal way without looking for a matching object in master. The relevant quote in books online is Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server … Read more
You can get the Json easily enough if you have a query wrapper; var qLinq = Query<T>.Where(x => x.name==”jim”); Console.WriteLine(qLinq.ToJson()); There’s also an Explain() method on MongoCursor, so you could do this; var exp = Collection.FindAs<T>(qLinq).Explain() Console.WriteLine(exp.ToJson()); So if you want the time taken, “millis” is in there; var msTaken = exp.First(x => x.Name == … Read more
Indexes are built from the start of the string towards the end. When you use LIKE ‘whatever%’ type clause, MySQL can use those start-based indexes to look for whatever very quickly. But switching to LIKE ‘%whatever%’ removes that anchor at the start of the string. Now the start-based indexes can’t be used, because your search … Read more
Usually joins will work faster than inner queries, but in reality it will depend on the execution plan generated by SQL Server. No matter how you write your query, SQL Server will always transform it on an execution plan. If it is “smart” enough to generate the same plan from both queries, you will get … Read more
For testing purposes you can force the use of the index by “disabling” sequential scans – best in your current session only: SET enable_seqscan = OFF; Do not use this on a productive server. Details in the manual here. I quoted “disabling”, because you cannot actually disable sequential table scans. But any other available option … Read more
Well first of all if you have a lot of geospatial data, you should be using mysql’s geospatial extensions rather than calculations like this. You can then create spatial indexes that would speed up many queries and you don’t have to write long drawn out queries like the one above. Using a comparision with ST_Distance … Read more
MongoDB concatenates the compound key in some way and uses it as the key in a BTree. When finding single items – The order of the nodes in the tree is irrelevant. If you are returning a range of nodes – The elements close to each other will be down the same branches of the … Read more
I would propose a clean version based on DISTINCT ON (see docs): SELECT DISTINCT ON (usr_id) time_stamp, lives_remaining, usr_id, trans_id FROM lives ORDER BY usr_id, time_stamp DESC, trans_id DESC;
It’s better to use either of the following: — Method 1. SELECT 1 FROM table_name WHERE unique_key = value; — Method 2. SELECT COUNT(1) FROM table_name WHERE unique_key = value; The first alternative should give you no result or one result, the second count should be zero or one. How old is the documentation you’re … Read more