|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Is this performance expected? |
| Author |
Message |
|
|
| Posted : 2007-08-01 04:26:33 |
I am using SQL Server 2005 Express and Quickbooks 2007 Enterprise.
When I run this query:
SELECT * FROM OPENQUERY(QBLS,'SELECT RefNumber from Invoice NOSYNC')
I get 21087 rows returned in 27.7 minutes
Is this performance expected?
When I run this query:
SELECT * FROM OPENQUERY(QBLS,'SELECT RefNumber FROM Invoice NOSYNC WHERE TimeModified >= {ts''2005-09-25 00:00:00.000''} AND TimeModified <= {ts''2005-10-26 00:00:00.000''}')
I get 481 rows in 31 seconds.
Is this performance expected?
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-08-01 08:41:35 |
Yes, you need to change the Iterator or Chunk Size to 20,000 or higher. The maximum Iterator or Chunk size that can be set is 100,000 (higher than that and it resets itself back to the default of 500). The lengthly time indicates a new tread is being setup every 500 records.
|
|
|
|
|
|
| Posted : 2007-08-01 11:23:42 |
| I have it set to 100,000 still the same amount of time. I also tried 20,000, and 10,000. |
|
|
|
|
|
| Posted : 2007-08-03 00:25:45 |
I rebuilt the .OPT file because I thought that it might be corrupt. (Is there some way to tell if it is corrupt?) I still get the same result as in the first query above (about 30 minutes for 21,000 rows). How long do you estimate that this query should take to run? I want to know what I am shooting for.
Also my QB company file is 400 MB and my .OPT file is 1200 MB. It takes about 12 hours to rebuild the .OPT file. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-08-03 11:25:14 |
Look we really nearly need to get this into the proper context, the query:
SELECT RefNumber from Invoice NOSYNC
is pointless as it returns over 21,000 rows, while a query for invoices for specific time period:
SELECT RefNumber FROM Invoice NOSYNC WHERE TimeModified >= {ts''2005-09-25 00:00:00.000''} AND TimeModified <= {ts''2005-10-26 00:00:00.000''}
returned 481 rows in 31 seconds. This is what we work on, to get QODBC to return useful data as quickly as possible.
Try using VB Demo to run your queries again and let me know the real time it took using QODBC. SQL Server attempts to use multiple thread IDs, which actually slows QODBC down as we have to reconnect with QuickBooks, tell QuickBooks the new thread id, recall or rerun to we were up to, and continue retreiving the XML data.
|
|
|
|
|