on running sql query through visual basic, it freezes

[Guest_imported]

i am running a query on the database using visual basic , since the tables are too big, everytime when i run the program, it takes a long time to execute and then freezes, what could i do about it.
thanks

 

[Nagrom]

Are you using ADO? You can "up" the timeout value so the query doesn't time out and throw you an error. This only prevents an error, however, it doesn't keep the query from "locking up" all your resources. You might want to consider making the query into a stored procedure (this can lessen the time required for it to execute) and look into the indexing on your tables (that can significantly affect the time it takes your queries to run). I was going to suggest making sure you have a "DoEvents" in your code, but since executing the query is only one instruction that wouldn't really help. If the bottleneck is the query itself you really need to look at the database side of it. I really think indexing is probably your best bet. I've seen that literally reduce the time required for a query to 1/1000 of the original time.

 

[Guest_imported]

thanks for your response, i am using dao, will it make any difference? my code goes something like this, and here _S4 is a big table with about 30000 records.

Private Sub Command2_Click()
Set db = OpenDatabase("dbtables.mdb&quot
Set db1 = OpenDatabase("dbS.mdb&quot
Set rs = db.OpenRecordset("SELECT * FROM _S4", dbOpenDynaset)
Do While Not rs.EOF
Set rs1 = db1.OpenRecordset("Select * from S4Mod where Date='" & rs.Fields("Field24&quot & "'", dbOpenDynaset)
On Error Resume Next
rs1.Edit
rs1!MTail = rs.Fields("Field9&quot
rs1!MHead = rs.Fields("Field6&quot

rs1.Update
rs.MoveNext
Loop
End Sub

 

[Nagrom]

I notice a couple of things here. First, DAO is pretty slow in and of itself. I highly recommend using ADO for any kind of data access over DAO. You will write more code, but it's a LOT faster. Another thing is the fact that you are using Access for your database. I'm not sure if there is any kind of "tweaking" you can do to return data faster in Access. Is this something that needs to run with a separate database on each client workstation, or will it be centralized. Also, do you have SQL Server (or any other more "robust" database server) available? I tend to prefer those because they are designed for larger amounts of data and more users than the desktop databases. I am not an Access expert by any means, though, so you might want to check the documentation from Microsoft to see if it provides any method for building indexes over the tables (or something else that will make it return data faster). Lastly, have you tried the query in Access directly? If your "Date" field is actually a date/time data type I think you have to surround the date string with the # sign when you specifiy it in your query (I am not 100% sure about that though).

 

[Guest_imported]

thanks,
i really appreciate your advice.
i changed to ado, however the program stil freezes, i am at a loss as to why this occurs. the date field is just a text i am using, not a date-time datatype.
Dim conn As ADODB.Connection
Dim conn1 As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim j As Integer
Set conn = New ADODB.Connection
Set conn1 = New ADODB.Connection
Set cmd = New ADODB.Command
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\dbS.mdb;Persist Security Info=False"
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\dbtables.mdb;Persist Security Info=False"
conn.ConnectionTimeout = 3000000
conn1.ConnectionTimeout = 1000000
conn.Open
conn1.Open

rs.Open "_S4", conn1
While Not rs.EOF
rs1.Open "SELECT * FROM S4Mod where Date='" & rs.Fields("Field24&quot & "'", conn, adOpenDynamic, adCmdTable
var = rs.Fields("Field6&quot
With rs1
.Fields("MHead&quot.Value = rs.Fields("Field6&quot
.Update
End With
rs.MoveNext
Wend
'Close the connection.
conn.Close
conn1.Close
Set conn = Nothing
Set cmd = Nothing

i am familiar with mysql database, however i have the huge files in excel and i dont know if i could import the files to mysql database as i could do it in access(.dbf) files.

i would appreciate any help.
thanks once again

 

[Nagrom]

Try running the query directly in Access and see if it takes an inordinate amount of time to return. It very much sounds like the slow response is due to Access taking a long time to prefer the query.

Try this - go into design view for the table and click on the DATE field. There should be a checkbox for "INDEXED". Check it so it creates an index based on that field. Try re-running your code after saving the database once you've made that change and see if that speeds it up.

 

[chiph]

Please see my reply in thread709-214800

Chip H.

 

[PCSAARON]

Get away from Access! Use mysql as a backend.... You will notice the difference RIGHT AWAY!

Aaron

 

[petermeachem]

Didn't read far enough down Chip.

The only thing you didn't mention was to link one of the tables to the other database.

No need to go to mysql, or sql server or ado.

I reckon that ado is slower than dao anyway.

Peter Meachem
peter @ accuflight.com