Solved

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

Posted on 2011-09-08
10
254 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
  • 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 100 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

733 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