|
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
|
|
| Extremely slow Linked Server Queries |
| Author |
Message |
|
|
| Posted : 2008-06-06 07:57:36 |
Do you have any clues as to why a Linked Server Query would take up to 20 minutes to run? Here is an example of some queries I'm trying to run:
SELECT * FROM QBBASH...INVOICE WHERE CUSTOMERREFFULLNAME = 'BRIGHT FUTURES'
update QBDATA...account set banknumber = '6255448' where ListID = '80000004-1205259640'
The query below took about 30 minutes to run and then timed out with no error message:
update openquery(QBOPT, 'select "Contact" from Customer where "ListId" = ''8000028D-1457879688''') set "Contact" = 'Bart Simpson'
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-06-06 08:10:30 |
Well for the last statement, this would be faster:
update openquery(QBOPT, 'select "Contact" from Customer calldirect where "ListId" = ''8000028D-1457879688''') set "Contact" = 'Bart Simpson'
Try changing your optimization settings to use the The start of every new connection (with "Load Updated Data" first) option and confirm that the Multi-Table Sync option has been disabled. If not, uncheck the Multi-Table Sync option as this is often defaulted on (checked) with QuickBooks Enterprise and the QODBC Enterprise Desktop Edition.
See also: MS SQL Server 2000 Linked Server issue using the QODBC optimizer
|
|
|
|
|
|
| Posted : 2008-06-09 23:14:41 |
No, that did'nt work at all. I'm currently running the "corrected" query you sent and so far, it has taken 29 minutes to run and is still running....! Is that how your program is supposed to operate?? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-06-10 11:41:48 |
|
|
|
|
|
| Posted : 2008-06-11 03:37:16 |
This linked server insert query appears to work fine:
insert openquery(QBOPT, 'select "Name","LastName","FirstName","BillAddressAddr1",
"BillAddressAddr2","BillAddressCity","BillAddressState","BillAddressPostalcode" from customer where 1=0')
values ('Zuniga, Daphne','Zuniga','Daphne','Daphne Zuniga','561 W 4th
St.','Carlsbad','CA','92009')
This simple query in VBDemo: SELECT * FROM Customer where LastName = 'Zuniga', takes 15 minutes to run. The following query in a linked server is still running as of 25 minutes:
select * from openquery(QBOPT, 'select * from Customer calldirect where LastName =''Zuniga''')
The ODBC Driver status window opens and is cycling through ALL the customer records just to find this one customer. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-06-11 10:12:29 |
| There's no index on LastName, so the query will do a table scan, try using ListID. |
|
|
|
|
|
| Posted : 2008-06-13 13:26:26 |
what other columns have index, i need to search the listID of the customer and it takes ages to run the query:
SELECT listID FROM customer where firstname='first' AND lastname='last';
i also tried:
SELECT listID FROM customer where fullname='first last'
and it was the same.
Thanks!! |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-06-13 14:27:49 |
The native indexes or QuickBooks jump-ins on the Customer table are: ListID, TimeModified, FullName and ActiveStatus. In your case you will need to use the FullName which can be called directly to QuickBooks using:-
SELECT listID FROM customer calldirect where fullname='first last'
or you can go directly to the SQLite QODBC Optimizer Engine instead using:
SELECT listID FROM customer nosync where fullname='first last' |
|
|
|
|