Parametrized view response time too long

[Nro]

Ok, I have a very strange problem. English is not my first language, so I’ll try my best to be clear.

I have a local parametrized view with some tables, left and right joins. Rushmore tell me that everything is optimized. The main table has 180 000 records. When I do a requery() on one primary key, the result is instantaneous and return me only one record. It’s what I want. This is my editing record for a form.

Now, I have another query, based on the same tables but retrieve approximatively 70 000 rows. It’s not a parameterized view. It’s a browse table that I show for the user to pick one record. When the user made his choice, I keep the key code, and requery() my parameterized view. The requery() took 10 to 15 minutes to complete.

I recreate the indexes, pack memos, pack the table, remove all joins, verify my network… Nothing. I even try to run it on a local machine, same problem.

I’m using VFP 9 SP2 with free tables.

Thanks in advance.

 

[Nro]

Hello Mike.

The two queries are not liked. I only use FactBrowView to browse (70 000) and it has 30 fields in it (invoice number, customer name addresses etc.) Once the user selects one record, I keep the invoice code in a memory variable (lnIdCode), and I use this variable to requery FactOneView. Here some simplified code for the main query

CREATE SQL VIEW ("FactOneView") AS ; 
SELECT Fac_Head.*, ;
 CUS_MAIN.Mn_No1, CUS_MAIN.Mn_Nom1, ;
 CUS_MAIN.Mn_Nom2, CUS_MAIN.Mn_Strt, ;
 CUS_MAIN.mn_str2, CUS_MAIN.Mn_Country, ;
 CUS_MAIN.Mn_POBox, CUS_MAIN.Mn_ChrNo1, ;
 CUS_MAIN.Mn_CURR,;
 CUS_MAINst.Mn_No1 AS Mn_No1St, ;
 CUS_MAINst.Mn_Nom1 AS Mn_Nom1St, ;
 CUS_MAINst.Mn_Strt AS Mn_StrtSt, ;
 CUS_MAINst.Mn_POBox AS Mn_POBoxSt, ;
 CUS_MAINst.Mn_ChrNo1 AS Mn_ChrNo1St, ;
 CUS_MAINst.Mn_Nom2 AS Mn_Nom2St,;
 CUS_MAINst.Mn_Str2 AS Mn_Str2St,;
 NVL(Tb_Port.Ct_Desc, SPACE(25)) AS Ct_Desc, ;
 NVL(Tb_Port.Ct_Fr_Desc, SPACE(25)) AS Ct_Fr_Desc, ;
 NVL(Tb_Vend.Vd_Label, SPACE(40)) AS Vd_Label, ;
 CAST(NVL(QUO_HEAD.Qd_Number,0) AS N(10,0)) AS Qd_Number ;
 FROM Fac_Head ;
	INNER JOIN CUS_MAIN ;
		ON Fac_Head.Id_MnNo = CUS_MAIN.Mn_No ;
	INNER JOIN CUS_MAINst ;
		ON Fac_Head.Id_St_MnNo = CUS_MAINst.Mn_No ;
	LEFT OUTER JOIN Tb_Vend ;
		ON CUS_MAIN.Mn_VdCode = Tb_Vend.Vd_Code ;
	LEFT OUTER JOIN Tb_Port;
		ON Fac_Head.ID_FREIGHT = Tb_Port.Ct_Ind ;
	LEFT OUTER JOIN QUO_HEAD ;
		ON Fac_Head.ID_QdCode = QUO_HEAD.Qd_Code;
 WHERE Fac_Head.Id_Code = lnIdCode

and the browse query...

