locked
Updating Identity Columns (Auto Increment) to Database Very Slow RRS feed

  • Question

  • When using auto increment ID columns in my dataset, it takes a very long time to update to the local MS SQL database (I usually get bored waiting or the operation times out before completing).

    The same operation, using the same data, on the same tables with the auto-increment feature turned off updates within about two seconds. 

    The auto-increment column shares foreign key constraints with two other tables.  

    I would really like to use auto increment keys. Is there any way to make the updates perform faster? 

    Thursday, October 4, 2012 9:27 PM

Answers

  • No replies, so I wrote a simple class to create auto values client-side. I don't know if this is the best solution, but it is a lot faster than waiting for the database to covert temp values. 

    public class AutoIncreamentID
        {
            public AutoIncreamentID()
            {
    
            }
    
            public static int GetAutoId(DataColumn idColumn)
            {
                DataTable dt = idColumn.Table;
                String cn = idColumn.ColumnName;
    
                DataRow[] maxRow = dt.Select(cn + " = MAX("+cn+")");
                int value = 0;
    
                try
                {
                    value = Convert.ToInt32(maxRow[0][cn]) + 1;
                }catch
                {
                    return 0;
                }
    
                return value;
            }
    
        }

    • Marked as answer by A.Russell Saturday, October 6, 2012 11:10 AM
    Saturday, October 6, 2012 11:10 AM

All replies

  • ...some more information: 

    Obviously, it takes a very long time to change the temporary values to permanent ones. 

    I am using the TableAdapterManager Class to update the database: 

    DataSet1TableAdapters.TableAdapterManager tam = new DataSet1TableAdapters.TableAdapterManager();
                tam.LexiconNamesTableAdapter = lexiconNamesTableAdapter;
                tam.FamiliesTableAdapter = familiesTableAdapter;
                tam.MemberWordsTableAdapter = memberWordsTableAdapter;
    
                
    
    tam.UpdateAll(dataSet1);

    Works incredibly slow with auto-increment; fast without.

    Friday, October 5, 2012 6:37 AM
  • No replies, so I wrote a simple class to create auto values client-side. I don't know if this is the best solution, but it is a lot faster than waiting for the database to covert temp values. 

    public class AutoIncreamentID
        {
            public AutoIncreamentID()
            {
    
            }
    
            public static int GetAutoId(DataColumn idColumn)
            {
                DataTable dt = idColumn.Table;
                String cn = idColumn.ColumnName;
    
                DataRow[] maxRow = dt.Select(cn + " = MAX("+cn+")");
                int value = 0;
    
                try
                {
                    value = Convert.ToInt32(maxRow[0][cn]) + 1;
                }catch
                {
                    return 0;
                }
    
                return value;
            }
    
        }

    • Marked as answer by A.Russell Saturday, October 6, 2012 11:10 AM
    Saturday, October 6, 2012 11:10 AM
  • Hi Russell,

    I'm glad you solved the issue. Thank you for sharing the solution with us.

    Good day.


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 10, 2012 5:10 AM