?
Solved

Need faster way to query database mutliple times (within For Loop currently)

Posted on 2011-09-08
10
Medium Priority
?
267 Views
Last Modified: 2012-08-13
I have an application that generates lists of tags. Once the lists are generated the user can generate Excel reports from the lists.

My problem is I want to query the database for all the tags in each list but the only way I know how is to use a For Loop and query the database for each and every tag. This is taking way too long.
For m = 0 To lstTags.Items.Count - 1
            currTagQry = "SELECT * from T_TEST WHERE TAG = '" & lstTags.Items(m) & "' ORDER BY TAG, LOCATION"
            Dim cmd6 As New System.Data.OleDb.OleDbCommand(currTagQry, conn6)
            Dim data6 As New System.Data.OleDb.OleDbDataAdapter(cmd6)
            ds_CurrTagQry.Reset()
            Try
                data6.Fill(ds_CurrTagQry, "CURRTAGQRY")
            Catch ex As Exception
            End Try

            For p = 0 To ds_CurrTagQry.Tables("CURRTAGQRY").Rows.Count - 1
                shtTags.Cells(lstExcelRow, 1).value = ds_CurrTagQry.Tables("CURRTAGQRY").Rows(p).Item("TAG").ToString()
            Next

        Next

Open in new window

0
Comment
Question by:KingMooBot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36504281
construct your sql to look like this....

SELECT * from T_TEST WHERE TAG in ('abc','def','ghi',...... ) ORDER BY TAG, LOCATION
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 36504307
or, pass your collection to a pl/sql table function and let it generate the data for you without requiring embedded literals
0
 
LVL 9

Expert Comment

by:sshah254
ID: 36504324
You need to insert code in the loop to get a sense of what is taking a long time ... is querying the db taking a long itme?  Or is inserting it into excel taking a  long time?

If the db query is taking a long time,

1.  Do you have appropriate indexes (on the Tag field)?  This will generally speed up the process.
2.  How far is the server (physically) from where you are running this code?  Is network latency an issue?
3.  If both of the above do not solve the problem, then you can run a single query that returns all the data and do the parsing in VBA.

SS
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36504343
Personally I wouldn't use a data adapter.  I have found them slow in the past.

If you are just looping through the results, I would change the SQL to the IN-List proposed above and use a DataReader.
0
 

Author Comment

by:KingMooBot
ID: 36504507
thanks. I tried the IN-List method that that seems to work a little faster.

I'll try passing the list to a temp table and see how that method works.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36506025
0
 

Author Comment

by:KingMooBot
ID: 36506865
i'm working with oracle
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 36508053
Why not use the Oracle data provider over the OleDB data provider? The latter being more generic, hence a tad slower.
0
 
LVL 15

Accepted Solution

by:
x77 earned 1600 total points
ID: 36514736
I work also with Oracle and has solved similar problem.

I found also solutions for similar problem here on EE with SqlServer.

The solution becomes from Stored procedures on Server.
Generic solutions send a string what contains many query that are execute at server side, then the server return result for all querys.

This becomes very eficient as the problem is you spend time on each call and you wait for each one.

Sample:

I have a query, as result for that query I need aditional information from other tables or same table for records what have dependencies with first result.

Then I need send individual query for each Key or other data information in primary query.
When those query are executed at server side, grouped the execution is very fast.

But I need create a stored procedure to allow send to it a group of keys and get the result on one call.

On most Bd you need gropy keys on an array and split it on server.

On oracle you can use Pls-Array. This is my Prefered solution.

 
Public ReadOnly WhereTCodTra As String = " Where CodTra in (Select * From Table(cast(v as TCodTra)));"

......
           If atr.Length > 0 Then
              Cmd = New OracleCommand("declare v TCodTra := TabCodtra(:T);" & _
                    "begin open :R for Select * from MyTable") & _
                    WhereTCodTra & " end;", db)
             Cmd.CommandType = CommandType.Text
             Cmd.BindByName = True
             Cmd.Parameters.Add(":T", OracleDbType.Varchar2, atr.Length, atr, ParameterDirection.Input). _
                CollectionType = OracleCollectionType.PLSQLAssociativeArray
             Cmd.Parameters.Add(":R", OracleDbType.RefCursor, ParameterDirection.Output)
             LoadTable(Td, Cmd)
           End If

Open in new window


I use this at client Side.
Note that I send an key array from aditional records I need.
The constant - WhereTCodTra - Contains the call to a Stored Method that converts the Pls-Array to a Oracle-Table (memory array acecpted as Table using Cast on oracle 9, on oracle-11  cast is not necesary)
Then the "in" clause allows me return all records selected from Keys array.

At server side, I need create some Types to allow Cast and a stored procedure / function that use those types.

 
CREATE TYPE "RECANET"."TCODTRA" AS TABLE OF VARCHAR2(10);

CREATE OR REPLACE  FUNCTION "RECANET"."TABCODTRA"  (T in Dbms_sql.varchar2s) return TCodTra is
  v TCodTra := TCodTra();
  Begin
    	v.extend(T.count);
    	for i in T.first..T.last loop v(i) := T(i); end loop;
      return V;
  end;

Open in new window


Note. atr is the array that contais all keys for records I need, I send it to Oracle as a PLSQLAssociativeArray.
The Stored function allows me use the array content as an Oracle Table.

I use a anonymous Pl-Sql sentence to use use the PLSQLAssociativeArray.
The result is a OracleDbType.RefCursor.
With this result  I can work like as a result from a Select Sentence.

LoadTable contains:

        cmd.FetchSize = &H100000
        T.Load (cmd.ExecuteReader (CommandBehavior.SequentialAccess))
        cmd.Parameters.Clear()

This is similar to : DA.Fill(T) - Where Da is a DataAdapter and T a DataTable.
0
 
LVL 15

Expert Comment

by:x77
ID: 36514791
Correction:

   ... from MyTable" & _

original contains more complex:  from " & TdTable("Td")  & _
I try to change TableName to a constant and do´nt remove the final ")"
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question