CREATE SQL VIEW ("FactBrowView") AS ; 
SELECT Fac_Head.ID_CODE, Fac_Head.ID_NUMBER, Fac_Head.ID_DATE ;
 CUS_MAIN.Mn_No1, CUS_MAIN.Mn_Nom1, ;
 CUS_MAIN.Mn_Nom2, CUS_MAIN.Mn_Strt, ;
 CUS_MAIN.mn_str2, CUS_MAIN.Mn_Country, ;
 CUS_MAIN.Mn_POBox, CUS_MAIN.Mn_ChrNo1, ;
 CUS_MAIN.Mn_CURR,;
 CUS_MAINst.Mn_No1 AS Mn_No1St, ;
 CUS_MAINst.Mn_Nom1 AS Mn_Nom1St, ;
 CUS_MAINst.Mn_Strt AS Mn_StrtSt, ;
 CUS_MAINst.Mn_POBox AS Mn_POBoxSt, ;
 CUS_MAINst.Mn_ChrNo1 AS Mn_ChrNo1St, ;
 CUS_MAINst.Mn_Nom2 AS Mn_Nom2St,;
 CUS_MAINst.Mn_Str2 AS Mn_Str2St,;
 NVL(Tb_Port.Ct_Desc, SPACE(25)) AS Ct_Desc, ;
 NVL(Tb_Port.Ct_Fr_Desc, SPACE(25)) AS Ct_Fr_Desc, ;
 NVL(Tb_Vend.Vd_Label, SPACE(40)) AS Vd_Label, ;
 CAST(NVL(QUO_HEAD.Qd_Number,0) AS N(10,0)) AS Qd_Number ;
 FROM Fac_Head ;
	INNER JOIN CUS_MAIN ;
		ON Fac_Head.Id_MnNo = CUS_MAIN.Mn_No ;
	INNER JOIN CUS_MAINst ;
		ON Fac_Head.Id_St_MnNo = CUS_MAINst.Mn_No ;
	LEFT OUTER JOIN Tb_Vend ;
		ON CUS_MAIN.Mn_VdCode = Tb_Vend.Vd_Code ;
	LEFT OUTER JOIN Tb_Port;
		ON Fac_Head.ID_FREIGHT = Tb_Port.Ct_Ind ;
	LEFT OUTER JOIN QUO_HEAD ;
		ON Fac_Head.ID_QdCode = QUO_HEAD.Qd_Code;
	 WHERE (Fac_Head.Id_Act = 1) OR (Fac_Head.Id_Act = 2)

Like I said, requery “FactOneView” is fast, in any circumstance, but as soon I use "FactBrowView", the first requery “FactOneView” it take forever… and all other requery are fast…

 

[Chris Miller]

Let it be very clear, what takes long? The requery of the FactOneView?

Is this duration observation of the application behavior or did you actually measure the requery time by something like

startseconds=Seconds()
Requery("FactOneView")
endseconds=Seconds()
Messagebox(endseconds-startseconds)

I ask, because there could be many reasons you think the view runs long, either you think it already runs whereas it starts later than you think or its done fast as usual but you don't refresh the form.

Chriss

 

[Nro]

Hello Chris

1 – I open the “FactOneView”, store something in lnIdCode and requery(“FactOneView”) = 0,016 seconds

2 – I open and browse thru "FactBrowView", select one line, store the Id_Code in my variable lnIdCode and requery(“FactOneView”) = 82,677 seconds.

3 – If I re-do step 2, now, it only takes 0,019 seconds…

 

[Mike Lewis]

I open and browse thru "FactBrowView", select one line, store the Id_Code in my variable lnIdCode

If I've understood this right, the above statement is irrelevant. It's the requery of FactOneView where the problem lies. How and where you populate Id_Code doesn't affect that.

It would be useful to know if that that's correct, as it would enable us to eliminate FactBrowView from the discussion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

While it's a good idea to use a numbered bullet points list, you're still not precisely clear about what takes which time.

Bullet point 2 is both query of FactBrowView and requery of FactOneView. Yes or no, are you saying the requery of FactOneView takes the 82.677 seconds?

Are you requerying it or do you use it for the first time? Is it having the parameter set before it's queried? Do you have the views in the DE of the form? Still many open questions.

