ADO vs. SQL

[frothytoad]

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

 

[gmmastros]

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

 

[Sheco]

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

 

[Sheco]

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.

 

[macleod1021]

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.

 

[frothytoad]

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

 

[frothytoad]

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

 

[zemp]

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

zemp