Post reply

Data sent in Order over network is not in order in SQL Database

  • November 18, 2005 at 10:56 am

    #166349

    I wrote a program in C# that sends data to a database.

    It generates about 100 SQL statements in less then 1 second, and sends about 10,000 statements over the network as quickly as it can. It sends each statement by itself, as it is ready.

    It creates and calls the Insert command for each record.

    The data I sent was In Order from the C# side.

    I have a key field (recordNumberKey) which is a long, that increments for every record. I.e. 1, 2, 3, ...N.

    However when I looked in the database, it was not in order. It seams to jump around.

    I was expecting to see recordNumberKey 1 at position 1 in the database, and recordNumber 2 at position 2 in the database,

    and so fourth and so on.

    When the datarate is decreased it seams to work just fine.

    When I sort the database using:

    "select * from mydatabaseTable ORDER by recondNumberKey ASC"

    All the data is there and in order. Is this normal? Am I doing something wrong? My specification requires that I be able to add 100 records within a second.

    Why is it not in the order I sent it?

    I am confident I am sending it in order, I am using loop that takes each element out one by one.

    The program creates the command below and sends it in C#

    string strSqlCommand = "";

    strSqlCommand += "Insert into ";

    strSqlCommand += this.strDatabaseName + ".dbo.results (";

    strSqlCommand += "recordNumberKey, ";

    strSqlCommand += "Lat, ";

    strSqlCommand += "Lon, ";

    strSqlCommand += "velocity, ";

    strSqlCommand += "observer1, ";

    strSqlCommand += "observer2, ";

    strSqlCommand += "observer3, ";

    strSqlCommand += "observer4, ";

    strSqlCommand += "type1, ";

    strSqlCommand += "type2, ";

    strSqlCommand += "type3, ";

    strSqlCommand += "SecondsTimeStamp, ";

    strSqlCommand += "NanoSeconds";

    strSqlCommand += ") VALUES (";

    strSqlCommand += longRecordNumber + ",";

    strSqlCommand += dblLatitude + ",";

    strSqlCommand += dblLongitude + ",";

    strSqlCommand += dblVelocity + ",";

    strSqlCommand += intObserver1 + ",";

    strSqlCommand += intObserver2 + ",";

    strSqlCommand += intObserver3 + ",";

    strSqlCommand += intObserver4 + ",";

    strSqlCommand += inttype1 + ",";

    strSqlCommand += bytetype2 + ",";

    strSqlCommand += bytetype3 + ",";

    strSqlCommand += longEpcohTimeInSeconds + ",";

    strSqlCommand += longNanoSeconds + ")";

    SqlCommand sqlCommand = new SqlCommand(strSqlCommand, this.sqlConnection);

    sqlCommand.CommandTimeout = 0;

    try

    {

    int rowsEffected = sqlCommand.ExecuteNonQuery();

    }

    catch(Exception e)

    {

    Console.WriteLine("<" + e.ToString() + ">" );

    this.sqlError = true;

    }

    I am using MSSQL 2000 on a MS Server 2003 machine.

    Thanks for any help on this.

  • November 18, 2005 at 11:26 am

    #604706

    - Rob - you're not doing anything wrong...best way to understand this is to follow this link







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 21, 2005 at 4:48 am

    #604900

    As an aside. Consider moving the insert into a stored procedure and calling the stored procedure from your app. It's more efficient and more secure.

    As written, your code is potentialy vulnerable to SQL Injection

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 22, 2005 at 1:09 pm

    #605316

    Thanks Sushila, the coment you wrote, and link you sent was very helpful. I feel much better about it now.

    Thanks GilaMonster, I'll consider moving the data to a stored procedure.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply