Avoid nesting of query in C#

Question

Currently i am having a C# code to display data in gridview which is based on several conditions. So, i have to the nesting of gridview. My code is something like this.

con.ConnectionString = ConfigurationManager.ConnectionStrings["mySQLConnection"].ToString();
con.Open();

            SqlCommand cmd = new SqlCommand("SELECT uname FROM vusers", con);
            var dr = cmd.ExecuteReader();


            if (dr.HasRows)
            {
                while (dr.Read())
                {


        con1.ConnectionString = ConfigurationManager.ConnectionStrings["mySQLConnection"].ToString();
        con1.Open();

                SqlCommand cmd1 = new SqlCommand("SELECT mydata FROM vdata where uname="+dr["uname"].ToString(), con1);
                var dr1 = cmd1.ExecuteReader();


                if (dr1.HasRows)
                {
                        while (dr1.Read())
                        {
                            getmydata = dr1["mydata"].ToString();
                    }
                }

        con2.ConnectionString = ConfigurationManager.ConnectionStrings["mySQLConnection"].ToString();
        con2.Open();

                SqlCommand cmd2 = new SqlCommand("SELECT mynewdata FROM vnewdata where uname="+dr["uname"].ToString(), con2);
                var dr2 = cmd2.ExecuteReader();


                if (dr2.HasRows)
                {
                        while (dr2.Read())
                        {
                            getmynewdata = dr2["mydata"].ToString();
                    }
                }
                }
            }

In the above code, from the outer SQL query, i am getting uname & on the basis of uname, i am getting data from 2 inner SQL queries. It is 2 slow & time consuming process for a big database. How can i prevent the 2 inner query in the loop from execution. How can i make it efficient & less time and resource consuming?

Simple first step:you do not need 3 Connections. you can use the same connection for all queries. This will save a few milliseconds in each Loop. — Thomas Krojer, Apr 14 '15 at 06:28
I am already using views not the tables in all the queries. If i further use joins, then i will make it much slower — Harshit, Apr 14 '15 at 06:31
`SELECT uname, mydata,mynewdata FROM vusers u JOIN vdata v ON u.uname = v.uname JOIN vnewdata n ON u.uname = n.uname` - that's one request instead of 3 — fubo, Apr 14 '15 at 06:32
@bogojane Try to use join and remove more than one connection — A.Goutam, Apr 14 '15 at 06:33
You would better return to tables level or build one more view based on your joined select. Joining on text fields is very bad idea in design perspective and it badly impacts performance as well. Hope that tables underneath use int IDs, primary and foreign keys, which are to be used to join tables on. — Paul Kyrejto, Apr 14 '15 at 06:37
Also user parameterized queries to avoid sql injections http://stackoverflow.com/a/17512230/1004522 — Ebad Masood, Apr 14 '15 at 06:39
Revisit your view design or investigate stored procedures. 1 Query should be faster than multiple queries. Work with id's over strings wherever possible. — Jon P, Apr 14 '15 at 07:01
@bogojane Batter if u add some more info like `View` your schema ..etc. bcoz as you told that those result came from `View` — Anant Dabhi, Apr 14 '15 at 07:31

score 0 · Answer 1 · answered Apr 14 '15 at 06:39

0

You not need three connection object and run query three time you can achieve those value in single query

           SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["mySQLConnection"].ToString();
            con.Open();
            // As per @fubo's comment
            SqlCommand cmd = new SqlCommand("SELECT uname, mydata,mynewdata FROM vusers u JOIN vdata v ON u.uname = v.uname JOIN vnewdata n ON u.uname = n.uname", con);
            using (var dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        var getmydata = dr["mydata"].ToString();
                        var getmynewdata = dr["mydata"].ToString();
                    }
                }

answered Apr 14 '15 at 06:39

Anant Dabhi

10,864
3
31
49

and also con.Close(); – Ebad Masood Apr 14 '15 at 06:41
vdata,vusers,vnewdata are already views made from different tables. So, i dont want to further use joins, it will make it even slower. I am looking for a way to make it efficient in case of time & resources. – Harshit Apr 14 '15 at 06:45
1

@bogojane, using joins is very good practice. I do not understand why - in your opinion - using joins is less time and resources efficient then other technics. Note, that above code should use less resources, because entire job is made on server side - once, instead of three times! – Maciej Los Apr 14 '15 at 07:13
1

@bogojane Do you have evidence for that statement? Wild guesses are generally not useful in optimization. Try looking at the execution plan, and figure out why it's not doing what you expect it to. It might be that you're missing an important index, or that the index is incorrect / not uptodate. You might want to use schema-bound views. You might want to use intermediate table variables in the database (very useful when you want to split apart a complex query where MS SQL just gives up). – Luaan Apr 14 '15 at 07:13
@MaciejLos Well, I've been there before. There's limits to what the execution planner will even attempt to optimize. I've once had a query with about 20 consecutive joins (e.g. each depending on the previous one), and the planner just gave up. Separating the query in two (on the database level, of course, not the client-side) meant a huge leap in performance. – Luaan Apr 14 '15 at 07:15
@Luaan, 3 vs. 20 makes BIG difference. Looping through.. two result sets to get another portion of data is poor idea. In my opinion - of course. – Maciej Los Apr 14 '15 at 07:20
1

@MaciejLos The OP was quite explicit that the views he's selecting from already contain joins, and the cost of joins usually propagates over view boundaries. Now, I'm not saying I actually believe the OP when he says it's going to be slower (if it did, he would give the concrete data and the plan supporting his assertion), but if he does in fact test it and it is in fact slower, there are plenty of reasons why this might be the case. In any case, looping through the result sets has to be a terrible thing to do, and a signal of very bad DB design and poor understanding of MS SQL. Oh well. – Luaan Apr 14 '15 at 07:28
My answer is just @fubo's comment and I added few code from my side and I think it definitely faster then getting value from first result and use in second query and so on.. Also we dont know OP table structure and Index and other fields .. If we have we definitely make batter answer. – Anant Dabhi Apr 14 '15 at 07:30
@Luaan, thank you for clarification. Agree. As per my first reading of your previous comment, i wasn't sure what you mean... Cheers, Maciej – Maciej Los Apr 14 '15 at 10:47

Avoid nesting of query in C#

1 Answers1