Recursive lookup needed. DB vs. C# GUI

I currently have a program in which I want to list ‘support tickets’ in a grid.

The tickets are coming from a SQL DB out of a ticketing system.

A ticket has an activity, example:

enter image description here

I have a fully functional design but have a problem to list the following:

What I want to do in my program is to list the parent tickets, with their child tickets as children (details). However, with the added complexity, i’m obliged to do this recursively.


enter image description here

My questions were:

  • In this case, is the recursive lookup to be done on DB level? Or application level?

  • AM i obliged to achieve this, to loop through all the tickets PER TICKET?

  • How is performance affected by these look ups?

I did some research and tried to get the tickets alone but haven’t achieved this. I tried it on the level of the DB using:

SELECT ticketID,
FROM dbo.Ticket

SELECT a.ticketID, 
FROM dbo.Ticket a 
INNER JOIN dbo.ticket s ON a.ticketID = s.ticketID 

where ticketID = 1

While I was testing this, i had the idea that maybe I’m doing this wrong, since maybe, the recursive search is better to be done in my application level (in which i would just need to do a simple select * from ticket in my DB.

Is this true? Would I be needing to do this on application level?


using a recursive query in SQL Server


enter image description here

With TicketTree(tID,  pID)
    Select ticketID, parentID From Ticket Where ticketID = 1 
    Select ticketID, parentID from Ticket Inner join TicketTree R On parentID = R.tID   
select * from TicketTree


enter image description here

or if you query root ticket 4

enter image description here

You can then join with your Activity or whatever you want to do, I just kept it to the minimum need for a recursive query

Leave a Reply

Your email address will not be published. Required fields are marked *