Performance tuning for pyramid looping

Question

i am designing a pyramid scheme for my customer, he going to apply this structure to the system. It is a referal scheme, if intro someone to join the website, all upper layer will enjoy the referal fee.

This is my table structure, so based on the referral by column, i can find back all the upper line or lower line of the particular user.

However when i using this statement to loop, (sorry cannot provide full code since it is big, but it can be easily to understand with the below code)

for (int a = 0; a < lowerLine.Count; a++)
{
    var query3 = from data in db.users_table where data.referral_by == referralUser && data.is_activated == true select new{ data.user_id,data.introducer};
    var lowerLine2 = query3.ToList();
    lowerLineCount2 += lowerLine2.Count;
    totalCount += lowerLine2.Count;
}

It is a linq statement and it will keep looping until end of the pyramid to get total referral. However if execute this thing, if he have 500 referal, it will become very slow to get all the data.

In this case, i think a solution to get all data through stored procedure, however when i tried to execute that statement for 500 times, the performance still 26 seconds which is still very slow, my requirement is maximum 10 seconds to get all the data out. As a result, the stored procedure is not a choice since it will still slow when execute at once

May i know how to get all the data out with this pyramid scheme within 10 seconds? I dont mind to indexing, however i did the indexing on the referral by the result is still slow

Possible approach: [https://stackoverflow.com/a/18111876/1565525](https://stackoverflow.com/a/18111876/1565525) — Fabio, Dec 26 '17 at 04:53
Also its better not to paste too many pictures as it makes the post a little hard to read — TheGeneral, Dec 26 '17 at 05:01

score 0 · Answer 1 · edited Jul 14 '21 at 22:28

Firstly, using char(n) is a bad habit and can easily cause you problems, I'd always use NVARCHAR(n) for user entered data.

Secondly, you might want to look at a Recursive Common Table Expression (CTE) in a stored procedure. Depending on the table size and other factors it will probably give you far better performance than querying through EF. However a standard T-SQL loop will also be worth while to test as well, see below for more resources.

Lastly, I'm not sure whether this an EF code-first approach and your referral_by is a navigation property, though I'd make sure it is indexed appropriately either way

...

The below code just attempts to make a list of the referral tree of @SomeUser (i.e the newly added member as per your original question) . I'm not entirely sure what you are trying to do with the lowest line stuff

CTE

With UserCte as
(
  //Anchor Query
  Select user_id, first_name, last_name, referral_by from users Where user_id=@SomeUser
  Union all
  //Recursive Query
  Select U.user_id, U.first_name, U.last_name, U.referral_by from users U
  Inner Join UserCte M //Joining with anchor member
  On U.user_id = M.user_id
)

//Returning all user of user_id @SomeUser
Select * from UserCte

Notes :

This is just an example and hasn't been tested but it should point you in a better direction
There is no hard and fast rule with performance, what is good for someone may not be good for you; it's best to do your own benchmarks with these types of things on your own system

Resources

Using Common Table Expressions

Optimize Recursive CTE Query

This --> always use NVARCHAR(n) for user entered data – Ross Bush Dec 26 '17 at 05:20 — Ross Bush, Dec 26 '17 at 05:20

score -1 · Answer 2 · answered Dec 26 '17 at 05:21

You can very well use Indexing.For your case, you can use primary indexing or cluster indexing. For that, You first need to decide which is the column and query that is frequently getting in use. and for that you will need to revise your ER model again and again.I hope performance will get improved

By looking at your SQL table , I am assuming that you might not have done ER model Correctly.It's a bad approach to have so many null columns in your table. So, Start from the root then automatically you will see the difference.

For your ref : Indexing

Performance tuning for pyramid looping

2 Answers2