| Very Slow data retrieval using RefNumber on Invoice table |
| Author |
Message |
|
|
| Posted : 2007-02-06 07:35:51 |
Hi ,
SELECT TxnId, Refnumber FROM Invoice unoptimized where RefNumber = 'T000131418' and
TxnDate > {d'2007-01-01'} and TxnDate < {d'2007-07-01'}
Why is this extremely slow?
I have tried this without the date , the same results.
My data file is 480megs so thought this would run quickly as RefNumber is tagged as a jumpin field.
If I do a search in Quicbooks using the find fucntion it is almost instant.
Main reason I am using this is to confirm correct updates and to retrieve the TxnId for inserted transactions.
sp_lastinsertid is not returning anything, I think I might be losing my connection, and my thinking is that this method will be useful for other functions too.
thanks for any advice
Johan
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-02-06 09:31:46 |
Firstly, your query by-passes the QODBC optimizer by using the UNOPTIMIZED tag after the table name. Your query will be executed by QuickBooks, so the larger the company file the longer time until the result is returned.
The fastest method is to do:
sp_optimizeupdatesync Invoice
to force the optimized Invoice table to be updated with any new transactions and then run:
SELECT TxnId, Refnumber FROM Invoice NOSYNC where RefNumber = 'T000131418'
for an instant answer.
BTW: The optimized jump-ins for the Invoice table are:
|
|
|
|
|
|
| Posted : 2007-02-06 10:15:41 |
If I can follow up with two questions
1) Is the way I am using the RefNumber not using the jump in? Not quite sure why it's slow.
2)What are the best Optimiser settings for my size file (480megs)
thanks
Johan |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-02-06 10:38:21 |
|
|
|
|
|
| Posted : 2007-02-06 10:44:19 |
Hi Tom,
Thanks, will try it.
What about the jump in using the refnumber ? Any ideas on why that would be slow?
thanks
Johan |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-02-06 11:20:01 |
It isn't, this is instantaneous:
SELECT TxnId, Refnumber FROM Invoice NOSYNC where RefNumber = 'T000131418' |
|
|
|
|
|
| Posted : 2007-02-06 11:48:05 |
Hi Tom,
If I understand 'NOSYNC' will bypass optimizer and go right to QB data, use the jump in and then return the result.
There is still something wrong, in QBooks the Find screen finds it instantly, in VB demo takes at least 10 mins or more.
If I do the same but use the TxnId then it takes about 10-12 secs
Am sure I am missing something somewhere.
thanks
Johan |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-02-06 13:03:11 |
| No, NOSYNC is go directly to the optimized table, don't call QuickBooks. UNOPTIMIZED means don't use the QODBC optimizer and call QuickBooks directly. |
|
|
|
|
|
| Posted : 2007-02-07 01:05:56 |
Hi Tom
If I execute that command , the driver status window pops up (it is switched on) with a
'waiting for quickbooks message' in the operation info.
Should it do this if the NOSYNC was specified?
thanks
Johan |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-02-07 08:38:21 |
| Yes, even with the NOSYNC parameter, QODBC will read the company file and check with QuickBooks security that you have permission to access the company file and table before reading the optimized table. |
|
|
|
|
|
| Posted : 2008-04-02 08:53:35 |
Hello
Sorry, I know this is an older post, but the issue is most similar to a problem I'm currently trying to solve. My main problem is that a query for invoices like:
SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo FROM Invoice WHERE TxnDate>{d'2006-04-02'}
takes close to an hour to execute if done from my C# .NET console application (returns roughly 7000 invoices). Issuing this same query from the VB demo only takes about 10 minutes. I've tried using the sp_optimizeupdatesync and NOSYNC strategy (both in my C# app and the VB demo) outlined above and it performs great (about 30s to get all of the records), but the fields BalanceRemaining and AppliedAmount are invalid for all resultant invoices. I'm guessing the sp_optimizefullsync will take care of the issue, but it also takes about an hour to execute.
I've gone through the optimizer tutorial in FAQ and have left the default settings. It mentions that the BalanceRemaining and AppliedAmount fields are affected by the "null / dirty reads / real time" setting, but changing this didn't help get the fields for the sp_optimizeupdatesync / NOSYNC technique.
I've ensured that the "Iterator Chunk Size" is set to 20,000 and am specifying the STAThread property for my functions as suggested in other threads.
Any additional tips would be greatly appreciated. Really I'm just looking for the fastest way to pull all invoice records into my C# app.
Thanks,
Mark |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-04-02 09:41:55 |
Actually this issue isn't a QODBC issue at all. You say that running:
SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo FROM Invoice WHERE TxnDate>{d'2006-04-02'}
only takes about 10 minutes in VB Demo for 7000 invoices. However, your C# .NET console application takes close to an hour to execute it. Why?, well that's because Microsoft only thinks you're using MS SQL Server where operatations are multi-threaded. Your C# app needs to use a STA (single-threaded-apartment) threading model. Multi-threading actually slows QODBC down as we need to re-establish a connection to QuickBooks every time the Thread ID changes.
For further information see: ASP.NET vs Classic ASP
See also the msdn .NET Framework Developer Center: http://msdn2.microsoft.com/en-us/library/system.stathreadattribute.aspx |
|
|
|
|
|
| Posted : 2008-04-03 08:48:36 |
Hi Tom,
Thanks for the quick reply. As I mentioned, I am specifying the STAThread property for my functions, but you're right it's not a problem with QODBC.
After a bit more trouble-shooting, I've discovered my main problem is the use of ADODB classes instead of ODBC. ADODB worked, but when I looped through the resultant recordset via Recordset.MoveNext(), an additional query was made through QODBC for each record. These additional queries caused an additional 50 minutes of runtime for my 7000 invoices.
The first function below demonstrates the ADODB method I was using (took ~1 hour). The second does the same thing with ODBC (takes ~10min). Hopefully this proves useful for others.
[STAThread]
static void test1() {
ADODB.Connection con = new ADODB.Connection();
con.Open("DSN=SOQB;OLE DB Services=-2", "", "", -1);
string invoiceSQL =
"SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " +
"FROM Invoice " +
"WHERE TxnDate>{d'2006-04-02'}";
ADODB.Recordset invoiceResult = new ADODB.Recordset();
invoiceResult.Open(invoiceSQL, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0);
while(!invoiceResult.EOF) {
Console.WriteLine("Invoice #" + invoiceResult.Fields["RefNumber"].Value.ToString());
invoiceResult.MoveNext();
}
con.Close();
}
[STAThread]
static void test2() {
OdbcConnection con = new OdbcConnection("DSN=SOQB");
con.Open();
OdbcDataAdapter dAdapter = new OdbcDataAdapter(
"SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " +
"FROM Invoice " +
"WHERE TxnDate>{d'2006-04-02'}", con);
DataTable result = new DataTable();
dAdapter.Fill(result);
DataTableReader reader = new DataTableReader(result);
while(reader.Read()){
Console.WriteLine("Invoice #: " + reader.GetString(1));
}
con.Close();
}
I'm still curious whether you have any thoughts on why the BalanceRemaining and AppliedAmount fields fail when using the NOSYNC technique you described previously in this topic. If I can get these two fields to return properly I could drop my 10min query into a 30s query. Would updating another table via sp_optimizeupdatesync help?
Thanks,
Mark |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-04-03 09:11:35 |
Thanks for the coding insight, I will copy your coding example to some of the other posts to make it easier to find. For balances and stock quantities: the default QODBC Optimizer option "Nulls" - will return NULLs for all fields that cannot be kept in sync, "Dirty Reads" - means return the most current value that was loaded into the optimizer cache, and "Real Time" uses the slower method to get the data directly out of QuickBooks making sure you get the most current value for the requested fields. (Real Time is the previous method used used in QODBC v5). I prefer to use "Dirty Reads" and force a calldirect or unoptimized statement for anything really important instead.
|
|
|
|
|
|
| Posted : 2008-04-03 09:20:31 |
|
|
|
