Database

Re: Getting id of last inserted row

5-Mar-07 9:15

In MS SQL Server you can call the function SCOPE_IDENTITY() and it will return the identity of the row you just inserted.

Hope that helps.
Ben

Marcus J. Smith5-Mar-07 9:16

Marcus J. Smith

5-Mar-07 9:16

Just set a parameter equal to @@IDENTITY.

_Cle^aKO

"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)

Re: Getting id of last inserted row

error14085-Mar-07 9:31

error1408

5-Mar-07 9:31

I found a solution
select gen_id(<generator>, 0) from rdb\$database

@cleako
Could you post an small example? I'm new to programming with databases.
Thank you

Scripting a SQL database

gantww5-Mar-07 5:20

gantww

5-Mar-07 5:20

Hello all,
Does anyone know of a good way to script off a SQL server database, including the schema and data? I'd like to be able to do this to make it easy for me to integrate with an existing project without touching the live server. Any ideas?

Re: Scripting a SQL database

Mike Dimmick5-Mar-07 5:27

Mike Dimmick

5-Mar-07 5:27

SQL Server has built-in facilities for scripting the schema, but does not do data.

We have Red Gate's SQL Compare and SQL Data Compare, the latter tool can do data export in script I believe.

Stability. What an interesting concept. -- Chris Maunder

MySQL slow inserting 10,000+ records

jds12075-Mar-07 4:27

5-Mar-07 4:27

I have a program (VB.net) that inserts a large amount of records (10,000 records +) into a MySQL database. It takes forever to import these records.

Anyone got some ideas on how to speed this insert up?

kubben5-Mar-07 5:04

5-Mar-07 5:04

If your current code opens the connection
then does a single insert
Then closes the connection
and then repeats this process 10,000 times.

I would suggest that you open the connection once
do the 10,000 inserts
finally close the connection.
That would be a lot faster.

Next I am not sure if MySQL support stored procedures (I usually use MS Sql Server) If it does a prepared stored procedure will execute faster then in line sql insert.

Next if you have lots of indexes and foreign keys etc. Consider dropping these do all the inserts then add the keys etc after the inserts are done. Of course, this is more work and would probably have to be done off hours, but lots of keys etc can cause the inserts to take longer.

Anyway, a few ideas, hope it helps.
Ben

jds12075-Mar-07 5:28

5-Mar-07 5:28

Here is my current code and could you modify this to reflect the open connection and close connection like you said?

Dim myConnString As String = My.Settings.ConnectionString
Dim MyConnection As New Odbc.OdbcConnection(myConnString)
MyConnection.Open()

Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('" & OrderDate & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & OrderItemQtyDtRec & "','" & OrderItemBO & "','" & OrderItemBODt & "','" & OrderItemCanceled & "','" & OrderItemCanceledDt & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "')")

MyCommand.Connection = MyConnection
MyCommand.ExecuteNonQuery()
MyConnection.Close()

kubben5-Mar-07 5:32

5-Mar-07 5:32

I am guessing that you have a loop outside of the code you posted, so I would do something like this:

Dim myConnString As String = My.Settings.ConnectionString
Dim MyConnection As New Odbc.OdbcConnection(myConnString)
Try
MyConnection.Open()

'your loop code here

Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('" & OrderDate & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & OrderItemQtyDtRec & "','" & OrderItemBO & "','" & OrderItemBODt & "','" & OrderItemCanceled & "','" & OrderItemCanceledDt & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "')")

MyCommand.Connection = MyConnection
MyCommand.ExecuteNonQuery()

'bottom of your loop code

Finally
MyConnection.Close()
End Try

That way you only open the connection once then do all your inserts, finally close the connection.

Hope that helps.
Ben

jds12075-Mar-07 5:57

5-Mar-07 5:57

But what if I have the loop code and the insert statement in two different functions?

kubben5-Mar-07 6:06

5-Mar-07 6:06

Pass in the connection object into the function that does the insert.
So you have:
outter function
Dim myConnString As String = My.Settings.ConnectionString
Dim MyConnection As New Odbc.OdbcConnection(myConnString)
Try
MyConnection.Open()

'loop goes here

'Call inner function pass in MyConnection

'bottom of loop

Finally
MyConnection.Close()
End Try

You innner function looks like:
Private Sub InnerFunc(p_MyConnection As Odbc.OdbcConnection)

Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('" & OrderDate & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & OrderItemQtyDtRec & "','" & OrderItemBO & "','" & OrderItemBODt & "','" & OrderItemCanceled & "','" & OrderItemCanceledDt & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "')")

MyCommand.Connection = p_MyConnection
MyCommand.ExecuteNonQuery()
End Sub

Hope that helps.
Ben

jds12075-Mar-07 9:04

5-Mar-07 9:04

Question, when I run the program on my computer the files will insert pretty quick. But when I run it on the computer that I installed the program on, I run into some problems. After the program run for about 1 minute or so an error would occur that says cannot connect to MySQL server. However, when I disable windows firewall the program will run without throwing the error but the data inserts slow. It takes about 20 min for the first file to insert on my computer, while on the other it took about an hour. Any ideas why it works fine on my computer and not the other? Is disabling the firewall making it run slow? It's very confusing.

kubben5-Mar-07 9:13

5-Mar-07 9:13

If you have a local MySql database on your local computer, that will always run faster then over the network. It also may help if your local table is empty or doesn't have many records.

I am not sure why disabling the windows firewall causes the program to run. I am guessing for some reason your firewall is preventing access to the sql server.

So on the other computer, if it is going across the network it will be slower then your local PC writings to a local database. It also might be an issue if you already have lots of records on the other MySQL database the inserts can take a longer time. This gets into my suggestion of dropping indexes and some things like that.

Hope that helps.
Ben

jds12076-Mar-07 3:38

6-Mar-07 3:38

If I install MySQL locally on the computer which I installed the program, will the data import faster?

kubben6-Mar-07 3:40

6-Mar-07 3:40

Yes if you are inserting into the local MySql database. Inserts will always be faster if you don't have to go across the network. If you are still inserting across the network the installing MySql will not help the speed. What helps the speed is that the program and the database it is inserting into are on the same computer.

Ben

Mike Dimmick6-Mar-07 2:06

Mike Dimmick

6-Mar-07 2:06

It sounds like you're using the ODBC driver layer to communicate with MySQL. You may get better performance with Connector/Net[^], the native .NET data provider for MySQL.

Also, consider preparing the statement - create the command object once, and call Prepare on it. This means that the server only has to parse the query once, not repeatedly. Don't forget to Dispose the command object once you've finished with it.

Stability. What an interesting concept. -- Chris Maunder

jds12076-Mar-07 3:44