help speeding up some code with data access

[twaldron]

I could use some guidance on a better way get get my data than I am using. This way is taking nearly 45 seconds to get the data.

here are my methods used to get the data

<code>
1. public List<BOM> GetBomList(string partno)
2. {
3.
4. List<BOM> bomList = new List<BOM>();
5. BOM spaBom = BOMDB.GetSpaBomParent(partno);
6. bomList.Add(spaBom);
7. //get spalevel1
8. List<BOM> level1List = BOMDB.GetSpaBomChild(spaBom.BomPartID);
9. foreach (BOM l1 in level1List)
10. {
11. bomList.Add(l1);
12. //getlevel2
13. List<BOM> level2List = BOMDB.GetSpaBomChild(l1.BomPartID);
14. foreach (BOM l2 in level2List)
15. {
16. bomList.Add(l2);
17. //getlevel 3
18. List<BOM> level3List = BOMDB.GetSpaBomChild(l2.BomPartID);
19. foreach (BOM l3 in level3List)
20. {
21. bomList.Add(l3);
22. //get level 4
23. List<BOM> level4List = BOMDB.GetSpaBomChild(l3.BomPartID);
24. foreach (BOM l4 in level4List)
25. {
26. bomList.Add(l4);
27. }
28. }
29. }
30.
31. }
32.
33. return bomList;
34. }

</code>

then my data access class is:

<code>
1. public static class BOMDB
2. {
3.
4. public static BOM GetSpaBomParent(string partno)
5. {
6. SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
7. SqlCommand cmd = new SqlCommand("dbo.BOSS3GetSpaBomLevel0", con);
8. cmd.CommandType = CommandType.StoredProcedure;
9. cmd.Parameters.Add("partno", SqlDbType.NVarChar).Value = partno;
10. BOM bom = new BOM();
11. SqlDataReader dr;
12. try
13. {
14. con.Open();
15. dr = cmd.ExecuteReader();
16. while (dr.Read())
17. {
18. if (dr["bompartid"] != DBNull.Value)
19. bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
20. //indent the bomblevel string based on level number
21. if (dr["bomlevel"] != DBNull.Value)
22. bom.BomLevel = dr["bomlevel"].ToString();
23. if (dr["avgcost"] != DBNull.Value)
24. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
25. if (dr["bomqty"] != DBNull.Value)
26. bom.BomQty = Convert.ToInt32(dr["bomqty"]);
27. if (dr["avgcost"] != DBNull.Value)
28. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
29. if (dr["stdcost"] != DBNull.Value)
30. bom.StdCost = Convert.ToDouble(dr["stdcost"]);
31. bom.BomUnit = dr["bomunit"].ToString();
32. bom.Description = dr["descript"].ToString();
33. }
34. return bom;
35. }
36. catch (Exception ex)
37. {
38. throw ex;
39. }
40. finally
41. {
42. con.Dispose();
43. }
44.
45. }
46.
47.
48. public static List<BOM> GetSpaBomChild(int ParentID)
49. {
50. SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
51. SqlCommand cmd = new SqlCommand("dbo.BOSS3_GetSpaBomChild", con);
52. cmd.CommandType = CommandType.StoredProcedure;
53. cmd.Parameters.Add("parentid", SqlDbType.Int).Value = ParentID;
54. List<BOM> bomList = new List<BOM>();
55. SqlDataReader dr;
56. try
57. {
58. con.Open();
59. dr = cmd.ExecuteReader();
60. while (dr.Read())
61. {
62. BOM bom = new BOM();
63. if (dr["bompartid"] != DBNull.Value)
64. bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
65. if (dr["bomlevel"] != DBNull.Value)
66. {
67. string l = dr["bomlevel"].ToString();
68. if (l.Trim().Equals("1"))
69. bom.BomLevel = "-" + l;
70. else if (l.Trim().Equals("2"))
71. bom.BomLevel = "--" + l;
72. else if (l.Trim().Equals("3"))
73. bom.BomLevel = "---" + l;
74. else
75. bom.BomLevel = "----" + l;
76. }
77. if (dr["avgcost"] != DBNull.Value)
78. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
79. if (dr["bomqty"] != DBNull.Value)
80. bom.BomQty = Convert.ToInt32(dr["bomqty"]);
81. if (dr["avgcost"] != DBNull.Value)
82. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
83. if (dr["stdcost"] != DBNull.Value)
84. bom.StdCost = Convert.ToDouble(dr["stdcost"]);
85. bom.BomUnit = dr["bomunit"].ToString();
86. bom.Description = dr["descript"].ToString();
87. bomList.Add(bom);
88. }
89.
90. }
91. catch (Exception ex)
92. {
93. throw ex;
94. }
95. finally
96. {
97. con.Dispose();
98. }
99. return bomList;
100. }
101. }


