| Single or Bulk pricing update too slow |
| Author |
Message |
| Mark |
| |
| Group | : Members |
| Posts | : 27 |
| Joined | : 2007-01-20 |
|
| Profile |
|
| Posted : 2008-05-08 04:43:01 |
I could use some suggestions on how to speed up price level updates.
I tried Access and it seems to do extra queries and slows way down. So I switched to vb.net here is my code modified from the QODBCDemo code. It takes 20 minutes to update one item. I have thousands of items to update.
Dim sql As String = "SELECT ListID, SalesPrice FROM ItemInventory NOSYNC WHERE ParentRefFullName = 'DP-Parts'"
Dim msodbcInventoryDA As New OdbcDataAdapter("SELECT ListID, SalesPrice FROM ItemInventory NOSYNC WHERE ParentRefFullName = 'DP-Parts'", msodbcConnection)
Dim msodbcInventoryDS As New DataSet
Dim dr As DataRow
Dim cmdInventory As OdbcCommand
Dim cmdUpdatePriceLevel As OdbcCommand
Dim dReader As OdbcDataReader
cmdInventory = New OdbcCommand(sql, msodbcConnection)
dReader = cmdInventory.ExecuteReader()
dReader.Read()
lblStatus.Text = dReader.GetValue(0).ToString
sql = "UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = " & Math.Round((dReader.GetValue(1) * 0.9), 2).ToString & " WHERE ListID = '8000000D-1177165668' AND PriceLevelPerItemItemRefListID = '" & dReader.GetValue(0).ToString & "'"
cmdUpdatePriceLevel = New OdbcCommand(sql, msodbcConnection)
dReader.Close()
cmdUpdatePriceLevel.CommandTimeout = 100
cmdUpdatePriceLevel.ExecuteNonQuery()
*******************************************************
I tried a bulk update with access and it does them one item at a time and takes even longer than 20 min for each item to update.
I have used the jump-in points and made sure I am in single thread apartment model. The optimizer works great for the read operation but the update needs something to speed it up.
Suggestions?
QBE 2008 with 8.00.00.242 Server edition of QODBC
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-08 08:15:27 |
The Intuit qbXML SDK requires us to modify the whole PriceLevelPerItem table with every insert or update, so it's going to take even longer the more levels and items you have. The problem here is that Intuit requires the whole list to be updated with every operation, not a single row like the rest of QuickBooks!
See: Speeding up PriceLevelPerItem INSERTS? |
|
|
|
| Mark |
| |
| Group | : Members |
| Posts | : 27 |
| Joined | : 2007-01-20 |
|
| Profile |
|
| Posted : 2008-05-09 02:39:10 |
Thanks for that post I had great hopes that I could use the bulk inserts but it does not seem to work the way I am using it.
I used vbdemo32 and ran the following
SP_BATCHCLEAR PriceLevelPerItem
SP_BATCHSTART PriceLevelPerItem
UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = 2.97 WHERE ListID = '8000000D-1177165668' AND PriceLevelPerItemItemRefListID = '80000C07-1175626250' <This statement took about 18 minutes to execute>
UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = 3.84 WHERE ListID = '8000000D-1177165668' AND PriceLevelPerItemItemRefListID = '80000C08-1175626251' <This statement took about 18 minutes to execute>
SP_BATCHUPDATE PriceLevelPerItem
sp_lastinsertid PriceLevelPerItem <nothing was returned>
If this was the correct way to execute the batch then I think the batch is broken. I kept the same connection with each statement executed.
I got the impression that the batch had been fixed in v7 but it does not seem to be working
Suggestions?
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-09 07:47:37 |
| sp_lastinsertid is only for inserts, not updates. |
|
|
|
| Mark |
| |
| Group | : Members |
| Posts | : 27 |
| Joined | : 2007-01-20 |
|
| Profile |
|
| Posted : 2008-05-09 08:22:01 |
Ah of course - Does the batch only apply to inserts as well because the batch update did not batch it processed each update as it recieved it.
I am running this in vb.net
'Start the batch for the PriceLevelPerItem table
SQLCmd.CommandText = "SP_BATCHCLEAR PriceLevelPerItem"
SQLCmd.ExecuteNonQuery()
SQLCmd.CommandText = "SP_BATCHSTART PriceLevelPerItem"
SQLCmd.ExecuteNonQuery()
While (dReader.Read())
lblStatus.Text = dReader.GetValue(0).ToString
SQLCmd.CommandText = "UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = " & Math.Round((dReader.GetValue(1) * 0.9), 2).ToString & " WHERE ListID = '8000000D-1177165668' AND PriceLevelPerItemItemRefListID = '" & dReader.GetValue(0).ToString & "'"
SQLCmd.ExecuteNonQuery()
End While
'Update the batch
SQLCmd.CommandText = "SP_BATCHUPDATE PriceLevelPerItem"
SQLCmd.ExecuteNonQuery()
SQLCmd.CommandText = "sp_lastinsertid PriceLevelPerItem"
SQLCmd.ExecuteNonQuery()
********************************
It has been running for 5 Hours and 12 minutes and has updated 12 records. Clearly the updates are not being batched. At this rate 2000 items will take about 37 days to complete.
Is this a bug with QODBC Batch or Intuits SDK or just bad code on my part?
Any help would be appreciated |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-09 08:35:08 |
| Yes, SP_BATCH is meant for insert operations. Sorry Intuit have designed the PriceLevel interface as a full export and full import interface only. You can't just change one row (or record) without the Intuit qbXML SDK exporting all the PriceLevels, modify it and then reimporting it. |
|
|
|
| Mark |
| |
| Group | : Members |
| Posts | : 27 |
| Joined | : 2007-01-20 |
|
| Profile |
|
| Posted : 2008-05-09 09:14:15 |
Ok - Could you expound on that a bit.. Can I export the entire table to a dataset and do all my updates and inserts on that and then reimport the entire dataset? 500 records at a time with the batch? Would I need to delete/trucate the table before reinserting?
Help me understand the how to do this - Thanks for all the help! |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-09 09:21:08 |
| That may work better. Run a query to extract all the PriceLevelPeritem items, delete the PriceLevelPeritem table and then reinsert all the values (with all the new changes) again..... but try this with a copy of your company file FIRST! |
|
|
|
| Mark |
| |
| Group | : Members |
| Posts | : 27 |
| Joined | : 2007-01-20 |
|
| Profile |
|
| Posted : 2008-05-09 11:33:56 |
| Very good advise - I'll give it a try |
|
|
|
| Mark |
| |
| Group | : Members |
| Posts | : 27 |
| Joined | : 2007-01-20 |
|
| Profile |
|
| Posted : 2008-05-10 07:46:52 |
Tom -
Extracting and then reinserting does not seem to be viable -
The statement "Delete from PriceLevelPerItem" deletes each item one at a time, It reads the entire table deletes the item and then reinserts. It takes as much time to delete as it would to do updates on each item.
The statement "Truncate table PriceLevel" is not supported.
I tried exporting to an IIF and then reimporting But export of PriceLevelPerItem is not supported only fixed percentages.
Any other suggestions? Do you have an efficient way to delete that table? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-10 08:46:49 |
| Well it was worth a try..... Intuit is going to have fix this in their SDK. |
|
|
|
