Fastest way for querieng multiple queries

Question

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.]

Are you talking about Java or C#? Since the code is C#, your question mentions Java.. — Erik Pragt, Jul 10 '13 at 09:54
I'd just get all the results in memory for your application if that's possible, and loop through it in the code and assign the values. I don't see how the temp table is going to help you in assigning values to the Element class, unless there are X million records in 'table' and you only need 20 of them. Then again, 20 calls can easily be replaced by an 'in' statement. (where something in..) — Erik Pragt, Jul 10 '13 at 09:57
How about join the values for your `where` condition into a single string then use a `where in` in your query? — whastupduck, Jul 10 '13 at 09:59
You can create sub sets of your `List` and try using [TaskFactory](http://msdn.microsoft.com/en-us/library/system.threading.tasks.taskfactory.aspx). So you can perform multiple task at the same time — huMpty duMpty, Jul 10 '13 at 10:00
"The fastest way" is highly DB-specific, and it depends on the query as well. You're using Oracle, right? — Sergey Kalinichenko, Jul 10 '13 at 10:00

Jodrell · Answer 1 · 2013-07-10T10:12:57.457

2

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.

edited Jul 10 '13 at 10:12

answered Jul 10 '13 at 10:06

Jodrell

34,946
5
87
124

whan i do: select value from table where something in (a,b,c) , doest it mean that select the value where something = n1 or n2 .... ?? also, how can i create this query in my code tough – susparsy Jul 10 '13 at 10:24
@susparsy, yes, that is what it means. However, if you are limiting across 100000 distinct values, this may not be the most efficient apporach. In fact, if there are more than 1000, it may not work http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause – Jodrell Jul 10 '13 at 10:42

score 1 · Accepted Answer · answered Jul 10 '13 at 10:11

1

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.

answered Jul 10 '13 at 10:11

Serge

6,554
5
30
56

whan i do: select value from table where something in (n1, n2,... ,n500) , doest it mean that select the value where something = n1 or n2 .... ?? also, how can i create this query in my code tough – susparsy Jul 10 '13 at 10:24
using String.Format + String.Join + a LINQ statement makes it quite easy. – Serge Jul 10 '13 at 10:28

score 0 · Answer 3 · answered Jul 10 '13 at 10:11

0

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

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

answered Jul 10 '13 at 10:11

Tamim Al Manaseer

3,554
3
24
33

Fastest way for querieng multiple queries

3 Answers3