SQL Linked Server Query Performance Issue

We’re using SuiteAnalytics Connect to create a linked SQL Server to perform queries for our custom code faster than SuiteTalk can, and it’s been working great for years, for the most part (except that we can’t use any code more complex than SQL ’92 code).

Anyways, I perform the following query, and the 8,251 results return in 5 seconds, which is fine and appropriate for such a query:

—

SELECT * FROM OpenQuery(NetSuite_Sandbox2, ‘
SELECT * FROM (
SELECT t.transaction_extid, t.billaddress,
tl.transaction_line_id,
–ii.item_extid, ii.displayname,
ABS(NVL(tl.item_count, 0)) AS Qty,
ab.ADDRESS_BOOK_ID
FROM transactions t
INNER JOIN transaction_lines tl ON tl.transaction_ID = t.transaction_id
–INNER JOIN inventory_items ii ON ii.item_id = tl.item_id
INNER JOIN ADDRESS_BOOK ab ON ab.company = t.billaddress
WHERE t.entity_id = ”46351”) x
‘);

—

But then I just add the following line to the bottom:

—

WHERE x.Qty > 0

—

and all of a sudden, virtually the same query takes almost FOUR MINUTES to return a result set of 6,341 records. This is unacceptable. So I’ll probably have to deal with it in my code on the C# side.

But my question to you all is…. WHY??? Why should the SuiteAnalytics Connect linked server take four minutes to go through 8,251 results and check if their “Qty” value is greater than zero to filter the list down to the 6,341 values that are the correct result set???