Best Approach To Handle Multiple Select Statements In A Single Trip To The Database

Question

I am thinking a transaction would help me in the following example but I could be wrong. What I am attempting to avoid is making 3 different calls to the database if I dont have too in the following scenerio..

I want to do the following:

select name, address from employee
select state from states
select error from errorTable

In this case I have to call several different selects to get data. What is the best way to approach this scenerio to return all the data I want to read minimizing several calls to the database?

Note: No tables have a relationship between them.

What's the relationship, if any, between the records you're fetching? Can you edit some schema information into the question? — svidgen, Jul 23 '13 at 19:33
no relationship between these tables. That is where my confusion on how to handle this scenerio stems — Nick LaMarca, Jul 23 '13 at 19:35
If you are calling these three queries many multiples of times, then perhaps look at prepared statements to minimise the overhead of repeated queries. If you only call these once each, this feels like a case of optimising in the wrong place. Have you quantified the overhead of three queries and established that it really is a cause of issues? — MatBailie, Jul 23 '13 at 19:42
Not sure why you are trying to run them in single iteration? There is no relation b/w the tables and hence can't be logically grouped. — Rahul, Jul 23 '13 at 19:45
What's your front end? ADO that supports multiple result sets? SSMS? Something else? Also, going along with @MatBailie aren't states pretty static and something you would want to cache ? — EBarr, Jul 23 '13 at 19:47
I need to crosswalk data. So data comes in as "123" I need to look at a certain table and get the translation, like our system for name "123" means "ABC". There are many refereance tables like this that are Not related — Nick LaMarca, Jul 23 '13 at 21:16
One table holds employee codes, another table holds state codes, etc.. in the state code table fields would be InState, Description, TransState and in the employee code table the schema would be SourceEmCode, Description, TransEmCode..This is just an example but they are not releated — Nick LaMarca, Jul 23 '13 at 21:25

score 2 · Accepted Answer · answered Jul 23 '13 at 19:40

2

If there is no relation between the tables you could do a kind of

Select [Your selected columns]
From (select * from table1 where [conditon for table1]) t1
Inner join (select * from table2 where [condition for table2]) t2 on 1=1 
Inner join (select * from table3 where [condition for table3]) t3 on 1=1

This makes one trip to the database.

answered Jul 23 '13 at 19:40

Luis LL

2,912
2
19
21

And if query 1 returns 10 records, query 2 returns 20 records and query 3 returns 30 records, then you'll get 6000 records back. That's not what I'd call optimisation. – MatBailie Jul 23 '13 at 19:43
@MatBailie good point. I would work fine for one row per table. And It's bad optimization for multiple rows in multiple tables. – Luis LL Jul 23 '13 at 19:51
Why can't you return multiple sets from sql server and capture them in a data reader? – JeffO Jul 23 '13 at 21:45

score 1 · Answer 2 · answered Jul 23 '13 at 19:45

1

I think you can use reader.NextResult() to iterate through result sets. An example borrowed from codeproject:

connection.Open();
dataReader = command.ExecuteReader(); // a multi-select query
while (loopResult) {
   stringBuilder = new System.Text.StringBuilder();
   while (dataReader.Read()) {
      stringBuilder.AppendLine(dataReader.GetInt32(0).ToString());
   }
   System.Windows.MessageBox.Show(stringBuilder.ToString(), "Data from the result set");
   loopResult = dataReader.NextResult();
}

But, I'm not sure what the rationale is for grouping queries for unrelated data. It'll likely make your project harder to understand and maintain.

answered Jul 23 '13 at 19:45

svidgen

13,744
4
33
58

@I don't see if the OP has mentioned that he is using .NET. So, why this solution. – Rahul Jul 23 '13 at 19:46
@Rahul It's tagged with sql-server ... I made an assumption. – svidgen Jul 23 '13 at 19:48
yes tagged as sqlserver and not .NET. Moreover, OP asking for TSQL solution/suggestion and .NET. – Rahul Jul 23 '13 at 19:49
@Rahul Yeah. Also with `t-sql`. Generally when someone's running `t-sql` against `sql-server` they're using .NET. I could certainly be wrong ... It's a reasonable assumption though, isn't it? – svidgen Jul 23 '13 at 19:52
Im using .Net, its fine assuption – Nick LaMarca Jul 23 '13 at 19:55
@NickLaMarca if you are running .NET then tag it and how is this not the best answer. Per the comment the accepted answer will return multiple rows. svidgen since the tables return different data can't use a single loop. – paparazzo Jul 23 '13 at 20:32
I need to crosswalk data. So data comes in as "123" I need to look at a certain table and get the translation, like our system for name "123" means "ABC". There are many refereance tables like this that are Not related – Nick LaMarca Jul 23 '13 at 21:18

