Performance hit of having many Result Set of a single row

Question

I have a TSQL code that relies on a stored procedure to select a row.

When I'm implementing a more complex TSQL script that will select many rows based on a condition, instead of having one result set of x rows I'm ending up with x result sets containing one row.

My first question is: is it a concern or the performances are close to what I would get with one result set of x rows?

Second question: does anybody think that a temporary table where my stored procedure insert the result (instead of a select) should be faster?

Edit: Basically this stored procedure select all the items of a given HierarchicalObject.

ALTER PROCEDURE [dbo].[MtdMdl_HierarchicalObject_Collection_Items]
@relatedid int

AS 
BEGIN
    SET NOCOUNT ON

    declare @curkeyid int
    declare cur CURSOR static read_only LOCAL 
        for select distinct [Id] from MtdMdl_Item where [Owner] = @relatedid

    open cur 
    fetch next 

    from cur into @curkeyid 
    while @@FETCH_STATUS = 0
    BEGIN

        -- select the item row from its ID
        exec MtdMdl_Item_ItemBase_Read @keyid = @curkeyid

        fetch next 
        from cur into @curkeyid 
    END 
    close cur 
    deallocate cur
END

ALTER PROCEDURE [dbo].[MtdMdl_Item_ItemBase_Read]
@keyid int
AS 
BEGIN
    SET NOCOUNT ON

    SELECT TOP(1) [Id], [TimeStamp], [Name], [Owner], [Value]
    FROM [MtdMdl_Item]
    WHERE ([Id]=@keyid)
    ORDER BY TimeStamp Desc
END

Can you show the queries you have questions about? – Jeff Siver Dec 17 '13 at 15:39 — Jeff Siver, Dec 17 '13 at 15:39

revoua · Accepted Answer · 2013-12-18T12:17:33.307

2

For sure you should better place all single output rows into resulting temporary table before selecting final recordset. There is no reason currently in your code to return one recorset containing all separate rows from iteration over cursor with sp;
Your MtdMdl_Item_ItemBase_Read is relevant a bit because after turning it into function you can avoid sp+cursor and complete the task with one single query using inline function.

upd
According to your data structure I understand that your [Id] is not unique which is source of confusing.
There are many ways to do what you need but here is example of one query even avoiding CTE for temporary result:

DECLARE @relatedid int = 2

SELECT top(1) WITH ties
[Id], [TimeStamp], [Name], [Owner], [Value]
FROM MtdMdl_Item
WHERE [Owner]=@relatedid
ORDER BY row_number() over(partition BY [Id] ORDER BY [TimeStamp] DESC)

Consider this SQL Fiddle as demo.

upd2

Example with inline table function:

CREATE FUNCTION MtdMdl_Item_ItemBase_Read (@keyid int)
RETURNS TABLE 
AS
RETURN 
(
    SELECT TOP(1) [Id], [TimeStamp], [Name], [Owner], [Value]
    FROM [MtdMdl_Item]
    WHERE ([Id]=@keyid)
    ORDER BY TimeStamp Desc
)
GO

DECLARE @relatedid int = 2

SELECT DISTINCT A.[Id],B.* FROM MtdMdl_Item A
OUTER apply (SELECT * FROM MtdMdl_Item_ItemBase_Read(A.[Id])) B
WHERE A.[Owner] = @relatedid

SQL Fiddle 2

edited Dec 18 '13 at 12:17

answered Dec 17 '13 at 16:26

revoua

2,044
1
21
28

This is interesting, ok I'll put the code of the second SP. Thanks – Nock Dec 17 '13 at 16:28
Just ensure there is no any insert/update inside sp or calling other sp; – revoua Dec 17 '13 at 16:30
I'm not familiar with Function, can you put a sample that will get rid of the cursor+sp please? – Nock Dec 17 '13 at 16:37
It seems that even function is not needed :) On my way. – revoua Dec 17 '13 at 16:39
I may have other SP more complicated than this one, well, it's mainly the where clause that compare more columns with parameters... – Nock Dec 17 '13 at 16:46
I am confused because you are using same table MtdMdl_Item in all queries and generally your query is very simple itself. Anyway I will show some routine for other purposes. – revoua Dec 17 '13 at 16:56
Updated with example. You can decide which top 1 will be used via manipulating partition part. – revoua Dec 17 '13 at 17:19
I appreciate the sample, but there's case where the inner and outer where are more complex, can you elaborate the use of function please? The thing is the select of the item is a stored procedure and the outer code is generated at run-time. – Nock Dec 17 '13 at 18:13
Concerning "inner/outer" you can find [this answer](http://stackoverflow.com/a/14787222/698082) useful. As for function, Ok, I will add. – revoua Dec 17 '13 at 18:16
I've profile your method against the cursor one and there's a huge difference! Cursor: CPU: 0, Reads 77, Writes 0, Duration 1. InlineTableFunction: CPU 31, Reads, 6508, Write 0, Duration 25. A closer look to the Estimated Execution Plan is that a temp table is built from Inline Table Function (understandable) but the "killing part" is you no longer profit of the indices of the "real" tables. So SQL Server is building temp indices -> kill the perf. The question is: Why do all SQL People fear Cursors? Concurrent execution should not be an issue if the cursor is a snapshot one, right? – Nock Jan 05 '14 at 23:04
Anyhow [cursors are the SLOWEST way to access data inside SQL Server](http://stackoverflow.com/a/287454/698082). Please put somewhere real dump for testing. As @marc_s [said](http://stackoverflow.com/questions/13400842/my-first-table-valued-function-and-cursor#comment18308640_13400842): "Avoid cursors - they're evil, they're hell, they're really really horrible for performance - avoid them whenever you can - and you CAN avoid them in at least 90% of the cases..." – revoua Jan 06 '14 at 11:03
I did some REAL tests, it's based from my app with a customer database. The issue with your query is like I told you it's no longer using the indices build in the database because what you're doing is creating a temporary table. The query is supposed to return a maximum number of row, like 100 max anyway. Cursor are the slowest way to access data, of course, but in this case the alternative is building a temporary table and scanning it WITHOUT precomputed indices (of course, it's temporary). If you find the solution without inline function table, it may be the best, but I couldn't do it. – Nock Jan 06 '14 at 17:03

score 0 · Answer 2 · edited May 23 '17 at 12:19

Your answer is in below link you should use GROUP BY instead of DISTINCT

SQL/mysql - Select distinct/UNIQUE but return all columns?

And in below line of your code enter list of columns you want in your result

declare cur CURSOR static read_only LOCAL 
        for select distinct [Id] from MtdMdl_Item where [Owner] = @relatedid

So your query will be

 declare cur CURSOR static read_only LOCAL 
        for select rows,you,want,in,result from MtdMdl_Item where [Owner] = @relatedid Order By [column name you want to be distinct]

Performance hit of having many Result Set of a single row

2 Answers2