I’m generalizing here, but… A clustered index seek is, for the most part, the best-case scenario. The only ways I can think of to improve performance would be: Update the query to return fewer rows/columns, if possible; Defragment or rebuild the index; Partition the index across multiple disks/servers. If it’s only returning 138 rows, and … Read more
So your C# code is sending an ad hoc SQL query to SQL Server, using what method? Have you considered using a stored procedure? That would probably ensure the same performance (at least in the engine) regardless of who called it. Why? The ARITHABORT setting is one of the things the optimizer looks at when … Read more
In Oracle, NULL values are not indexed, i. e. this query: SELECT * FROM table WHERE column IS NULL will always use full table scan since index doesn’t cover the values you need. More than that, this query: SELECT column FROM table ORDER BY column will also use full table scan and sort for same … Read more
Because nvarchar has higher datatype precedence than varchar so it needs to perform an implicit cast of the column to nvarchar and this prevents an index seek. Under some collations it is able to still use a seek and just push the cast into a residual predicate against the rows matched by the seek (rather … Read more
Why it’s not recommended to use subqueries? Database Optimizer (regardless of what database you are using) can not always properly optimize such query (with subqueries). In this case, the problem to the optimizer is to choose the right way to join result sets. There are several algorithms for joining two result sets. The choice of … Read more
EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN, it will collect all the results from the sub-query before further processing.
Say your model is ‘Shop’ class Shop(models.Model): street = models.CharField(max_length=150) city = models.CharField(max_length=150) # some of your models may have explicit ordering class Meta: ordering = (‘city’,) Since you may have the Meta class ordering attribute set (which is tuple or a list), you can use order_by() without parameters to clear any ordering when using … Read more