Query tuning and Optimization

Question

I am the beginner in TSQL. Here is the question. How can i optimize the query below and improve execution time? This SP executes inside the cursor, but it's allowed to modify this piece only. Am thinking about using temp tables. Thank you

    CREATE PROCEDURE dbo.GetProductFromHistory 
    @ProductID INT
AS
BEGIN
    DECLARE @Result TABLE(
        PurchaseOrderNumber NVARCHAR(25),
        TransactionID INT
    )

    INSERT INTO @Result(PurchaseOrderNumber, TransactionID)
    SELECT soh.PurchaseOrderNumber, th.TransactionID
    FROM Production.TransactionHistory th
    INNER JOIN Sales.SalesOrderHeader soh on th.ReferenceOrderID = soh.SalesOrderID
    WHERE th.ProductID = @ProductID;

END

GO

Answer 1

There is nothing you can do. RUnning an SP inside a cursor loop is totally NOT how you do it and is going to make things extremely slow, but you are as per your own words not allowed to change this fact.

Given that you have one ID and that the output is also defined - this is as simple a select statement as it gets and there is nothing you can optimize on this code. Obviously there should be an index on both relevant fields (th.ProductID and th.ReferenceOrderID and soh.SalesOrderID) but then again, this is not part of the query (but an error I ahve generally seen to often).

The crux here is that having this SP inside a loop is what makes it slow, not the small code you have.