[Solved] Update/Rewrite only a single row in SQL Server

EverSQL Database Performance Knowledge Base

Update/Rewrite only a single row in SQL Server

Database type:

I want to only have 1 row in my table. I am populating a row from Server1 to Server2 on a single table using SSSIS. I am updating Execution End time that I get from a table in server1 to a table in server2. Here is the query I use to populate :

SELECT TOP 1 EXEC_END_TIME    
FROM cr_stat_execution cse    
WHERE cse.EXEC_NAME = 'ETL'     
ORDER BY exec_end_time DESC

The problem: I only want to update server2's table with the recent record only or rewrite previous days data. I don't want to have a history on my table, how can I modify my query to only populate the most recent data from Server1 to Server2 without having rows of history.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX cr_execution_idx_exec_name_exec_time ON cr_stat_execution (EXEC_NAME,exec_end_time);
The optimized query:
SELECT
        TOP 1 cse.EXEC_END_TIME 
    FROM
        cr_stat_execution cse 
    WHERE
        cse.EXEC_NAME = 'ETL' 
    ORDER BY
        cse.exec_end_time DESC

Related Articles



* original question posted on StackOverflow here.