| How to speed up Insert into PurchaseOrderLine Table |
| Author |
Message |
|
|
| Posted : 2007-06-21 07:36:43 |
Hi ,
I've started to have some success using qodbc - thank you for developing this product.
On an insert in to PurchaseOrderLine, the following command:
INSERT INTO PurchaseOrderLine (TxnID, PurchaseOrderLineDesc, FQSaveToCache) VALUES ('1FF8D-1181774340', '157 x 607', 0)
takes about 4 or 5 seconds to execute, spending most of it's time on "Find First Record"
Whereas;
INSERT INTO PurchaseOrderLine (TxnID, PurchaseOrderLineDesc, PurchaseOrderLineQuantity, PurchaseOrderLineItemRefListID, PurchaseOrderLineItemRefFullName, PurchaseOrderLineRate, FQSaveToCache) VALUES ('1FF8D-1181774340', '253 x 607', 4, '3E60000-1016478115', '.', 1, 0)
takes 20-25 seconds, again mostly doing a "Find First Record"
4 or 5 seconds is unpleasantly long, 20 seconds is getting unusable.
Any ideas a) why the second insert is so much slower, and b) what i can do to speed these up?
The PurchaseOrderLine table has 80 000 some odd records.
Thanks for any help,
Duncan. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-06-21 08:12:45 |
| Both of the above statements add lines to an existing Purchase Order, normally the INSERT process would be to create a new Purchase Order which is faster as QODBC doesn't have to load the purchase order first. The more lines there are in an existing Purchase Order, the slower it is to add another line. |
|
|
|
|
|
| Posted : 2007-06-21 08:39:54 |
The Tip on "the more lines in an existing purchase order, the longer it takes to add one" was a good one. Starting with a fresh PO sped things up considerably. I was working with rather bloated test POs, some of which i had deleted lines from, to make them more manageable, but QB those deleted lines are still included in the search time i think.
I'm still wondering why the difference in speed between the two lines i quoted - one quite short, the other including PurchaseOrderLineItemRefListID Any Thoughts There?
Thanks again for the help
Duncan. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-06-21 09:01:16 |
| The first statement just created an empty line with a comment, while the second statement involved the look up of an item. |
|
|
|
|
|
| Posted : 2008-09-10 19:09:08 |
| We have had some issues related to speed issues when inserting new order line items
We have tried the approach with FQSaveToCache=1, then FQSaveToCache=0 in the last insert and we have also tried to use the stored procedure SP_BATCHSTART and SP_BATCHUPDATE to do a batch insert in cache then in Quickbooks but none of them seem very effective when creating an order with 54 lines.
do you have any other suggestions we could try? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-10 22:12:34 |
| Yes, but you're are not going to like it. You need to split the lines over muliple POs. Creating 5 or 6 POs with 10 lines is much faster than creating one PO with 54 lines. |
|
|
|
|
|
| Posted : 2008-09-10 22:31:56 |
Hello
thanks for your reply. If we decide to do that, is there anyway to tell Quickbooks that all those PO are related?
Many thanks |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-10 22:34:51 |
| Yes you can set the Reference Number (RefNumber) to show there is a set ie: A1222-1, A1222-2, A1222-3 etc. |
|
|
|
|
|
| Posted : 2008-09-11 18:53:05 |
We have done some tests splitting the original file into 5 files for processing and the response time is very similar.
From 30 minutes that took processing one order with 3 recipients (18 items each recipient) we have passed to about 25 minutes to process 5 orders with one recipient (and 18 items). This has happened having the Optimizer OFF
When we turn the Optimizer ON the system just hangs on queries than before were executed in about a minute. You can see the SELECT below took about 40 minutes with the VERIFY parameter and similar thing happens when we use NOSYNC (SELECT ListID FROM Item NOSYNC WHERE FullName = 'FMA0542')
DEBUG2008-09-10 02:35:44 - Getting reader for query 'SELECT ListID FROM Item VERIFY WHERE FullName = 'FMA0542''
DEBUG2008-09-10 03:13:30 - Running query 'INSERT INTO SalesOrderLine (
It doesn't matter what we try, it seems that without the optimizer we get much better results than activating it.
Can you see anything that maybe we are not doing correctly?
Many thanks |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-11 22:25:00 |
| Sorry, but you shouldn't be using VERIFY. VERIFY rebuilds the whole optimizer table from scratch every time it's executed then runs the SELECT query. If you look at the start of this tread, it's not me but another user that confirms more PO's - less lines, is faster. QODBC by default works faster if you let it work with the optimizer on. |
|
|
|
|
|
| Posted : 2008-09-11 22:35:12 |
Hello,
thanks for the quick reply.
is there any reason why the processing of my 5 orders take the same time with and without optimizer?
As I said I have processed 5 orders with 18 items each and that took 23 minutes. If I delete my purchase orders in Quickbooks and re-run my application with the optimizer ON I'm getting the same results: 23 minutes.
It doesn't really matter if I use the Optimizer or not, I cannot find a way to speed things up:
my code using sp_batchstart and sp_batchstart and sp_batchupdate for multiple inserts with STATThread in the DAL class and the use of FQSaveToCache doesn't have any effect on having the optimizer ON or OFF
Am I doing anything wrong?
thanks |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-11 23:41:16 |
| Yes, INSERTs have nothing to do with the QODBC optimizer. The optimizer is used in SELECTs and UPDATEs. |
|
|
|
