Reg : Databse connection in vb 6.0

[praveen09]

Hi all

I have a doubt regarding the query execution time in vb(ODBC) because whenever I run query in VB6.0 interface its taking 5 min to execute a query while I run the same query using java (ODBC) it takes 5 sec to execute the Query

for more details here is my code

dim query as String

query = "371523"
     conn.ConnectionString = "DSN=mydsn;UID=test;PWD=test"
      conn.Open
       Set commandQuery.ActiveConnection = conn
         commandQuery.CommandText = "SELECT A.LAA_APP_ACCOUNTNO_C,A.CUST_ID_N, b.CUSTOMERNAME,c.LSO_OFFICE_NAME_C,A.APP_ID_C FROM LOS_APP_APPLICATIONS A,NBFC_CUSTOMER_M b,LOS_SEC_OFFICE c WHERE A.CUST_ID_N=b.CUST_ID_N AND A.LAA_BRANCHID=c.LSO_OFFICE_CODE_C AND A.APP_ID_C=" & query

       Set rsData = commandQuery.Execute
       
        MsgBox (rsData.EOF)

    conn.Close
    Set conn = Nothing

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

While you have shown some of your code, you are missing parts that are likely to be important - such as the declaration of the variables, and any code that deals with them before this part of the code runs.

In addition to that, is there a particular reason for using a DSN, rather an a full connection string?

[praveen09]

dear geek please find the code


Dim conn As New ADODB.Connection
Dim commandQuery As New ADODB.Command
Dim rsData As New ADODB.Recordset
Dim Prm As ADODB.Parameter
Dim query As String

Private Sub Command1_Click()



query = "371523"

conn.ConnectionString = "DSN=mydsn;UID=test;PWD=test"

conn.Open
Set commandQuery.ActiveConnection = conn

commandQuery.CommandText = "SELECT A.LAA_APP_ACCOUNTNO_C,A.CUST_ID_N, b.CUSTOMERNAME,c.LSO_OFFICE_NAME_C,A.APP_ID_C FROM LOS_APP_APPLICATIONS A,NBFC_CUSTOMER_M b,LOS_SEC_OFFICE c WHERE A.CUST_ID_N=b.CUST_ID_N AND A.LAA_BRANCHID=c.LSO_OFFICE_CODE_C AND A.APP_ID_C=" & query

Set rsData = commandQuery.Execute
rsData.MoveFirst

Do While Not rsData.EOF
MsgBox (rsData.Fields(0))
rsData.MoveNext
Loop

conn.Close

End Sub


regards
praveen

[si_the_geek]

That confirms one of the things I suspected, which will affect the speed etc... so I recommend reading the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum).


You should remove the line rsData.MoveFirst , because it should never be used immediately after opening a recordset - at best it will only waste time, and in bad cases it will create an error for no valid reason.


If the APP_ID_C field is not a numeric data type (such as Integer), you should make a change to your query to delimit the value appropriately for whatever the data type is (how depends on the data type, and the database system you are working with). This can make a massive difference to speed.


As implied by the question I asked before, usage of a DSN might cause a speed issue too - and so could the driver/provider it uses, and so could the file location/permissions. The more information you give about that kind of thing the better.


Another important point is to ensure that you are running a like-for-like comparison against the Java code. Are they both running on the same computer? ...and using the same DSN? ...and using exactly the same SQL statement? ...and running it in the same kind of way (rather than being Paged etc in the Java code)?