score 0 · Answer 3 · answered Jul 23 '13 at 19:30

0

You might want to consider using a stored procedure.

answered Jul 23 '13 at 19:30

mti2935

11,465
3
29
33

what if thats not an option? Also can you show me a quick sample of a sp with these sample selects? – Nick LaMarca Jul 23 '13 at 19:31
If a sp is not an option, then you might want to think about using a `union` to couple a few select statements together. For example: select column1,column2 ,column3 from table1 union select column1,column2 ,column3 from table2 The only caveat to this is that the number of columns must be the same for all queries. – mti2935 Jul 23 '13 at 19:36
Errr, and the same data types. Errr, and you need a way to identify which row is from which query. Errr, and you may have network traffic overhead due to communicating that additional column. Errr, and additional traffic communicating NULLs where columns exist in one record set but not others. Errr, and confusion caused by potentially forcing data into fields with the same type but the wrong name. Yeah,just the one caveat... – MatBailie Jul 23 '13 at 19:38

score 0 · Answer 4 · answered Jul 23 '13 at 19:32

0

You could try select query with JOIN table if there is relationship between them

answered Jul 23 '13 at 19:32

Miller

1,096
9
22

no tables have a relationship between them – Nick LaMarca Jul 23 '13 at 19:33
Join is not a solution since most probably he is trying to query different tables – Rahul Jul 23 '13 at 19:35
@MillerKoijam - That's simply a cartesian product. Are you ***really*** suggesting a CROSS JOIN of three different record sets??? – MatBailie Jul 23 '13 at 19:44
Probably, SP with multiple select query is the only option left. – Miller Jul 23 '13 at 19:56

score 0 · Answer 5 · answered Jul 23 '13 at 19:33

0

I assume you're using ASP.Net 4.0...

You can "chain" the queries together by doing something like this:

select name, address from employee;select state from states;select error from errorTable;

And then fill a DataSet like this:

string sql = "...";
SqlCommand comm = new SqlCommand(sql, con);
SqlDataAdapter adpr = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
adpr.Fill(ds);

answered Jul 23 '13 at 19:33

Greg

8,574
21
67
109

yea I thought so too the queries will run seperatly not a single call – Nick LaMarca Jul 23 '13 at 19:38
It is my understanding that will make 1 call to the db and return the 3 tables/results... – Greg Jul 23 '13 at 19:41

score 0 · Answer 6 · answered Jul 23 '13 at 21:28

If you can't use a stored procedure, then you have on option: Execute multiple select statements in a single batch. This 'batch' is processed as a single entity by SQL Server, returning multiple result sets. Once you have that, you can

Read it via a SqlDataReader, thus:

using ( SqlConnection connection = new SqlConnection(connectionString))
using ( SqlCommand cmd = connection.CreateCommand() )
{

  cmd.CommandText = " select * from foo"
                  + " select * from bar"
                  + " select * from baz"
                  + " select * from bat"
                  ;
  cmd.CommandType = CommandType.Text; ;

  using ( SqlDataReader reader = cmd.ExecuteReader() )
  {
    int n = 0 ;

    do
    {

      Console.WriteLine( "Result set #{0}", ++n ) ;
      int rows = 0 ;
      while ( reader.Read() )
      {
        ++rows ;
        for ( int i = 0 ; i < reader.FieldCount ; ++i )
        {
          object o1 = reader[i] ; // get object by ordinal column number
          string columnName = reader.GetName(i) ;
          object o2 = reader[columnName];
        }
      }
      Console.WriteLine("  {0} rows", rows ) ;

    } while ( reader.NextResult() ) ;
  }

Read it via a SqlDataAdapter, thus:

DataSet results = new DataSet() ;

using ( SqlConnection connection = new SqlConnection(connectionString))
using ( SqlCommand cmd = connection.CreateCommand() )
using ( SqlDataAdapter adapter = new SqlDataAdapter(cmd) )
{
  cmd.CommandText = " select * from foo"
                  + " select * from bar"
                  + " select * from baz"
                  + " select * from bat"
                  ;
  cmd.CommandType = CommandType.Text; ;

  connection.Open() ;
  int rowcount = adapter.Fill(results) ;
  connection.Close() ;

}

Process(results) ;

score 0 · Answer 7 · answered Jul 23 '13 at 21:43

There's no reason you can't return multiple sets from sql server.

select name, address from employee;
select state from states;
select error from errorTable;

User a sql reader and getting the .NextResult will go to the next dataset.

http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm

Best Approach To Handle Multiple Select Statements In A Single Trip To The Database

7 Answers7