Regarding 1 you say you open “FactOneView” and set its parameter afterwards. That's not what to do, you always first set the parameter, then use or requery the view.

What works with first establishing the view is adding it to the DE explicitly with the option "nodataonload", then set parameter and then requery.

Chriss

 

[Chris Miller]

And what I suggest forr your workflow is:

1. If at all, only have “FactBrowView” in the form DE
2. Let the user pick one row in FactBrowView.
3. Set lnIdCode = FactBrowView.field (with whatever field is the relevant id). Important: Ensure there is a picked value, it's not NULL and you're not at EOF('FactBrowView').
4. Now USE FactOneView IN 0, or, if it's the second time, REQUERY('FactOneView').
5. Thisform.Refresh().

You thereby ensure you don't use FactBrowView without lnIdCode set to a valid value.

Chriss

 

[Chris Miller]

One other point could be outsoide influence, have an exception on scanning DBFs and FPT,CDX or folders of data in antivirus software. Both serverside and local.

Chriss

 

[Nro]

Ok, I’ll try clarifying again.

The problem is not with “FactBrowView”, it’s with “FactOneView”.

These are two different queries based on the same tables. I don’t have problem with “FactBrowView”, even if this query returns 70 000 rows. It’s fast enough.

My problem is when I requery “FactOneView” with “FactBrowView” populated with 70 000 records in the same datasession, the first time I requery “FactOneView”, it’s slow.
I’m sorry if my point 2 is not clear (again, it’s the first time I have this problem), the requery time of “FactBrowView” Is ok, but as soon as I requery “FactOneView”, it’s slow.

