Connect is Busy - OK or Revert?

[stanlyn]

Hi,

When I first load a form, I set some values and then it runs a cursorfill() event and shown me the records in a grid. So far so good...

Now if I click another record in the grid, I'm presented with a "Connection is Busy" dialog with options of OK, Revert and Help. I can select either OK or Revert and it continues on as if its all ok...

There has been no known updates or changes to the cursor, and was wondering why it is thinking that data was changed.

Any ideas?
Stanley

 

[JRB-Bldr]

I populate the child grid in the parent's AfterRowColChange

Maybe you could get ALL of the Child records at the same time you get ALL of the Parent records (or effectively 'at the same time') and then merely use an Index (or Filter) on the Child cursor to only display those Child records which match the desired Parent record.

In that way, you would not need to do a re-query of the backend each time you change Parent records.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Hi Stanley,

JRB-Bldr is right, thoiugh I recommended to go the route of querying only child records of the current parent record at any time.

What you should try to speed up the process is NOT to reset the child grid recordsource. Because the good thing about CA cursors is, a cursorfill or rerquery of them does not cause grid reconstruction.

All you need is:

gcCountyPageKey = crsParent.county_pagekey
Requery('crsChild')
.grdChild.Refresh()

Setting the SelectCmd should be done once only, as it doesn't change at all, you change the variable gcCountyPageKey, that is sufficient. Also Requery() of the CA alias does cause CursorRefresh(), you don't need to address the CA object at all.

Additional to that you should only init the gcCountyPageKey with the initial parent key and CursorFill() to get the initial crsChild alias and bind the childgrid to it once. You don't need to unbind via Recordsource = Space(0), CA cursor requery does not cause grid reconstruction, like a view cursor requery(), this is one of the good things about CA in contrast to SPT.

Bye, Olaf.

 

[Olaf Doschke]

The only other question I have is: Do you get the same "connection busy" effect, if you don't scroll through the grid via DOWNARROW key on hold, but via Scrollbar and click on some row?

Using keys you will cause many rowchange events, while using the scrollbar does not trigger rowcolchange until you click into a different row.

So if that helps I already have an idea to relay the crsChild requery via a timer. rowchange would set timer.enabled = .t. and call timer.Reset(), the timer interval would be something like 100ms and the timer event would do the Requery('crsChil'), but since each rowchange would reset the timer only after you stop and wat a 1/10 second the requery() would run. Additional to that the timer event of the timer should disable the timer, so you only requery() once.

Bye, Olaf.

 

[stanlyn]

>> Maybe you could get ALL of the Child records at the same time you get ALL of the Parent records (or effectively 'at the same time') and then merely use an Index (or Filter) on the Child cursor to only display those Child records which match the desired Parent record. In that way, you would not need to do a re-query of the backend each time you change Parent records.

You would get the list of parents and then loop thru each one of them building a child cursor because the keys are too independent and could not be gathered a a single where clause. This is what they look like:
KY0661QA09C6I4
KY06630L0JOHRA
KY0662LA0SZUZ2
KY0662LA0T42OV
KY0662LA0TBDP2
KY0661X40U768F
KY0663940JGBSL
KY0663940JHNJ8
KY0663940JIFU2
KY0662WH0O001L
KY0662970J06QZ
KY1931H80Q6577
KY1931H80Q657I
KY1931H80QCE4D

Is there any way to set up something like a relation, as Olaf stated that we cannot do relations.

I don't want the (what appears to be duplicates of the parents) that you get if you do it all in a single sql with a join.

any suggestions?
Stanley

 

[Olaf Doschke]

There is a simple way to retrieve parents and childs with two CAs, without joins, still using the same filter:

a) select * from parent where yourwherclause
b) select child.* from child inner join parent on child.fk = parent.pk where yourwhereclause

pk meaning primary key and fk foreign key.

See?

You apply the same filter twice and still, though joining parent dat, in the second query, only select child table fields. I already layed out that idea earlier.

The data is related in the server.

You can go one step further and allow filtering data of both parent and child fields, by applying the join in both queries:

a) select parent.* from parent left join child on child.fk = parent.pk group by parent.pk where yourwhereclause
b) select child.* from parent left join child on child.fk = parent.pk where yourwhereclause

The two selects now only differ in the fields they select into the result, and you can filter parent data with child fields and child data with parent fields. For example you can find orders from a certain customer (where clause adressig the orders table order.customerid) ordering a certain product (where clause addressing the orderitems table orderitems.productid) and limit both results to only contain the relevant orders and orderitems fulfilling both criteria.

Relations are joins, and now you do them on the server, not on the client, to just pull the data needed.

Bye, Olaf.

 

[Olaf Doschke]

a) group by parent.pk
Sorry, wouldn't be sufficient, rather use select DISTINCT
or a subquery:

select parent.* from parent where yourparentwhereclause
and parent.pk in
(select child.fk from child where yourchildwhereclause)

Something like that.

Bye, Olaf.

 

[stanlyn]

>> JRB-Bldr is right, thoiugh I recommended to go the route of querying only child records of the current parent record at any time.

Thats precisely what I'm doing... (or attempting to do), and it does work quickly after waiting the first initial minute while it is quitely busy.


>> speed up the process is NOT to reset the child grid recordsource
Done... No recordsource changes now... Here is the AfterRowColChange event code including commented out code.

With Thisform As Form
gcCountyPageKey = crsPage.county_pagekey

**************************************************
* PartyName #1 Lookup

aa = "select party_name, party_type, county_pagekey, creator, CREATION_DATE "
bb = "from PartyName where county_pagekey = ?gcCountyPageKey and party_type = '1- Grantor'"
dd = aa+bb