if you think you can help me speed this up please let me know. I can't have it run this slow

Thanks!

</code>

[twaldron]

I formated the code incorrectly, my apologies. I will see if this will look better

public List<BOM> GetBomList(string partno)
{

List<BOM> bomList = new List<BOM>();
BOM spaBom = BOMDB.GetSpaBomParent(partno);
bomList.Add(spaBom);
//get spalevel1
List<BOM> level1List = BOMDB.GetSpaBomChild(spaBom.BomPartID);
foreach (BOM l1 in level1List)
{
bomList.Add(l1);
//getlevel2
List<BOM> level2List = BOMDB.GetSpaBomChild(l1.BomPartID);
foreach (BOM l2 in level2List)
{
bomList.Add(l2);
//getlevel 3
List<BOM> level3List = BOMDB.GetSpaBomChild(l2.BomPartID);
foreach (BOM l3 in level3List)
{
bomList.Add(l3);
//get level 4
List<BOM> level4List = BOMDB.GetSpaBomChild(l3.BomPartID);
foreach (BOM l4 in level4List)
{
bomList.Add(l4);
}
}
}

}

return bomList;
}




public static BOM GetSpaBomParent(string partno)
{
SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
SqlCommand cmd = new SqlCommand("dbo.BOSS3GetSpaBomLevel0", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("partno", SqlDbType.NVarChar).Value = partno;
BOM bom = new BOM();
SqlDataReader dr;
try
{
con.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["bompartid"] != DBNull.Value)
bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
//indent the bomblevel string based on level number
if (dr["bomlevel"] != DBNull.Value)
bom.BomLevel = dr["bomlevel"].ToString();
if (dr["avgcost"] != DBNull.Value)
bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
if (dr["bomqty"] != DBNull.Value)
bom.BomQty = Convert.ToInt32(dr["bomqty"]);
if (dr["avgcost"] != DBNull.Value)
bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
if (dr["stdcost"] != DBNull.Value)
bom.StdCost = Convert.ToDouble(dr["stdcost"]);

bom.Source = dr["source"].ToString();
bom.Group = dr["componentgroup"].ToString();
bom.PartNo = dr["partno"].ToString();
bom.BomUnit = dr["bomunit"].ToString();
bom.Description = dr["descript"].ToString();

}
return bom;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Dispose();
}

}


/// <summary>
///
/// </summary>
/// <param name="ParentID"></param>
/// <returns></returns>
public static List<BOM> GetSpaBomChild(int ParentID)
{
SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
SqlCommand cmd = new SqlCommand("dbo.BOSS3_GetSpaBomChild", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("parentid", SqlDbType.Int).Value = ParentID;
List<BOM> bomList = new List<BOM>();
SqlDataReader dr;
try
{
con.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
BOM bom = new BOM();
if (dr["bompartid"] != DBNull.Value)
bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
if (dr["bomlevel"] != DBNull.Value)
{
string l = dr["bomlevel"].ToString();
if (l.Trim().Equals("1"))
bom.BomLevel = "-" + l;
else if (l.Trim().Equals("2"))
bom.BomLevel = "--" + l;
else if (l.Trim().Equals("3"))
bom.BomLevel = "---" + l;
else
bom.BomLevel = "----" + l;
}
if (dr["avgcost"] != DBNull.Value)
bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
if (dr["bomqty"] != DBNull.Value)
bom.BomQty = Convert.ToInt32(dr["bomqty"]);
if (dr["avgcost"] != DBNull.Value)
bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
if (dr["stdcost"] != DBNull.Value)
bom.StdCost = Convert.ToDouble(dr["stdcost"]);

bom.Source = dr["source"].ToString();
bom.Group = dr["componentgroup"].ToString();
bom.PartNo = dr["partno"].ToString();
bom.BomUnit = dr["bomunit"].ToString();
bom.Description = dr["descript"].ToString();
bomList.Add(bom);
}

}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Dispose();
}
return bomList;
}

[Arjay]

Can you do the join in a sproc and return the data in one result set?