How to optimize Database query to respond fast

Question

Hello,

I have an application that runs on Asp.Net and MSSQL server. But the database grows constantly (from 0 - over 4500 users) and have a table that grow on a daily bases as users do their activities, currently, that table has over 10,000 records. Now it has gotten to the point where the sql request takes time to respond making the page load to take upto 60 seconds or more to load.

below is my code sample

If I want to call up file I use this code as below

C# code

 string getGenerationControl = Id;// Getting Generation/ Generation control
 object iGenerationControl = dbClass.ExecuteGetGenerationControl(getGenerationControl);
 string scalarValueGenerationControl = iGenerationControl.ToString();
 string GenerationControl = scalarValueGenerationControl;

dbClass

 public object ExecuteGetGenerationControl(string Id) 
 {
     ds = new DataSet();
     using (cmd = new SqlCommand(Id, con))
         cmd.CommandText = Id;
     cmd.Connection = con;
     con.Open();
     cmd = new SqlCommand("[dbo].[getGenerationControl]", con);
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.Add("@Id", SqlDbType.NVarChar).Value = Id;
     object result = cmd.ExecuteScalar();
     con.Close();
     return result;
 }

The stored procedure

ALTER PROCEDURE [dbo].[getGenerationControl]  (
    @Id nvarchar(128)   
	)  
AS   
BEGIN
    SET NOCOUNT ON; 
	  
	SELECT isnull(
       (select (GenerationControl)
        FROM Matrix
        WHERE Child = @Id)
        , 0) as 'GenerationControl' 
      
END; 

Please, I there any solution for this type of my issue?

Answer 1

That query should be instant with a proper index on the column Child.

(And 60 seconds for 10000 rows still seems excessive.)

Answer 2

Hi @Emeka Okoye

To handle a performance issue that database applications may experience when using SQL Server, please refer to below doc to narrow down the cause of the slow queries issue and direct you towards resolution.

Troubleshoot slow-running queries in SQL Server.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

that table has over 10,000 records.

Really that less? I have tables with over 200 million records and no problem with.

Seems you are doing something wrong.

But again, with that less informations ...

Check the execution plan of your queries and may share it via https://www.brentozar.com/pastetheplan/