ExecuteNonQuery problems
-
Currently I have a huge insert method and from running OSQL it takes a few seconds to execute and inserts the many number of entries I require. However, having to use that query on C#, it times out and nothing gets inserted. This also happens when I try to use the Stored Procedure as well for the insertion (the procedure works on OSQL as well). Has anyone got ideas of how best to handle this because it shouldnt be taking longer from C# than from running OSQL.....
-
Currently I have a huge insert method and from running OSQL it takes a few seconds to execute and inserts the many number of entries I require. However, having to use that query on C#, it times out and nothing gets inserted. This also happens when I try to use the Stored Procedure as well for the insertion (the procedure works on OSQL as well). Has anyone got ideas of how best to handle this because it shouldnt be taking longer from C# than from running OSQL.....
You need to change the CommandTimeOut. That might work. 1.Public Function ExecuteSQLCommand(ByVal strSql As String) As Integer 2. InitializeConnection() 3. If con.State = ConnectionState.Open Then con.Close() 4. con.Open() 5. cmd = New SqlCommand() 6. cmd.Connection = con 7. cmd.CommandTimeout = 60 8. cmd.CommandType = CommandType.Text 9. cmd.CommandText = "delete from d where id > 10" 10. Return cmd.ExecuteNonQuery() 11. End Function
-
You need to change the CommandTimeOut. That might work. 1.Public Function ExecuteSQLCommand(ByVal strSql As String) As Integer 2. InitializeConnection() 3. If con.State = ConnectionState.Open Then con.Close() 4. con.Open() 5. cmd = New SqlCommand() 6. cmd.Connection = con 7. cmd.CommandTimeout = 60 8. cmd.CommandType = CommandType.Text 9. cmd.CommandText = "delete from d where id > 10" 10. Return cmd.ExecuteNonQuery() 11. End Function
Thats not what I wanted, I know it will run but its extremely slow of doing an Insertion by selects with nested inner joins. Doing it on OSQL seems to be in 5 seconds, doing it in C# is for 2 minutes 10 seconds, thats like 2 minutes and 5 seconds of something going on that I cant see. Is ExecuteNonQuery on SQL Command doing something to the text or the string, besides executing it? Cause its taking quite a long time to do it....
-
Currently I have a huge insert method and from running OSQL it takes a few seconds to execute and inserts the many number of entries I require. However, having to use that query on C#, it times out and nothing gets inserted. This also happens when I try to use the Stored Procedure as well for the insertion (the procedure works on OSQL as well). Has anyone got ideas of how best to handle this because it shouldnt be taking longer from C# than from running OSQL.....
-
Can you show us C# code that execute the huge insert. If you use the same stored procedure to insert big number of rows, you can use sqlCommand.Prepare(), where sqlCommand = new SqlCommand("MyStoredProcedure", myConnection)
I figured it out why it was slow, it was due to SQL Transactions.... Now gotta work out how to speed it up...... Basically what I did that slowed it down was an INSERT SELECT TO Statement, and no I didnt use stored procedures since I also tried that with the same outcome. Basically in PSEUDO form was more of a INSERT INTO table1 SELECT [blah blah blah] FROM #a INNER JOIN #b ON 1 = 1 INNER JOIN table2 ON a.[field] = table2.[field] AND [etc]. WHERE something = something