QODBC问题

Question

好的。过去几天我一直在努力做世界上最简单的报告.

因此，在计算出表的关联并提取样本数据之后，我注意到我需要改变我提取数据的方式。这其中的一部分是从发票表中提取年度数据。然而，qodbc是愚蠢的(可能是我，但它使我感觉更好地指责司机)

SELECT * FROM Invoice WHERE TimeCreated > '2014-01-01 00:00:00.000'一直给我Invalid operand for operator: >的错误

搜索谷歌没有为我提供任何帮助。

哇哦..。我需要帮助搜索一个日期字段。有人有什么想法或建议吗？

还有，加分但是相关的..。还有人对qodbc司机的速度有问题吗？有些表的搜索速度和mysql一样快，但是有些表.我的天啊。一个简单的查询只需10分钟。提高这些速度的想法？

Answer 1

日期格式

SELECT *
from InvoiceLine
WHERE Txndate >= {d '2005-09-23'}

时间邮票格式

SELECT *
FROM Customer
WHERE TimeCreated = {ts '1999-07-29 14:24:18.000'}

SELECT *
from InvoiceLine
WHERE TimeModified >= {ts '2005-09-23 00:00:00.000'}

参考：http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2203/50/how-are-dates-formatted-in-sql-queries-when-using-the-quickbooks-generated-time-stamps

如何使QODBC运行得更快

  Keep in mind that QODBC is not a database tool, but rather a translation tool. QuickBooks is a non-normalized flat file system which has no indexes available and will not perform like SQL Server or dBase files. Every transaction you request must be translated and communicated to QuickBooks via large complicated XML transactions.

  Try and keep your result set as small as possible to get a feel for the system, carefully design and test any multi-file joins, and keep the number of returned fields to a minimum for maximum performance.

  Our main goal is to make it easier to access QuickBooks data in a standardised database-like fashion, but queries must be optimized to perform as fast as possible.

  Also, try and use ranges of dates on TxnDate, TxnDateMacro and TimeModified as much as possible to narrow down the data to the smallest possible segment. For example, make something similar to this in the first part of your WHERE clause:

  Invoice.TimeModified >= {ts'2003-09-01 17:01:09'} AND
  Invoice.TimeModified <= {ts'2003-09-02 17:01:09'}

我建议使用Optimizer. sp_optimizefullsync All

有关优化器的所有细节，请参见:如何设置QODBC优化器，以及优化器选项在哪里。(http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2358/48/how-to-setup-qodbc-optimizer-and-where-are-the-optimizer-options)

  By default the optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the local optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have. 

Answer 2

从未使用过此驱动程序，但请尝试如下：

从发票中选择* TimeCreated > {d'2014-01-01 00:00:00.000'}

可能需要处理一下日期字符串的格式，只是猜测一下而已。

就您选择的速度而言，如果查询中有WHERE子句，这可能会因为表上没有索引而受到影响。有索引的表返回结果的速度比没有索引的表要快。