How to get 6 level hierarchical parent child from more than 10 lacs records faster way?

Question

1.00/5 (1 vote)

See more:

I Have more than 10 lac records and i want to get records like a parent and child.

How to optimize my query because of this query taking more than 6 hr to get result.

please suggest how to optimize this query.

i want records like this.

seqno	level
1	S1	NULL
2	S2	1
3	S3	2
5	S1	NULL
6	S2	5
7	S3	6
8      S4    7
9	S3	6
10    S4   9
11	S3	6
13	S3	6
15	S1	NULL
16	S2	15
17	S3	16
19	S3	16
21	S3	16
23	S3	16
25	S1	NULL
26	S2	25
27	S3	26
29	S3	26
31	S3	26
33	S3	26

Thanks,

What I have tried:

How to get above result set using cte.
I want sequence
Like 1 parent-child
2 bChild-child
3 dchild-childb
4 bchild1-child
5 echild- childb1
6 bchild2- child

Posted 16-Jan-20 21:07pm

Rahulgupta.hanu

Updated 6-May-20 15:36pm

v7

Add a Solution

Comments

OriginalGriff 17-Jan-20 3:17am

And?
What does that do that you didn't expect, or not do that you did?
Where are you stuck?
What help do you need?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. Just dumping a pile of code on us with no access to your data tells us nothing.

Use the "Improve question" widget to edit your question and provide better information.

Jörgen Andersson 17-Jan-20 6:23am

This is not a query, this is nested and looped procedure. Yes it looks very slow.
You're selecting your data from a lot of temp tables, but how does the table that the data comes from look like? How does the data look like.
What do you want to achieve?

Jörgen Andersson 7-May-20 8:19am

We still need to know how your source table looks like.

Richard Deeming 7-May-20 10:47am

You've edited your question to remove any trace of useful information from it. You obviously don't want anyone to help you. Why not simply delete the question?

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-01-17T02:57:00

You haven't given us enough information to recreate your scenario - for example @SS3Min is not declared, then I get an error Invalid object name '#P1' and no-one is going to wade through that much code to try and recreate your tables for you.

As @jorgen_andersson asked - What are you trying to achieve?

There are far easier ways to determine an hierarchy. I give a worked example in my article Processing Loops in SQL Server[^] - it works on the principal that you don't need a loop!

SQL is (all relational databases are) set-based - avoid using loops in all but some very rare circumstances.

Patrice T · Answer 2 · 2020-01-17T13:16:00

Quote:
How to optimize my query because of this query taking more than 6 hr to get result.

You only forgot to tell us the structure of input tables (and details like indexes, number of records ...) and a sample data that give the result you expect.
Since your query is nested loops, the slightest mistake degenerate, but without what you didn't tell, it is impossible to guess what fo wrong and why.

How to get 6 level hierarchical parent child from more than 10 lacs records faster way?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to get 6 level hierarchical parent child from more than 10 lacs records faster way?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us