SQL Server : getting execution time out exceptions from stored procedure many times, but when executing the query it returns output quickly

Question

I have a webform where a student can login, on that form I will have to display the TOP 3 students along with their total score and id's. Also the total score of current logged in student and his total score

For getting this information I have a stored procedure which takes @StudentId as the input parameter and returns the TOP 3 students and their total score and also the logged in students total score and his rank. So the total number of rows returned by my query will be 4 and if in case the Logged in student is among TOP 3 students then the total number of rows returned will be 3

Below is the query for that, I am not posting how the data will be populated into the @StudentScore table as that's a very simple query with some If conditions and a simple join.

DECLARE @StudentScore AS TABLE (StudentId INT, [Score] INT)
DECLARE @StudentRankTable AS TABLE (StudentId INT, [TotalScore] INT, [StudentRank] INT)


INSERT INTO @StudentRankTable 
    SELECT 
        [StudentId], 
        SUM([Score]) AS [TotalScore], 
        DENSE_RANK () OVER (ORDER BY SUM([Score]) DESC) [Rank]
    FROM @StudentScore
    GROUP BY [StudentId] ORDER BY [Rank]


SELECT TOP 3 
    [StudentId], [TotalScore], [StudentRank] 
FROM StudentRankTable  

UNION

SELECT 
    [StudentId], [TotalScore], [StudentRank] 
FROM @StudentRankTable 
WHERE [StudentId] = @StudentId 
ORDER BY [StudentRank]

Below is how the sample data in @StudentScore temp table looks like.

StudentId Score
   101      21
   103      43
   101      11
   107      5
   103      7

The query works as expected and i am getting the desired output.

Problem: I am getting exception very frequently messages saying that this query has timed out, but when i try running this query on SQL Server the query returns the output in 3-4 seconds. I really cant understand where the problem is lying as when i run the query with different @StudentId's i get the output in 3-4 and still i see of exception messages with the below message

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

any help and suggestions on this issue?

Note - This is not happening each and every time when student logs in, but many times this is happening.

Answer 1

Maybe in SSMS you can view the result of your query or stored procedure, but your code doesn't return a query or table, I think that you should create a function that returns a table to get what you need.

You can create views to replace your temporary tables, and can create a function to get the result that you want from anywhere, an API o what you want, try this

create view v_studentRank as (
 SELECT [StudentId], 
  SUM([Score]) AS [TotalScore], 
  DENSE_RANK () OVER (ORDER BY SUM([Score]) DESC) [Rank]
 FROM StudentScore
 GROUP BY [StudentId]
);
go

create function f_studentRank(
 @StudentId int)
returns table
as
return
(
 SELECT TOP(3) [StudentId], [TotalScore], [Rank] 
 FROM (
  SELECT *, 
   ROW_NUMBER() OVER (ORDER BY [Rank]) AS [RowNumber]
  FROM v_studentRank) as v
 WHERE [RowNumber] <= 3
 UNION
 SELECT [StudentId], [TotalScore], [Rank] 
 FROM v_StudentRank 
 WHERE [StudentId] = @StudentId 
);
go

select *
from f_studentRank(4)
order by [Rank]

You can find more information about here