QODBC SELECT取> 15分钟返回结果 [英] QODBC SELECT taking > 15 minutes to return results
问题描述
我对使用QODBC进行QuickBooks报表非常陌生.我准备的第一个SELECT查询中的一个实质上是在ReceivePayment表中标记有现有未使用付款的客户，以查询该客户的剩余余额的发票.输出是(或应该是)按CustomerRefFullName分组的客户列表，第2列中有未使用的付款总额，以及未结发票中的余额合计.
I'm pretty new to using QODBC for QuickBooks reporting. One of the first SELECT queries I prepared essentially flags customers with existing unused payments in the ReceivePayment table, querying against invoices with remaining balance for that same customer. The output is (or should be) a list of customers grouped by CustomerRefFullName, with a sum of unused payments in column #2, and sum of total balance remaining from open invoices.
这是一个相当简单的查询.当我将表作为静态表导入到MS Access中时，查询速度很快.但是，当我对链接表运行查询时，最多需要15分钟才能产生结果.
It's a fairly simple query. When I import the table to MS Access as a static table, the query is fast as would be expected. However, when I run the query against a linked table, it takes upwards of 15 minutes to produce the results.
这不应该发生.这是MS Access配置错误的问题吗?我缺少或不知道的与QODBC配置有关的其他内容吗?任何帮助将不胜感激.
This should not be happening. Is this a MS Access misconfiguration issue? Something else relating to QODBC configuration that I am missing or unaware of? Any help would be appreciated.
推荐答案
我想通知您，当您在Access&中导入QuickBooks数据时，运行查询，那么显然它将执行得更快，因为本地MS Access表(离线)中的所有可用数据.但是，当您从链接表中执行查询时，QODBC将从QuickBooks中获取数据. QODBC是使用QuickBooks SDK的ODBC驱动程序.
I would like to inform you that when you Import QuickBooks data in the Access & run query, then obviously it will execute faster because all data available in a local MS Access table (offline). But when you execute query from linked table, QODBC is fetching data from QuickBooks. QODBC is an ODBC driver which uses QuickBooks SDK.
QODBC的工作原理是通过ODBC接口从应用程序接受SQL命令，然后将这些调用转换为导航XML命令到QuickBooks Accounting DBMS，并返回符合查询结果的记录集.该驱动程序不是客户端/服务器产品，而是在运行时直接与QuickBooks应用程序通信. QuickBooks是一个平面文件数据库，该驱动程序不会将其更改为关系数据库，因此在使用此驱动程序进行开发时请记住这一点.
QODBC works by accepting SQL commands from applications through the ODBC interface, then converting those calls to navigational XML commands to the QuickBooks Accounting DBMS and returning record sets that qualify for the query results. This driver is not a Client/Server product, but rather communicates directly with the QuickBooks application as it runs. QuickBooks is a flat file database, and this driver will not change it into a relational database, so keep that in mind when developing with this driver.
QODBC充当Intuit SDK的包装器"，因此客户最终可以使用标准数据库工具获取其QuickBooks数据.加快开发时间.
QODBC acts as a 'wrapper' around the Intuit SDK so customers can finally get at their QuickBooks data using standard database tools; speeding development time.
请记住，QODBC不是数据库工具，而是翻译工具.您要求的每笔交易都必须通过大型复杂的XML交易进行翻译并传达给QuickBooks.
Please keep in mind that QODBC is not a database tool, but rather a translation tool. Every transaction you request must be translated and communicated to QuickBooks via large complicated XML transactions.
QuickBooks应用程序层需要处理这些XML请求.记录的数量越大，XML请求的时间就越长，因此QuickBooks可能会花费更多的时间和内存来处理更长的请求.
The QuickBooks application layer needs to process those XML requests. The larger the number of Records, the lengthier the XML request, therefore QuickBooks may take more time and memory to process longer requests.
我建议您通过QODBC设置屏幕->消息窗口->选择显示驱动程序状态"和显示优化器状态"选项来启用QODBC状态面板.
I would suggest you to enable QODBC status panel via QODBC Setup Screen->Message Window->Select "Display Driver Status" and "Display optimizer Status" options.
然后，下次您运行查询时，如果看到"Waiting for QuickBooks"，则表示QuickBooks需要时间来处理请求.屏幕的右下角将有一个状态面板，将显示一个窗口，其中包含有关QODBC正在运行的信息.请注意QODBC花费最多时间或陷入困境并与我们分享的步骤.
Then the next time you run a query, if you see "Waiting for QuickBooks", it means QuickBooks is taking time to process the request. There will be a status panel at the lower right corner of your screen, will be shown a window with information on what QODBC is working at. Please note the step on which QODBC spends the most time or get stuck and share it with us.
我还建议您尝试在VB演示上执行以下命令，看看它可以解决此问题:
I also suggest you to try executing below command on VB Demo and see it can resolve the issue:
SP_OPTIMIZEFULLSYNC全部
SP_OPTIMIZEFULLSYNC ALL
此命令会将一些数据带到本地缓存，以提高查询的检索性能.我建议您请完全运行上面的命令.上面的命令可能需要一些时间，具体取决于您在QuickBooks中的记录.可能需要2-5个小时或更长时间，具体取决于QuickBooks公司文件中的记录数量.请让此命令完全运行，不要杀死该命令.建议不要在构建优化程序文件时终止/关闭该应用程序.
This command will bring some data to a local cache to increase retrieval performance for queries. I would like to suggest you to please let run above command completely. Above command may take sometime depends on your records in QuickBooks. It may take 2-5 hours or more depends on the number of records in QuickBooks company file. Please let this command run completely do not kill this command. It is not advisable to kill/close the application while its building the Optimizer file.
参考: 如何对选定的表执行SP_OPTIMIZEUPDATESYNC或SP_OPTIMIZEFULLSYNC
如果您仍然遇到问题，请您从下面提到的链接&提供要求的信息:
If you are still facing issues, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
还分享有关您面临的问题的更多信息，以便我们快速找到问题.
Also share more information about the issue you’re facing, so that we can locate the problem quickly.
我们可能需要以下信息，请在答复票证时附上以下列出的文件.
We may need following information, Please attach below listed files when replying to the ticket.
1)QODBC设置屏幕的屏幕快照->关于(开始>>所有程序>>与QuickBooks一起使用的QODBC驱动程序>> QODBC设置屏幕>>关于选项卡) 2)您面临的问题的屏幕截图. 3)共享QuickBooks版本详细信息:在QuickBooks UI&上按f2.分享截图. 4)分享您正在使用的SQL语句. 将整个日志文件作为附件共享为以下格式的文本格式: 5)QODBC设置屏幕->消息->查看QODBC消息 6)QODBC设置屏幕->消息->查看SDK消息
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab ) 2) Screenshot of the issue you’re facing. 3) Share QuickBooks Version details: Press f2 on QuickBooks UI & share screenshot. 4) Share the SQL statement you’re using. Share Entire Log Files as an attachment in text format from 5) QODBC Setup Screen -- > Messages -- > Review QODBC Messages 6) QODBC Setup Screen -- > Messages -- > Review SDK Messages
这篇关于QODBC SELECT取> 15分钟返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
