C# executescalar realy slow on big table

Question

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);
        connection.Open();
        var count = (int)cmd.ExecuteScalar();
        return count > 0;
    }
}

My table is build like this:

CREATE TABLE PriorityQueue
(
    queueID int IDENTITY(1,1) PRIMARY KEY,
    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?

It's not that your method is slow. It's your query taking long time. You can add index on `absolute_url` to make it bit faster, but for large table it will take time as it supposed to. — Mahesh, Nov 22 '16 at 09:44
Also if you reuse this query a lot with different URLS it is better ot use the Add method to define your parameter. The Add method allows you to specify the type and size of the parameter. In particular using the Size property (=100) helps a lot the Database engine optimizer to reuse the query plan and speed up things on the database side. — Steve, Nov 22 '16 at 09:47
I don't think you can solve this problem on the database. Try adding some kind of cache layer in your application to improve performance. Avoid unneccessary db queries. — Daniel, Nov 22 '16 at 09:48
I thank everyone for the good answers it really solved my problem — R.hagens, Nov 22 '16 at 10:16

score 1 · Accepted Answer · answered Nov 22 '16 at 10:02

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.

score 0 · Answer 2 · answered Nov 22 '16 at 09:48

0

ExecuteScalar() is use only for run your query , your fetching data is more thats y its take more time.

answered Nov 22 '16 at 09:48

rani

68
8

Innat3 · Answer 3 · 2016-11-22T10:09:54.780

Have you tried "SELECT TOP 1 queueID from PriorityQueue where absolute_url = @url" instead? There should be a noticeable performance boost.

In any case, I suggest you add a stored procedure to your database to return the boolean value you desire

Create PROCEDURE UrlFound @absolute_url varchar(900)  
AS  
IF (EXISTS(SELECT TOP 1 1 from PriorityQueue where absolute_url = @absolute_url))
    RETURN 1  
ELSE  
   RETURN 0;  
GO

You can then test it like this:

DECLARE @result bit
exec @result = UrlFound 'YourAbsoluteUrl'
print @result

score 0 · Answer 4 · edited May 23 '17 at 12:24

0

SQL Count is always slow with large number of records. Please look for alternative options. You can use if Exists or any other option as per your requirement.

Below are some links which will help you to understand it.

SQL Server Count is slow

SQL count(*) performance

SQL Server - Speed up count on large table

edited May 23 '17 at 12:24

Community

1
1

answered Nov 22 '16 at 10:00

Banketeshvar Narayan

3,799
4
38
46

score 0 · Answer 5 · answered Nov 22 '16 at 10:03

A 20,000 row selection will always be slightly slow, it might be a good idea to review your indexes and maybe exectute AlreadyQueued in a background worker.

https://msdn.microsoft.com/en-us/library/system.componentmodel.backgroundworker(v=vs.110).aspx

C# executescalar realy slow on big table

5 Answers5