I have a problem with executescalar being real slow on a table with over 200.000 records.
The method i use checks if an varchar exists in the table and returns a count to see if anything can be found:
public static bool AlreadyQueued(string url)
using (SqlConnection connection = new SqlConnection(_connectionString))
SqlCommand cmd = new SqlCommand("SELECT Count(queueID) from PriorityQueue where absolute_url = @url")
Connection = connection,
CommandType = CommandType.Text
var count = (int)cmd.ExecuteScalar();
return count > 0;
CREATE TABLE PriorityQueue
queueID int IDENTITY(1,1) PRIMARY KEY,
absolute_url varchar (900),
The slowness is in the database, as has already been pointed out by others. Since you don't really need the exact count, but rather a Boolean indicating whether a row exists or not, you may get a slight performance increase like this:
SELECT TOP 1 1 from PriorityQueue where absolute_url = @url
With this query, the database can stop searching once the first (and presumably only) match is found.
But to get significant performance gains, you need to add an index to the
absolute_url column. But that column is currently defined as
varchar(900) which is (if I've Googled correctly) right at the limit of how long a column can be in an index. If you index it as such, the index will take up about the same amount of space as the table itself.
So if possible, shorten the column and then add an index on it. If you absolutely cannot shorten it, you can maybe add one more column that holds the first (say) 50 chars of the columns and then index that column instead. Then you can do like this:
SELECT TOP 1 1 from PriorityQueue where absolute_url = @url and shortened_url = @shortenedUrl
Then you need to also add the
@shortenedUrl parameter which should (of course) contain the first 50 chars of the url you are looking for.