Solved

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

Posted on 2011-09-08
10
235 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 73

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 73

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
 
LVL 76

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36506025
0
 

Author Comment

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now