Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ADO vs. SQL

Status
Not open for further replies.

frothytoad

Technical User
Aug 1, 2003
32
US
I have VB code that accesses a MySQL database, and am hoping that someone can educate me on the following issue. There are two methods that are immediately obvious to me (although I am sure there are many others, probably more clever) for updating the database. The first is to open a recordset, such as using ADO, and manipulate the data. E.g.,

Set MyRST = New ADODB.Recordset
MyRST.Source = "SELECT * FROM MyTable WHERE ..."
...
MyRST.Open
MyRST.Add
...
MyRST.Update

The second would be to send an SQL statement more directly, e.g.:

strQry = "INSERT INTO MyTable VALUES (..."
MyConn.Execute strQry

My guess is that one of these is likely to be better than the other, but I am not sure which.

Any thoughts?

Thanks,

-- Jeff
 
The second method is better.

You may not notice performance issues with small tables, but when the table gets big, a Select * will take a lot of time, whereas the insert into method will always be quick.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
yeah do the second one...

Although for inserting purposes you can limit the size of your SELECT * resultset with a condition like WHERE 1 = 0

but that is kinda cheesey
 
The most favorite way is probably to make a stored procedure and then pass all the values to it... then you just have the name of the procedure in your compiled executable and you can tweak the actual insert/update logic without doing an update to all client machines.

I think the new verison of MySQL has stored procedures but I never worked wit it.
 
I agree with Sheco. SP's are the way to go. It also makes it easier to change if you need to do extra validation or for some reason table names, etc. change. You'll only need to re-compile your code if the parameters change.
 

Thanks for the feedback.

The other question, now, is what happens when I need to do this a few hundred or few thousand times. One way I have an open recordset with which I can loop rst.AddNew's, the other way I have to send multiple commands to the server (of course, it may be that the ODBC drivers are doing this anyway when I invoke each AddNew - I don't know).

Does that change anyone's thoughts??

-- Jeff
 
Forgot to mention - I am avoiding SP's because I have been designing the application (which is pretty much single user, btw, but may be multi-user in the future) to work with multiple db servers with only very minor changes to the VB app (like just changing the connect string).

-- Jeff
 
I support using SP with ADO parameters. Helps prevent SQL injection Attacks (see faq709-1526).

zemp
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top