R.hagens R.hagens - 10 months ago 59
C# Question

C# executescalar realy slow on big table

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
cmd.Parameters.AddWithValue("@url", url);
var count = (int)cmd.ExecuteScalar();
return count > 0;

My table is build like this:

CREATE TABLE PriorityQueue
absolute_url varchar (900),
depth int,
priorty int

Is there someway to make my C# method faster, or do I need to change something in my table?

Answer Source

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.