c# and sql recursive data query
-
Hi, in my project I use:
command = new SqlCommand(SQLStatement, DataBase.SqlDataReader
reader = command.ExecuteReader();to query "part_ID" from large table. Each part_ID can have more subparts up to n level. They are all stored in same table. At the moment I use foreach statement to recursivly query parts from table until they have no sub parts. As there are up to 10000 subparts for some of parts it takes up to almost a minute to get all subparts (each query takes app. 15 ms). Is there a faster (more efficient) way to get the same job done?
-
Hi, in my project I use:
command = new SqlCommand(SQLStatement, DataBase.SqlDataReader
reader = command.ExecuteReader();to query "part_ID" from large table. Each part_ID can have more subparts up to n level. They are all stored in same table. At the moment I use foreach statement to recursivly query parts from table until they have no sub parts. As there are up to 10000 subparts for some of parts it takes up to almost a minute to get all subparts (each query takes app. 15 ms). Is there a faster (more efficient) way to get the same job done?
-
Hi, in my project I use:
command = new SqlCommand(SQLStatement, DataBase.SqlDataReader
reader = command.ExecuteReader();to query "part_ID" from large table. Each part_ID can have more subparts up to n level. They are all stored in same table. At the moment I use foreach statement to recursivly query parts from table until they have no sub parts. As there are up to 10000 subparts for some of parts it takes up to almost a minute to get all subparts (each query takes app. 15 ms). Is there a faster (more efficient) way to get the same job done?
Use a DataSet and fill it with each table and set a DataRelationship. Although it may use more memory depending on how much data you have it beats 10000 individual queries.
I know the language. I've read a book. - _Madmatt
-
Hi, in my project I use:
command = new SqlCommand(SQLStatement, DataBase.SqlDataReader
reader = command.ExecuteReader();to query "part_ID" from large table. Each part_ID can have more subparts up to n level. They are all stored in same table. At the moment I use foreach statement to recursivly query parts from table until they have no sub parts. As there are up to 10000 subparts for some of parts it takes up to almost a minute to get all subparts (each query takes app. 15 ms). Is there a faster (more efficient) way to get the same job done?
Could you show some of the schema?
-
Could you show some of the schema?
All queries are done from the same table (lets call it "parts_table"). Let say I start with "part_ID" = 1001 --> query returns 120 "subpart_id" (1002, 1004, 2030 ...) then i execute same query 120 times and get n subparts for each of 120 subpart_ID from first query and so on until there are no subparts left. As you can see the number of queries can increase very fast ... Table: "parts_table" id part_id subpart_id 1 1001 1002 2 1001 1004 3 1002 2150 4 1002 3250 5 1004 1250 . Part description for "part_id" is in separate table "part_info". I use join to get info for "part_id" ...
-
All queries are done from the same table (lets call it "parts_table"). Let say I start with "part_ID" = 1001 --> query returns 120 "subpart_id" (1002, 1004, 2030 ...) then i execute same query 120 times and get n subparts for each of 120 subpart_ID from first query and so on until there are no subparts left. As you can see the number of queries can increase very fast ... Table: "parts_table" id part_id subpart_id 1 1001 1002 2 1001 1004 3 1002 2150 4 1002 3250 5 1004 1250 . Part description for "part_id" is in separate table "part_info". I use join to get info for "part_id" ...
I'm still thinking about it, and one way (with SQL Server 2008) to reduce the number of queries/trips to the database is with a Table Valued Parameter.
-
Hi, in my project I use:
command = new SqlCommand(SQLStatement, DataBase.SqlDataReader
reader = command.ExecuteReader();to query "part_ID" from large table. Each part_ID can have more subparts up to n level. They are all stored in same table. At the moment I use foreach statement to recursivly query parts from table until they have no sub parts. As there are up to 10000 subparts for some of parts it takes up to almost a minute to get all subparts (each query takes app. 15 ms). Is there a faster (more efficient) way to get the same job done?
This is the reason we implemented the hierarchy id, using the partid in the hierarchy id has made a tremendous difference. So much so that I am taking the concept to an Oracle database. I would craft a stored proc that takes a part and returns all the children in one table, using a CTE to do the recursive loop (and I hate CTEs). Then to the presentation formatting in the business layer.
Never underestimate the power of human stupidity RAH
-
This is the reason we implemented the hierarchy id, using the partid in the hierarchy id has made a tremendous difference. So much so that I am taking the concept to an Oracle database. I would craft a stored proc that takes a part and returns all the children in one table, using a CTE to do the recursive loop (and I hate CTEs). Then to the presentation formatting in the business layer.
Never underestimate the power of human stupidity RAH