Login code working slowly

Question

i am using the following code to check login details entered from textboxes but i realize that i have almost 130,000,982 usernames in my database so its kind of slowing the things . can the experts from your side suggest what should be the code to check for it in few seconds .
i have upload a text file which contains my code q1.txt

i cannot paste the code here as

Please fix the following issues to continue:

• We encountered an unexpected error. Please try again later. If this issue continues, please contact site support.

this pop up again and again . i found out that this was asked by someone else wayback but hadn't solve by microsoft .

https://learn.microsoft.com/en-us/answers/questions/1167856/microsoft-q-a-new-platform-website-problems

please suggest how do we post code

Answer 1

Hi,@vijay kumar .

To improve the performance of your login check code when dealing with a large number of usernames in the database, there are a few approaches you could consider:

Indexing: Ensure that the username and password columns in your database table have appropriate indexes. Indexing can significantly speed up the search process.

Query Optimization:

1.You could try to use IF EXISTS(SELECT * ...) to optimize the login check. Instead of retrieving the actual data, it checks for the existence of matching records based on the provided conditions. This can be more efficient as it stops searching once it finds the first match.

    string query = "IF EXISTS(SELECT 1 FROM vijay WHERE username = @Username AND password = @Password) SELECT 1 ELSE SELECT 0";

In this approach, the query checks if there is at least one row that matches the provided username and hashed password. If a match is found, it returns 1; otherwise, it returns 0.

2.You could optimize the SQL query to improve its execution time. Instead of using COUNT(*), you can try to use TOP 1 to retrieve a single record and then check its existence.

string query = "SELECT TOP 1 1 FROM vijay WHERE username = @Username AND password = @Password";

This modification will stop the query execution as soon as a matching record is found, instead of counting all the matching records.

Asynchronous Execution: You could consider executing the login check query asynchronously to prevent blocking the UI thread. By using async/await and the ExecuteScalarAsync method, the application will remain responsive during the database operation.

Here's an example of how you can try to modify the code to use asynchronous execution:

private async void btnLogin_Click(object sender, EventArgs e)
{
    // ...

    // Create a SqlCommand object with the query and connection
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        // Set the parameter values for username and password
        command.Parameters.AddWithValue("@Username", enteredUsername);
        command.Parameters.AddWithValue("@Password", enteredPassword);

        // Execute the query asynchronously and get the result
        int result = (int)await command.ExecuteScalarAsync();

        // ...
    }
}

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.