*= SQLPREPARE(lnDS, dd)
*= SQLEXEC(lnDS,dd)

*.DE_AppMain.caPartyName.SelectCmd = dd
dd = ''

REQUERY('crsPartyName')

*.PageFrame1.pagPartyName.grdPartyNameParty1.RecordSource = Space(0)
*.DE_AppMain.caPartyName.CursorFill()
*CursorSetProp("Buffering",5,'crsPartyName')
*.PageFrame1.pagPartyName.grdPartyNameParty1.RecordSource = 'crsPartyName'

.PageFrame1.pagPartyName.grdPartyNameParty1.AutoFit
.PageFrame1.pagPartyName.grdPartyNameParty1.Refresh()

Same delayed result but twice as fast as it now takes 20-30 seconds. Also note the fetch sizes are 100 with a max of 200 for the PartyName CA, while it is 25 with a max of 50 for the page CA. And it on a local gigabit network where both the server and the workstation are extremely fast with I7 cpus... And I'm the only user on the system...


>> The only other question I have is: Do you get the same "connection busy" effect, if you don't scroll through the grid via DOWNARROW key on hold, but via Scrollbar and click on some row?

I set 4 pieces of criteria before fetching the PAGE data and they are:
1. ?gcCountyId ie. 'KY066'
2. ?gcPageType ie. 'DEED'
3. ?gcBookNum ie. '200'
4. ?gcStartPage ie. '1'

This is returned immediately, (split second), and the Page grid is fully populated. The grid receives focus immediately and I manually call the AfterRolColChange event so it can populate the PartyNames grid with partynames that belongs to the page, and that takes 20-30 seconds. Actually you will never see it unless you do something to cause a grid or form refresh, and that requires navigating the page/parent grid. If you only use the scrollbar, you will never see anything as that never triggers a refresh. I did just now add a refresh to the grid's scrolled event, and it too is 20 -30 seconds late.

Once the PartyNames grid finally loads, its speed and ability to match the proper partyname records with their parents is fine and fast...

I'm confused about this Syncronis mode thing. Is it working and why is VFP continuing when its been suggested that vfp waits when Syncronious mode...


More after some testing...
Stanley

 

[stanlyn]

THE ISSUE MAY HAVE BEEN IDENTIFIED...

While using SSMS I discovered that SqlServer is allowing the query to go into suspended mode and can possible resume or timeout. The query I used to find this is:

SELECT * FROM(
select
spid,
loginame,
DatabaseName= (select Name from sys.databases where database_id = P.dbid),
program_name,
cmd,
Query = (select TEXT from ::fn_get_sql(P.sql_handle)),
lastwaittype,
waitresource,
cpu,
physical_io,
login_time,
status,
hostname
from
sys.sysprocesses P
) DATA
where 1=1



On the process in question there was a "LastWaitType" that was "ASYNC Network IO" and web page

http://www.confio.com/English/Tips/Async_Network_IO.php

describes it as:

The “async network io” (in SQL 2005/2008) and “networkio” (in SQL 2000) wait types can point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough. This will result in filling the network buffers so that SQL Server cannot send more data to the client. Therefore, the process executing the batch will need to wait for the ability to continue sending results to the client. More........



I found this article from

http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/942fe3d4-141a-486a-afdf-53f079d3a4d3/

that is related to the suspended issue:

my solution was to set 'Maximum insert commit size' value to 50000, for all OLD DB Destination in my packages. And the random 'Suspended mode' then disappeared.



I'm retrieving small sets and I cannot imagine why I'm having such issues on trival issues...

Does anyone want to take a stab at this???
Stanley

 

[Olaf Doschke]

So you say just the initial single child data query alone takes 20-30 seconds?

How about adding in SQL Server Profiler? You can eg start it from Extras in the SSMS and then monitor what's really going on.

Just one very obvious question: Are you having any indexes in your tables? If not: WHY? At least primary and foreign keys should have an index.

Bye, Olaf.

 

[stanlyn]

>> So you say just the initial single child data query alone takes 20-30 seconds?

Yes, and instantly for the parent table. Once the child table loads, changing records in the parent table shows the child records instantly.


>> Are you having any indexes in your tables?

Yes, I have indexes on any field that may be used in a where clause.


>> How about adding in SQL Server Profiler?

Where is that done, on the vfp side or mssql? I've never used it.

Thanks,
Stanley

 

[Olaf Doschke]

I said:
So you say just the initial single child data query alone takes 20-30 seconds?

You said:
Yes, and instantly for the parent table. Once the child table loads, changing records in the parent table shows the child records instantly.

So are you sure your CA doesn't autoload data before you first do cursorfill()? It seems like you load the whole table, taking that much time. Set the CA to SELECT * FROM Child WHERE 1=0 (I already proposed this type of WHERE clause default), so you are sure the CA will only query an empty cursor before you have changed the SelectCmd. You know, there also is automatic filling of the CA table purely by it's instanciation.

I said:
How about adding in SQL Server Profiler?

You said:
Where is that done, on the vfp side or mssql? I've never used it.

You can eg start it from Extras in the SSMS and then monitor what's really going on.

Look at the SSMS menu, it has a main menu "Extras", Sql Server Profiler is there. It's also is in the Windows Startmenu in "Performance Tools".

If you type "Profiler" in the Search box of the start menu it should also be found. If not, you didn't install all features of SQL Server.

Google SQL Server Profiler and see what it can do for you. Besides logging and monitoring queries and their execution time, it can also make recommendations on indexing, for example.

Bye, Olaf.