Some simplified example….

 CREATE DATABASE "C:\temp\data2"

 LOCAL lnIdCode
 lnIdCode = SPACE(10)
  
  CREATE SQL VIEW ("FactOneView") AS ; 
	SELECT &lcBaseAlias..*, ;
		CUS_MAIN.Mn_No1,;
		CUS_MAINst.Mn_No1 AS Mn_No1St, ;
		NVL(Tb_Port.Ct_Desc, SPACE(25)) AS Ct_Desc, ;
		NVL(Tb_Port.Ct_Fr_Desc, SPACE(25)) AS Ct_Fr_Desc, ;
		NVL(Tb_Vend.Vd_Label, SPACE(40)) AS Vd_Label, ;
		CAST(NVL(QUO_HEAD.Qd_Number,0) AS N(10,0)) AS Qd_Number ;
	 FROM &lcBaseAlias ;
		INNER JOIN CUS_MAIN ;
			ON &lcBaseAlias..Id_MnNo = CUS_MAIN.Mn_No ;
		INNER JOIN CUS_MAINst ;
			ON &lcBaseAlias..Id_St_MnNo = CUS_MAINst.Mn_No ;
		LEFT OUTER JOIN Tb_Vend ;
			ON CUS_MAIN.Mn_VdCode = Tb_Vend.Vd_Code ;
		LEFT OUTER JOIN Tb_Port;
			ON &lcBaseAlias..ID_FREIGHT = Tb_Port.Ct_Ind ;
		LEFT OUTER JOIN QUO_HEAD ;
			ON &lcBaseAlias..ID_QdCode = QUO_HEAD.Qd_Code;
	 WHERE &lcBaseAlias..ID_Code = ?lnIdCode

 USE "FactOneView" IN 0 NOREQUERY
 
 lnIdCode = "0000245138"
 
 *** Requery #1
 lnStart = SECONDS()
 REQUERY("FactOneView")
 lnEnd = SECONDS()
 MESSAGEBOX(lnEnd - lnStart, 0+64, "FactOneView - 1")
 *** -> 0,012 sec
 
   CREATE SQL VIEW ("FactBrowView") AS ; 
	SELECT &lcBaseAlias..ID_CODE, &lcBaseAlias..ID_NUMBER, ;
                &lcBaseAlias..ID_DATE, &lcBaseAlias..ID_CUST_PO, ;
                &lcBaseAlias..ID_DATE_PO, &lcBaseAlias..Id_CustPO,;
 		&lcBaseAlias..Id_Warehou, &lcBaseAlias..Id_Curr, ;
                &lcBaseAlias..ID_BO, &lcBaseAlias..ID_Interne, ;
		CUS_MAIN.Mn_No1,;
		CUS_MAINst.Mn_No1 AS Mn_No1St,;
		NVL(Tb_Vend.Vd_Label, SPACE(40)) AS Vd_Label, ;
		NVL(&lcTblDIMainAlias..Td_Desc, SPACE(120)) AS cTbDescDI, ;
		NVL(&lcTblAIMainAlias..Td_Desc, SPACE(120)) AS cTbDescAI ;
	 FROM &lcBaseAlias ;
		INNER JOIN CUS_MAIN ;
			ON &lcBaseAlias..Id_MnNo = CUS_MAIN.Mn_No ;
		INNER JOIN CUS_MAINst ;
			ON &lcBaseAlias..Id_St_MnNo = CUS_MAINst.Mn_No ;
		LEFT OUTER JOIN &lcTblDIMainAlias ;
			ON &lcBaseAlias..ID_INV_PO = &lcTblDIMainAlias..nOldCode ;
		LEFT OUTER JOIN &lcTblAIMainAlias ;
			ON &lcBaseAlias..Id_Act = &lcTblAIMainAlias..nOldCode ;
		LEFT OUTER JOIN Tb_Vend ;
			ON CUS_MAIN.Mn_VdCode = Tb_Vend.Vd_Code ;
     WHERE &lcBaseAlias..Id_Inv_PO = 3 OR &lcBaseAlias..Id_Inv_PO = 4

 USE "FactBrowView" IN 0 NOREQUERY
 REQUERY("FactBrowView")
 SELECT FactBrowView
 BROWSE
 
 lnIdCode = FactBrowView.Id_Code

 *** Requery #2
 lnStart = SECONDS()
 REQUERY("FactOneView")
 lnEnd = SECONDS()
 MESSAGEBOX(lnEnd - lnStart, 0+64, "FactOneView - 2")
 ********************
 *** -> 442,832 secs
 ********************

 *** Requery #3
 lnStart = SECONDS()
 REQUERY("FactOneView")
 lnEnd = SECONDS()
 MESSAGEBOX(lnEnd - lnStart, 0+64, "FactOneView - 3")
 ********************
 *** -> 0,044 secs
 ********************

As you can see, the result for REQUERY("FactOneView")#1 is ok, but REQUERY("FactOneView") #2 took 442,832 seconds. What’s strange, if I REQUERY("FactOneView") third time, the result is only 0,044 seconds.

 

[TamarGranor]

The first thing I'd do is get that macro operator out of there. Change it like this:

[pre] CREATE SQL VIEW ("FactOneView") AS ;
SELECT BaseAlias.*, ;
CUS_MAIN.Mn_No1,;
CUS_MAINst.Mn_No1 AS Mn_No1St, ;
NVL(Tb_Port.Ct_Desc, SPACE(25)) AS Ct_Desc, ;
NVL(Tb_Port.Ct_Fr_Desc, SPACE(25)) AS Ct_Fr_Desc, ;
NVL(Tb_Vend.Vd_Label, SPACE(40)) AS Vd_Label, ;
CAST(NVL(QUO_HEAD.Qd_Number,0) AS N(10,0)) AS Qd_Number ;
FROM (lcBaseAlias) BaseAlias ;
INNER JOIN CUS_MAIN ;
ON BaseAlias..Id_MnNo = CUS_MAIN.Mn_No ;
INNER JOIN CUS_MAINst ;
ON BaseAlias..Id_St_MnNo = CUS_MAINst.Mn_No ;
LEFT OUTER JOIN Tb_Vend ;
ON CUS_MAIN.Mn_VdCode = Tb_Vend.Vd_Code ;
LEFT OUTER JOIN Tb_Port;
ON cBaseAlias..ID_FREIGHT = Tb_Port.Ct_Ind ;
LEFT OUTER JOIN QUO_HEAD ;
ON cBaseAlias..ID_QdCode = QUO_HEAD.Qd_Code;
WHERE cBaseAlias.ID_Code = ?lnIdCode[/pre]

