Solved

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

Posted on 2011-09-08
10
240 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Allow User To Arrange Columns At Datagridview 3 19
Exit the loop 4 39
VB.NET Repostiory Pattern 7 16
Crystal reports vb.net 2 20
'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 …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

867 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

22 Experts available now in Live!

Get 1:1 Help Now