Tag: performance

SQL count(*) performance

I have a SQL table BookChapters with over 20 millions rows. It has a clustered primary key (bookChapterID) and doesn’t have any other keys or indexes. It takes miliseconds to run the following query if (select count(*) from BookChapters) = 0 … However, it takes over 10 minutes when I change it like so if […]

SQL Query slow in .NET application but instantaneous in SQL Server Management Studio

Here is the SQL SELECT tal.TrustAccountValue FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = 70402 AND ta.TrustAccountID = 117249 AND tal.trustaccountlogid = ( SELECT MAX (tal.trustaccountlogid) FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN […]


Which one of the following queries is faster (LIKE vs CONTAINS)? SELECT * FROM table WHERE Column LIKE ‘%test%’; or SELECT * FROM table WHERE Contains(Column, “test”);

sql group by versus distinct

Why would someone use a group by versus distinct when there are no aggregations done in the query? Also, does someone know the group by versus distinct performance considerations in MySQL and SQL Server. I’m guessing that SQL Server has a better optimizer and they might be close to equivalent there, but in MySQL, I […]

Does the order of columns in a WHERE clause matter?

Does the order of the columns in a WHERE clause effect performance? e.g. Say I put a column that has a higher potential for uniqueness first or visa versa?


I want get n-th to m-th records in a table, what’s best choice in 2 below solutions: Solution 1: SELECT * FROM Table WHERE ID >= n AND ID <= m Solution 2: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS row FROM Table )a WHERE row >= n AND row <= […]

How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005

Storing sex (gender) in database

I want to store a users gender in a database with as little (size/performance) cost as possible. So far, 3 scenarios come to mind Int – aligned with Enum in code (1 = Male, 2 = Female, 3 = Hopefully no need for this?) char(1) – Store m or f Bit (boolean) – and call […]

Pros and Cons of using SqlCommand Prepare in C#?

When i was reading books to learn C# (might be some old Visual Studio 2005 books) I’ve encountered advice to always use SqlCommand.Prepare everytime I execute SQL call (whether its’ a SELECT/UPDATE or INSERT on SQL SERVER 2005/2008) and I pass parameters to it. But is it really so? Should it be done every time? […]

SQL Server 2008 paging methods?

I have to work with a potentially large list of records and I’ve been Googling for ways to avoid selecting the whole list, instead I want to let users select a page (like from 1 to 10) and display the records accordingly. Say, for 1000 records I will have 100 pages of 10 records each […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.