See how much difference that makes.

Tamar

 

[Chris Miller]

The problem with macro substitution of BaseAlias is already addressed.

USE "FactOneView" IN 0 NOREQUERY

and

USE "FactBrowView" IN 0 NOREQUERY

NOREQUERY is a clause only available for remote views.

I talked about nodataonload of the DE object, Mike said Nodata. I tried and you get no error on local views, but you also don't get the nodata behavior.

There might be your problem, confusing these clauses.

Besides that, I only see one major advantage of this using of views without loading data with the DE. You may get necessary information about the parameters with the form Init() first, so you can't query the views beforehand. Otherwise, if you don't want data from a view, then don't use it until you want data, that's the simplest way of not making a mistake like that. It doesn't yet explain why the requery of the FactOneView id takes long, but we might get there, if you take all that into account.

Chriss

 

[Steve Meyerson]

I may be barking up the wrong tree and/or displaying my own confusion about JOIN clauses, but can they sometimes add unwanted records, affect data, indexes and maybe even data type(s), and do something really not desired.

Would it be possible to run the queries without JOINs to see if the JOINs are part (or alll? of the problem.

Steve

 

[Nro]

Thanks for you feedback.

Sleep brings advice…

Maybe it’s a question of resources in the .BDC.

This morning, for a test, I create one database called C:\temp\data1
Then create, open and requery "FactOneView”. The result is fast : 0,009 sec

Then, I create a second .DBC (C:\temp\data2), create, open and requery "FactBrowView". After the browse, I requery "FactOneView” and the result is 0,455 sec

I’m surprised with the result, but when I show the SQL plan, there a lot of intermediate results so maybe the resources exhausted.

 CREATE DATABASE "C:\temp\data1"

 LOCAL lnIdCode
 lnIdCode = SPACE(10)
  
  CREATE SQL VIEW ("FactOneView") AS ; 
	SELECT Fac_Head.*, ;
		CUS_MAIN.Mn_No1,;
		CUS_MAINst.Mn_No1 AS Mn_No1St, ;
		NVL(Tb_Port.Ct_Desc, SPACE(25)) AS Ct_Desc, ;
		NVL(Tb_Port.Ct_Fr_Desc, SPACE(25)) AS Ct_Fr_Desc, ;
		NVL(Tb_Vend.Vd_Label, SPACE(40)) AS Vd_Label, ;
		CAST(NVL(QUO_HEAD.Qd_Number,0) AS N(10,0)) AS Qd_Number ;
	 FROM Fac_Head ;
		INNER JOIN CUS_MAIN ;
			ON Fac_Head.Id_MnNo = CUS_MAIN.Mn_No ;
		INNER JOIN CUS_MAINst ;
			ON Fac_Head.Id_St_MnNo = CUS_MAINst.Mn_No ;
		LEFT OUTER JOIN Tb_Vend ;
			ON CUS_MAIN.Mn_VdCode = Tb_Vend.Vd_Code ;
		LEFT OUTER JOIN Tb_Port;
			ON Fac_Head.ID_FREIGHT = Tb_Port.Ct_Ind ;
		LEFT OUTER JOIN QUO_HEAD ;
			ON Fac_Head.ID_QdCode = QUO_HEAD.Qd_Code;
	 WHERE Fac_Head.ID_Code = ?lnIdCode

 USE "FactOneView" IN 0 NOREQUERY
 
 lnIdCode = "0000245138"
 
 *** Requery #1
 lnStart = SECONDS()
 REQUERY("FactOneView")
 lnEnd = SECONDS()
 MESSAGEBOX(lnEnd - lnStart, 0+64, "FactOneView - 1")
 *** -> 0,009 sec

 CREATE DATABASE "C:\temp\data2"

   CREATE SQL VIEW ("FactBrowView") AS ; 
	SELECT Fac_Head.ID_CODE, Fac_Head.ID_NUMBER, ;
		Fac_Head.ID_DATE, Fac_Head.ID_CUST_PO, Fac_Head.ID_DATE_PO, Fac_Head.Id_CustPO,;
		Fac_Head.Id_Warehou, Fac_Head.Id_Curr, Fac_Head.ID_BO, Fac_Head.ID_Interne, ;
		Fac_Head.Id_MnNo, Fac_Head.Id_St_MnNo, Fac_Head.Id_Act, Fac_Head.ID_INV_PO, ;
		CUS_MAIN.Mn_No1,;
		CUS_MAINst.Mn_No1 AS Mn_No1St,;
		NVL(Tb_Vend.Vd_Label, SPACE(40)) AS Vd_Label, ;
		NVL(&lcTblDIMainAlias..Td_Desc, SPACE(120)) AS cTbDescDI, ;
		NVL(&lcTblAIMainAlias..Td_Desc, SPACE(120)) AS cTbDescAI ;
	 FROM Fac_Head ;
		INNER JOIN CUS_MAIN ;
			ON Fac_Head.Id_MnNo = CUS_MAIN.Mn_No ;
		INNER JOIN CUS_MAINst ;
			ON Fac_Head.Id_St_MnNo = CUS_MAINst.Mn_No ;
		LEFT OUTER JOIN &lcTblDIMainAlias ;
			ON Fac_Head.ID_INV_PO = &lcTblDIMainAlias..nOldCode ;
		LEFT OUTER JOIN &lcTblAIMainAlias ;
			ON Fac_Head.Id_Act = &lcTblAIMainAlias..nOldCode ;
		LEFT OUTER JOIN Tb_Vend ;
			ON CUS_MAIN.Mn_VdCode = Tb_Vend.Vd_Code ;
     WHERE &lcBaseAlias..Id_Inv_PO = 3 OR &lcBaseAlias..Id_Inv_PO = 4

 USE "FactBrowView" IN 0 NOREQUERY
 REQUERY("FactBrowView")
 SELECT FactBrowView
 BROWSE
 
 lnIdCode = FactBrowView.Id_Code

 *** Requery #2
 lnStart = SECONDS()
 REQUERY("FactOneView")
 lnEnd = SECONDS()
 MESSAGEBOX(lnEnd - lnStart, 0+64, "FactOneView - 2")
 ********************
 *** -> 0,455 secs
 ********************

 

[Chris Miller]

Are you referring to SYS(3054) when you talk of "Show plan"? For every join there is the use of an index and a possible intermediate result, it's virtual, in the first place, especially when the key is so selective this result in one final record only.

What you describe as demonstration code, is that what you actually do? Do you understand that views unlike tables have no data and only are the query you specify them to be? And do you also understand, that though there is no data stored in a DBF file, your view definition is stored in the DBC and doesn't need to be repeatedly defined? It's already known, you just use it, and when you use it you can change parameter values and requery it, unless buffered changes would hinder that. A simple requery mechanism also is closing the view with a USE (just like a table, actually you close the workarea) and then USE theview again. A requery usually is nicer with grids bound to the view alias, though.

What's stored about the view can be seen in the SL window of the view designer. It mainly shows the query of the view and many dbsetprops. They are already done and store all necessary information about the view in the DBC. There's no DBF file as there only needs to be this meta information, the query and several properties about view fields. And you then just USE the view.

Sorry if that's obvious, but you could clutter your DBC with repeatedly same query and view information it already knows and cause the view to become slow because of storing it and using it in a database with a steadily rising number of all the same views.

I hope its not what you're doing, because it would then contradict the view definition itself as something you define to reuse it. Then it is simpler to just execute the query, you don't need a view to execute a query especially when you always define slight variations of a view query to directly use it. Then just execute the query. It would takes a long time for a DBC to overflow with view definitions, though, as it itself is a table and so can hold 2GB of meta data about views.

Chriss

 

[Nro]

Hello Chriss

Thanks for your precisions. In fact I’m using SYS(3054,11) to show the SQL plan and optimization of the links.

Also, I’m sorry. This morning I think I was not all awake because I did my test on my testing environment, so my results are wrong. I then test on my production tables with the two views on different DBC and… same results : Requery of "FactOneView” take forever to complete.

I agree with you that the DBC only contain meta data about tables and views and no data at all, but I thought, maybe because of the complexity (meh, not that complex) SQL view it made a difference.

For your questions: no, I don’t recreate the view repeatedly. At the opening, the system creates a temporary DBC and the views are created on demands only once. For example, when the user opens the invoice form, the system show him the last invoice he was working on (requery FactOneView). So, it’s very fast. If the user wants to look for another invoice, he can search the exact invoice number (again it’s and indexseek() on the main table, and requery FactOneView, still very fast) or open a browse window (requery FactBrowView), choose an invoice number and the system requery FactOneView. The first requery is slow, but if I repeat the process, it’s fast.

All the views are created once and opened once. When the user closes the application, the views are closed and the temporary DBC is deleted, so the DBC is not cluttered with views.
I Think maybe it’s a questions of memory resources.

Thanks for your help. I’ll try to find something else.

 

[Nro]

Hello Mike

Yes, all the tables are opened before I create and requery the views. It's free tables so there is no tables in the DBC.

 

[Chris Miller]

I think were caught in a loop based on something you're missing, which you therefore not post (not aware of it) which we therefore can't take into account.
I don't think it's locks on the DBC, if you're recreating it "at the opening" (btw. opening of the form or opening of the application, application start?) that's local and only has one user that can't step on his own foot (notice you can't even fail with an RLOCK on a record you already have locked yourself, you even won't fail using a file you already used exclusively again. So it's actually an art to step on your own foot when you act on a local DBC or DBF even when you just accidentally have SET EXCLUSIVE ON).

