April 24, 2009 at 9:01 am
The following query takes about 2 minutes to run:
SELECT * FROM OPenQuery(BI_RPT1,'SELECT DMAH.PERIOD_START_DATE,DMAH.INVENTORY_ITEM_ID,
DMAH.ORGANIZATION_ID,
sum(round(DMAH.INTERORG_ISSUE + DMAH.STD_WIP_USAGE + DMAH.MISCELLANEOUS_ISSUE + DMAH.SALES_ORDER_DEMAND)) TOTAL_USAGE,
TO_CHAR(TO_DATE(apps.FLKBI_GET_GL_PERIOD(DMAH.PERIOD_START_DATE,1),''MON-RR''), ''YYYYMM'') HISTORY_PERIOD
FROM INV.MTL_DEMAND_HISTORIES DMAH
WHERE DMAH.PERIOD_START_DATE > (sysdate-20) and DMAH.ORGANIZATION_ID=164
Group by DMAH.PERIOD_START_DATE,DMAH.INVENTORY_ITEM_ID,
DMAH.ORGANIZATION_ID,
TO_CHAR(TO_DATE(apps.FLKBI_GET_GL_PERIOD(DMAH.PERIOD_START_DATE,1),''MON-RR''), ''YYYYMM'')')
WHen I copy the same SQL into a view, it times out. How do I overccome this?
BI_RPT1 is an Oracle server. I'm using SQLServer2005.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
April 28, 2009 at 10:51 am
How are you running the view? Just via the management studio? I don't have a solid answer, but since there was no responses yet I figured I would give you a potential solution. I was thinking you might look at the server wide setting of "remote query timeout" as a possible cause. Not sure if OPENQUERY() calls are impacted by this, but might explain the issue. I could see how they may be viewed as a remote query. By default it will timeout remote queries based on however many seconds are defined in that option (but I think its like 10 minutes or something pretty long).
May 4, 2009 at 10:03 am
Yes, I'm just running the view from Management Studio.
Where do I look to see the remote query timeout setting?
I don't understand why the remote query timeout would affect the View, but not the straight OpenQuery statement...?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
May 4, 2009 at 10:56 am
I misread the explanation. I was thinking that when you run the SQL directly on the source it does not time out, but when you run it as the OPENQUERY() in a view on SQL Server it times out. In that case I was assuming that it was initiating a remote conneciton to another server and therefore timing out.
However, after reading it again it sounds like if you run it as a view it fails. But if you run it as just the OPENQUERY() it successeds. And that both are run from Management Studio. With that being the case I would think remote connection timeout, or the BI_Rpt1 datasource would be consistant accross both executions and should not be a factor. That leaves me puzzled as to the possible cause.
Can you post the view code? Just to see the difference between the two statements you execute to see if anything strikes a thought as to a solution?
May 4, 2009 at 11:26 am
The code for the view is exactly as posted above. The view won't run (times out) but if I just copy the same code to a new query window and run it, it runs OK, right now in under a minute.
Is there a time-out period that can be set for a specific view? If so, how do I find it?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
May 4, 2009 at 11:28 am
Is there a time-out period that can be set for a specific view? If so, how do I find it?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
May 5, 2009 at 8:05 am
I am not aware of any setting that would affect only views.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply