Hello, I have a Delphi application which acquires some of its data from a corporate Cache database via an ODBC SQL interface.
Most queries work relatively well however the following call initially times-out with a 450 or 452 error requiring a re-connection. When reconnected however, the same SQL works every time without fault. If there is a pause (approx 30 minutes or so) without calling this query and it is attempted, the time-out and error occurs again. Here is the SQL: SELECT (ISNULL(orders_line_items.required_by,0)) As RequiredBy, orders_line_items.date_required As DateRequiredBy, (ISNULL(orders_line_items.allocation_code,0)) As CostedTo, (ISNULL(orders_line_items.order_number,0)) As OrderNum, (ISNULL(orders_line_items.order_line,0)) As OrderLineNum, (ISNULL(orders_line_items.line_item_description,0)) As Description, (ISNULL(orders_line_items.quantity_ordered,0)) As QTYOrded, (ISNULL(orders_line_items.quantity_received,0)) As QTYReceived, (TRIM(TRAILING 'Q' FROM orders_line_items.committed_cost)) AS AmountCommitted, orders_line_items.amount_invoiced As AmountInvoiced, (TO_NUMBER(TRIM(TRAILING 'Q' FROM orders_line_items.committed_cost)) - ISNULL(orders_line_items.amount_invoiced,0)) AS RemCommitment FROM orders_line_items INNER JOIN orders_user_profile ON ((orders_line_items.order_number = orders_user_profile.order_no) AND (orders_line_items.order_line = orders_user_profile.line_no)) WHERE orders_user_profile.profile_ID = '2501' AND orders_line_items.line_status <> 'B' AND orders_line_items.committed_cost > 0 AND ((orders_line_items.quantity_received IS NULL) OR ((orders_line_items.quantity_invoiced IS NULL) AND (orders_line_items.quantity_received <> 0)) OR (orders_line_items.quantity_received < orders_line_items.quantity_ordered)) I have experimented unsuccessfully with variations to find a possible offending field or function. It almost appears as if the server is somehow creating an optimised compilation of the query which it uses after the re-connection but discards after a period of no use. If there is an optimisation process (I would imagine there is), is there a command that would force the server to carry this out and return the requested data on the first call? I don't have access to, or control over the data structure of this database so am flying completely blind. Any help much appreciated. Roland