The tables and data should be centralized/shared, aren't they? So from that my last attempt on this would be the question whether you are using data files exclusively. While that works up to the use of a view itself, it will fail on queries. Notice exclusive access is an environmental setting per data session. It won't help to once have set exclusive off. On the other side off is the default at runtime. So you'd have to actively set exclusive on with new data sessions to cause problems with concurrent access of shared files views need to query. Within the ID, when testing code, you could fail on the ID having exclusive on as a default.

Chriss

 

[Nro]

I think I find a solution of this weird problem. It’s the memo field in the “FactBrowView”.

   CREATE SQL VIEW ("FactBrowView") AS ; 
	SELECT &lcBaseAlias..ID_CODE, &lcBaseAlias..ID_NUMBER, ;
                &lcBaseAlias..ID_DATE, &lcBaseAlias..ID_CUST_PO, ;
                &lcBaseAlias..ID_DATE_PO, &lcBaseAlias..Id_CustPO,;
 		&lcBaseAlias..Id_Warehou, &lcBaseAlias..Id_Curr, ;
                &lcBaseAlias..ID_BO, &lcBaseAlias..ID_Interne, ;
.
.
.

I removed “&lcBaseAlias..ID_Interne” (the memo field) from the query and now all the requeries are working correctly.
So, I think when you return a large dataset (in this case 170000 lines) with a memo, it probably takes a lot of memory and slow down all the other operations.

Thanks for your feedback