CODEWITHSUNDEEP
c#sql
susparsy
susparsy

Reputation: 1046

Fastest way for querieng multiple queries

What i need to do is this: I got an array with lets say 100000 values, For each value i need to make the same query ,just change that specific value. Now, i am thinking that if i loop all this values in my c#/ java code and reach for a query it would take a lot of time. My other option is doing all the work in my db, populate a temp table and than reading back in my code from that temp table.

What is the fastest way of doing such thing?

private void GetValues(List<Element> _Elements)
    {
        foreach (Element e in _Elements)
        {
            using (OracleCommand cmd = new OracleCommand())
            {
                cmd.Connection = _conn;
                cmd.CommandText = "select value from table where something = " +e.Indicator;
                using(OracleDataReader r = cmd.ExecuteReader());

                   while (r.Read())
                   {
                       e.setvalue = r.GetString(1);
                   }

                   r.Close();
                }
            }
        }
    }

[Editor note: question was originally unclear as to whether it was C# or Java -- but the languages are largely equivalent, and answers should be applicable to both.]

Upvotes: 1

Views: 237

Answers (3)

Jodrell
Jodrell

Reputation: 35716

Do one query,

select value from table where something between min and max;

or

select value from table where something in (a, b, c, ... );

or

select value from table where something in 
    (select things from tempTable);

or

select value from table where something in
    (select things from @tablevariable);

whichever of these approaches is most applicable to your database and problem.

Repeating all the processing over and over, than amalgamating the results must be slower than taking a set based approach in the first place.

It all rather depends on on the type and distribution of the Indicator property of the Element List.

Upvotes: 2

Serge
Serge

Reputation: 6692

The faster way is to use a dynamic query. In a loop, you build up a statement to make use of several values at once.

In the sample you gave, it mean building up statements like those.

  • query1: select value from table where something in (n1, n2,... ,n500)
  • query2: select value from table where something in (n501, n502,... ,n1000)
  • etc.

You may not have to make several queries at all depending of the (characters?) limitations you face.

Upvotes: 1

Tamim Al Manaseer
Tamim Al Manaseer

Reputation: 3724

There are a lot of optimiziation tips, but for your specific case, here are 2:

  1. Try to do everything in one go, @Jodrell suggested very good ideas for that.
  2. Retrieve the smallest possible data from the db, select only the fields you need.

Upvotes: 0

Related Questions