0

I am building an application that requires me to query an AS400 database up to 100 times per single use.

I have functions for each of the queries right now written in the following structure.

Option Explicit On
Shared Function query1()
    Dim conn as New ADODB.Connection
    Dim recordSet1 as New ADODB.RecordSet
    conn.open("databaseName")
    recordSet1.Open("Select * From Table",conn)

    Return someValue
End Function

My application runs pretty slow, around 30-40 seconds. I've done some performance profiling using the tools built into Visual Studio 2013 and noticed that repeatedly opening connections to a database is taking up a significant portion of time.

My question is, can I set a global connection variable at the top level of my routine so that I only have to open the connection once, and close it once the routine is finished with all the queries? Do you think that this would significantly speed up my application?

Something like the following:

Option Explicit On
Global conn as ADODB.Connection
conn.open(DSN = "database")

Shared Function query1(ByVal conn)
    Dim recordSet1 as New ADODB.RecordSet
    recordSet1.Open("Select * From Table",conn)
    Return someValue
End Function

Thanks in advance for your help !

rOcelot
  • 1
  • 1
  • My guess would be the Com overhead for ADODB. I would bet that either OleDB or ODBC data access methods would work, and since they are designed to work with NET apps, should be faster. – Ňɏssa Pøngjǣrdenlarp Jun 02 '15 at 22:00
  • What do you mean by the "Com overhead for ADODB" ? Is that some sort of general method I can implement at the top level? At this point I dont know that I could switch over all of my queries to OleDB as I have a semi tight deadline. Apologies for my n00bness in advance. – rOcelot Jun 02 '15 at 22:12

0 Answers0