C# - SQL Update keyword LAG!

E

EFileTahi-A

Well-known member
Joined
Aug 8, 2004
Messages
539
Location
Portugal / Barreiro
What could lead an simple "UPDATE _table SET _field = bla" query to lag? That is, When I execute this throught an OleDbCommand the record will only update about 2/3 seconds after.

Do I have a problem with my connection to the database?
What can cause update lag?

Help desperately needed...
Thanks in advance!
 
Can you define what you mean by lag? To me, youre saying the code finishes executing but the database doesnt reflect the change for a few seconds? Thats crazy!

Are you saying that the command takes a few seconds to run? Youll have to give us more info. Maybe youre updating 10,000 rows (you didnt specify a WHERE clause) and it just takes that long. Maybe youre saying that the code seems to be "doing something" for a few seconds before actually doing the update. Youll have to track that down, if youre seeing it. Maybe youre network connection is 56k modem connecting to a DB across the Atlantic.

Also, am I to assume that 2/3 is not two thirds, but 2 or 3?

-ner
 
Nerseus said:
Can you define what you mean by lag? To me, youre saying the code finishes executing but the database doesnt reflect the change for a few seconds? Thats crazy!

Are you saying that the command takes a few seconds to run? Youll have to give us more info. Maybe youre updating 10,000 rows (you didnt specify a WHERE clause) and it just takes that long. Maybe youre saying that the code seems to be "doing something" for a few seconds before actually doing the update. Youll have to track that down, if youre seeing it. Maybe youre network connection is 56k modem connecting to a DB across the Atlantic.

Also, am I to assume that 2/3 is not two thirds, but 2 or 3?

-ner
Click to expand...

Here is the code and I will describe in detail what happens:
Code: 
//*******************************************************************
// 7 - Updates the document number
//*******************************************************************
sSQL = "UPDATE DocLinTemp SET numdoc = " + this.txt_numDoc.Text + ", coddoc = " + this.txt_codDoc.Text + "";  
OleDbCommand cmd3 = new OleDbCommand(sSQL, DB_Engine.MyConnDataTemp);
cmd3.ExecuteNonQuery();


//MessageBox.Show(dtDocLinTemp.Rows[0]["numdoc"].ToString());


//*******************************************************************
// 8 - Copies the records from doclintemp to InDocLin
//*******************************************************************
sSQL = "INSERT INTO InDocLin SELECT * FROM doclintemp IN " + Application.StartupPath + "\\DataTemp.mdb" + "";
OleDbCommand cmd4 = new OleDbCommand(sSQL, DB_Engine.MyConnDataTemp);
cmd4.Connection = DB_Engine.MyConnData;
cmd4.CommandText = sSQL;
cmd4.ExecuteNonQuery();

See the deactivated MessageBox? When its ON the code executes with no prob, but when OFF both updated "numdoc" and "coddoc" will be blank in InDocLin table when the records are copied...

So my guess is that "UPDATE" query is not totaly processed by the time the program reaches "INSERT INTO" query... And we are talking about ONLY 1 record from DocLinTemp to InDocLin...
 
Last edited by a moderator:
Also, the Application.Events() does not resolve this issue...

Is there a way of performing a pause in the code? I used to use API.Sleep or somthing in VB6, only this freezes the CPU...
 
Is the database in step 7 the same as that pointed to in step 8? If it is, why use the "IN" keyword? Is there another need for this, such as having to do this across databases in the future? If so, let us know - maybe you need to "flush" Access before it commits the changes?

Also, above step 7, do you have any code that would start a transaction or do anything that would make you think its not committing the data immediately?

-ner
 
Hi nerseus... They were in diferent Access data bases with independent connenctions. That is DocLinTemp and InDocLin were from diferent Access Data Bases

I mean "were" because I am converting the program to mySQL, and I am no longer using the DataTemp database which was holding the DocLinTemp table, instead, I will use an DataTable image of InDocLin in memory (that explains the other thread of mine about copying rows between tables) instead of storing it on an independent DataBase...

This was the way the software am currently working on was done in VB6, and am converting it to C# and things can go diferent now...
 
I was asking because the SQL didnt look like it was doing what youd expect, if it were truly two different databases.

Suppose the SQL built in step 8 looks like this (I made up the path):
INSERT INTO InDocLin SELECT * FROM doclintemp IN c:\DataTemp.mdb

The table DocLinTemp is coming from the database c:\DataTemp.mdb, not the other one. At least, thats what my tests show.

I think the code is assuming that its using the DocLinTemp table from Step 7 - the one getting updated. I cant explain why the MessageBox would change this though.

Here was my test:
Create two databases: db1.mdb and db2.mdb.
db1 has table DocLinTemp ONLY
db2 has table InDocLin ONLY

Using similar code to yours I create a connection to db1 and perform the UPDATE. Then, still using that connection, I issue the INSERT INTO command that includes the "IN c:\db2.mdb" query. I get an error that table DocLinTemp doesnt exist.

So even though Im using a connection to db1 which has the table, it seems like the query isnt really using that table.

What do you make of that?

-ner
 
Back
Top