Database Update Very Slow due to many rows???

[Jigabyte]

Hi Peeps,

I have a auditing program which is run on a PDA with WM5. It records data in several tables and the data is output as XML files and sent via mobile to a FTP server. One of my tables has 17000 rows in it now and when I run my recovery routine it takes 4 seconds to do the database update routine for every row. My recovery program un tags rows with a specified audit date.

Is there any reason why this should occur or is there a better way of updating the live database?

Cheers,

Jiggy!

[Jigabyte]

This is also proving a problem when I sync the data because I have to change the row status column to NO_CHANGE so it is not exported again. On the Update database it takes 15 minutes.

Why is it so slow?????

Cheers for any help,

Jiggy!

[gep13]

Hey,

The limiting factor here would be the Mobile Device itself. i.e. it does not have the same processing power as a full blown computer.

Techniques that work well in a full server environment will not work the same on the Mobile Device. You may need to rethink the strategy that you are using to update the tables on the Mobile Device.

Gary

[Jigabyte]

Hi Mate,

I understand that, here is the code if you have any suggestions please:-

                Dim xw As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(xmlOutputPath & sFilename, xs)
                xw.WriteStartElement("NewDataSet")

                For iRows = 0 To sqlDSData.Tables(0).Rows.Count - 1
                    xw.WriteStartElement(sqlDSData.Tables(0).TableName)
                    For i As Integer = 0 To sqlDSData.Tables(0).Columns.Count - 1
                        xw.WriteElementString(sqlDSData.Tables(0).Columns(i).ColumnName, IIf(IsDBNull(sqlDSData.Tables(0).Rows(iRows).Item(i)), String.Empty, sqlDSData.Tables(0).Rows(iRows).Item(i)))
                    Next
                    xw.WriteEndElement()
                    sqlDSData.Tables(0).Rows(iRows).Item(sPrefix & "_RECORD_STATUS") = "NO_CHANGE"
                Next

                sqlCBData = New SqlCeCommandBuilder(sqlDAData)
                sqlDAData.Update(sqlDSData, sTablename)
                sqlCBData.Dispose()

I have stepped through the code and its on the sqlDAData.Update that takes forever and its only on the table with 17000 rows. Surely 17000 is not the reason. I am going to put SP 2 for sqlce 2.0 on and see if it helps. The problem with that is all the PDA's are out on the field. I suppose I will have to create an install. Normally I just get the PDAs in and do the install by running through vs2005.

Thanks for the reply,

Jiggy!

[Jigabyte]

OK I just did a test and on the update it took 9 minutes to complete and I altered the status on 161 rows. There are 17000 rows in the table and I don't have a clue why it should take so long. Maybe a repair and compact is in order?

Cheers,

Jiggy!

[Jigabyte]

Unusual is this, I almost always get an answer on here. Meybe the way I am doing it is the only way.

Nevermind ...

[gep13]

I am not sure of anything to suggest to be honest.

I know there is a functionality to use merge replication between SQL Server Compact and a back end server, but I am not sure that this help you in your case.

Wait and see what PeteVick has to say about it, but I think it might simply come down to the shear number of rows you are processing.

Gary

[Jigabyte]

Hi mate,

The thing is I am only processing around 100 to 200 rows at a time and I am only updating one of the columns. Its on the update back to the live database that is taking around 10 minutes. The table is holding around 17000 rows and I don't think that is that many, maybe wrong.

Thanks for the reply I will see if any other posts come in.

Jiggy!

[petevick]

Hi,
just clarify please - you are getting an XML file from a server, then reading the XML file and updating 17,000 records on a SQL Mobile database?

If so, I would be tempted to look at Replication, or the newer sycnc services - http://msdn.microsoft.com/en-us/sync/bb887608.aspx which doesn't need full blown SqlServer on the back end

Thanks

[Jigabyte]

Hi Mate,

No, I have a sqlce database on the PDA and I have a recovery utility which sets the status of rows in various tables depending on what date is entered. My problem is that one of the tables has 17000 in it and the utility flies through the dataset setting the status of aroun 160 rows but when it does the update to the database using command builder and data adapter its taking around 10 minutes. I don't know why its taking so long.

Cheers,

Jiggy!

[Shaggy Hiker]

I have just been tinkering around with something similar on a desktop running Access. While the number of rows is nowhere near what you have, the update took an inordinately long time for about 100 rows (4-6 s, but on a PDA, that would be FAR longer). I wrote a little profiler class that I can drop into any project I want (not sure if it uses any non-CF items, but it might), and used it to track down where the slowdown was coming from. The code called GetChanges on the dataset prior to the update, and that was 90% of the time cost.

Your post just got me tinkering with my code to time different things. The data I was importing was actually 400 lines, so I was inserting 400 records when I called Update. I also altered the code to remove the call to GetChanges, as I don't see that call in your code. Without the call to GetChanges, the Update took approximately 400ms, which means about 1ms per line on a pretty good desktop. With GetChanges in there, the Update took over 4000ms, or about 13ms per line.

Therefore, if you are using GetChanges in your code, you will see a heavy penalty for that, but you don't appear to be doing that. Update itself is somewhat slow, since it walks through the table calling the appropriate CRUD function for each row.

The only alternative that comes to mind is that you re-design that update to use a command object and ExecuteNonQuery. If you can do a batch update using ExecuteNonQuery, you should see a considerable performance boost over updating via a dataadapter. If you can't do it as a batch process, it may not be any faster, though.

[Jigabyte]

Thanks very much mate for your advice, I am thinging of having a archive database as most of the rows being stored is for view purposes and when they log in give them the option to select the correct database.

Thanks again for all the advice, its good that I know I am not doing something stupid.

Jiggy!