We are using QODBC (6.155) as a linked server in SQL Server 2005. Having severe performance issues inserting into the TimeTracking table. Inserts of ~10 rows takes 9-12 MINUTES. We were expecting milliseconds or at worst, seconds. Optimization is turned off. Any ideas or suggestions for improving this performance?

Any help greatly appreciated...

You have not detailed how you are doing the inserts, but most developers make the mistake of opening and closing the connection with every insert, which can open and close QuickBooks for every insert. See: VB Script Performance importing from SQL Server to QuickBooks for an example of this.

You should also check that the inserts work with acceptable performance using VB Demo.

Here's a code snippet for the inserts. As you see, I'm wrapping an openquery.  The insert is performing a delta operation, which explains the usage of the outer join against customer. We tested the outer join against a SQL port of the QB customer file and the job finished in < 300 msec. If you see anything suspect in the code, please be frank.

BTW -- the server is a fairly recent Zeon Dual Processor box with 2 GB main memory and a 4 disk SATA RAID array. Since everything else we do on that machine is pretty fast, we are not suspecting hardware at this point.

Thanks

Jeff Ishee

Software Development Manager

As far as our bit in:

insert jobs_ref (CustomerRefListID, CustomerRefFullName, isactive, row_created, row_createdby ) select qbd.ListID, qbd.FullName, qbd.isactive, getdate(), system_user from openquery (qb, ' select listID, Fullname, isactive from customer where isactive = 1 and JobStatus = ''InProgress''and (name like ''250%'' or name like ''%FS%'')') qbd left outer join jobs_ref jr on qbd.ListId = jr.CustomerRefListID where qbd.isactive = 1 and isnull(jr.isactive, 0) <> 1

the QODBC select statement:

select listID, Fullname, isactive from customer where isactive = 1 and JobStatus = 'InProgress' and (name like '250%' or name like '%FS%')

runs fine in VB Demo but the where clause uses a evaluation that isn't a QODBC jump-in, so it will have to do a full table scan. I suggest you turn on trace and see what your TimeTracking insert is really doing? See: How do I turn on the trace log?  for more.

Thanks Tom. I'll try that.

Pls help me understand "jump-ins". I'm not familiar with them, though quite comfortable with B-Tree index structures used by most RDBMS products. Reading between the lines of your reply, looks like there is no concept of partial optimization within QuickBooks. Is this right? That would imply that ANY column in a where clause which is not a jump-in would revert to a full scan. If that's the deal, a simple workaround would be to create a temporary table in SQL, grab a superset of the QB subset using just jump-in columns, then grab the desired results from the temp table. Although that's technically more overhead, for these small result sets, I would expect millisecond performance.

Regards

Jeff Ishee

QODBC uses XML statements to communicate with Intuit's QuickBooks qbXML SDK, so there's no underlying database or B-Tree index stuctures. For that reason we have added the QODBC Optimizer to Version 6. It places a optimized copy of the data on the desktop that can be queried extremely fast.

What we are calling 'jump-ins" are methods in the qbXML SDK where can retrieve the data without a full table scan.