title: SignalR & SqlDependency Query performance
body: <p>I have a Table, it has around 1 million rows. This table is updated by web services, CMS applications and other sources. I want to Monitor few columns within this table. I read about SignalR and <code>SqlDependency</code>.</p>
  
  <p>However it seems like SqlDependency will <strong>re-scan</strong> the entire table on every <strong>change</strong>?? My table is pretty large and I cant afford to rescan the DB upon every change. </p>
  
  <p>is the only solution is to use <em>Trigger</em> or something? I really want to stay away from Triggers.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>public IEnumerable&lt;JobInfo&gt; GetData()
  {
  
      using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
      {
          connection.Open();
          using (SqlCommand command = new SqlCommand(
             @"SELECT [JobID],[Name],[LastExecutionDate],[Status]
             FROM [dbo].[JobInfo]", connection))
          {
              // Make sure the command object does not already have
              // a notification object associated with it.
              command.Notification = null;
  
              SqlDependency dependency = new SqlDependency(command);
              dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
  
              if (connection.State == ConnectionState.Closed)
                  connection.Open();
  
              using (var reader = command.ExecuteReader())
                  return reader.Cast&lt;IDataRecord&gt;()
                      .Select(x =&gt; new JobInfo(){ 
                          JobID = x.GetInt32(0), 
                          Name = x.GetString(1), 
                          LastExecutionDate = x.GetDateTime(2),  
                          Status  = x.GetString(3) }).ToList();                            
  
  
  
          }
      }        
  }
  
  private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
  {            
      JobHub.Show(); 
  }
  </code></pre>
  
  <p>Query in Example is...</p>
  
  <pre><code>SELECT [JobID],[Name],[LastExecutionDate],[Status]
  FROM [dbo].[JobInfo]
  </code></pre>
  
  <p>Now imagine if this table has a million rows, it would really slow down the process.</p>