AutoRefresh Overhead

[Gigasoft]

Hi all, I am writing a full blown multi user accounting package, this package will have at least 80 - 120 tables and who knows how many users.

I am using MySQL5 with the TMyTable and TMyQuery components.

My question is does anyone know what the overhead is or might be using AutoRefresh in this environment.

Also how do I stop the SQL hourglass from displaying each time the tables refresh?

Thanks in advance..

[AlexP]

Hello,

For automatic updating of your DataSet, you should set the MyQuery.Options.AutoRefresh option to True and set the needed interval between auto-updates in the MyQuery.Options.AutoRefreshInterval property (in seconds)
For avoiding hourglass cursor appearing when opening/updating DataSet, you can either remove the MyDACVcl module from the uses section or set the ChangeCursor global variable (declared in the DBaccess module) to False.

[Gigasoft]

Thanks for the quick response but I think you missed the point of the first part of my question,

I know the AutoRefresh and the AutoRefreshInterval will do what I want.

I need to know will the setting the AutoRefresh and AutoRefreshInterval properties of the queries and tables use huge amounts of system resources with 50+ users accessing the same database which will have over 100 tables, will this slow everything down to a snails pace?

Thanks
Daryl

[AlexP]

Hello,

Using of server resources at regular data refresh depends on various factors, such as: the server capacity, the resources provided to it, the weight of queries, the reload frequency, the ammount of data being refreshed simultaneously, etc. Therefore it is quite difficult to estimate exactly how much the server load will increase when using auto-refresh. You can define it experimentally only. To decrease the server load, you can add the timestamp field to tables. In this case, when using auto-refresh, the RefreshQuick method will be called, and only changed data will be taken from the server, unlikely the FullRefresh method using, where all data is read out from the server.

[skydvrz]

I need to know will the setting the AutoRefresh and AutoRefreshInterval properties of the queries and tables use huge amounts of system resources with 50+ users accessing the same database which will have over 100 tables, will this slow everything down to a snails pace?

I would not do that for all tables, all at once, but if you make your app "context sensitive" and only open/refresh queries that are visible in your app at the moment, you can cut down the amount of bandwidth consumed to an absolute minimum. I use tabbed notebooks in my apps and detect when a specific tab is visible. The event handler for the notebook then opens all the needed datasets and closes the non-visible ones. Another event handler for the notebook closes the queries for the previously visible tab.

Yes, there is a delay when changing contexts as the appropriate queries load, but it beats a 2 minute app startup time. I can also manually refresh the queries for a given context using a TTimer component and code similar to what you see below. I do this for some of my "live" grids.

A side benefit is that each time the user switches contexts, they are guaranteed that the data visible on screen is fresh - refresh timer or not. You may find that you don't actually need to force periodic refreshes.

I have 100+ tables in each of my apps, and lots of users.

Here is the basic concept. I use DevExpress notebook components, but this will work fine with other notebooks:

Code: Select all

// open/close the dataset
PROCEDURE DsMgr(CONST Ds : TDataSource; CONST Mode : TDsOpenClose);
BEGIN
    IF Assigned(Ds) THEN BEGIN
        IF Mode = EDsOpen THEN
            Ds.DataSet.Open
        ELSE
            Ds.DataSet.Close;
    END
    ELSE
        LogMsg('Unassigned DS', EERROR); // red alert!
END;

// OnChange handler for notebook - new tab is visible
PROCEDURE TMainFrm.MainNotebookChange(Sender : TObject);
BEGIN
    MainNotebookPageDsMgr(EDsOpen);
END;

// OnChanging handler for notebook - user clicked some other tab; close this old one
PROCEDURE TMainFrm.MainNotebookPageChanging(Sender : TObject; NewPage : TcxTabSheet; VAR AllowChange : Boolean);
BEGIN
    MainNotebookPageDsMgr(EDsClose);
END;

PROCEDURE TMainFrm.MainNotebookPageDsMgr(Mode : TDsOpenClose);
VAR
    Strg : STRING;
    Id :   UInt64;
BEGIN
    // ignore notebook changes upon FormCreate 
    IF cwDm.StartingUp OR (NOT Assigned(MainNoteBook.ActivePage)) THEN
        EXIT;

    TRY
        Screen.Cursor := crSQLWait;// this might take a few seconds
        // log user activity
        LogMsgFmt('%s %s', [MainNotebook.ActivePage.Name, OCStr[Mode]], EDEBUG);

        // figure out what tab we are dealing with
        // Cast the index into an enumerated pagename for clarity
        CASE TMajorTabs(MainNotebook.ActivePageIndex) OF
            EMajAllContacts:
            BEGIN
                DsMgr(cwDM.dsAllContacts, Mode);
                DsMgr(cwDm.dsAgencies, Mode);
                DsMgr(cwDm.dsMemberStatus, Mode);
                DsMgr(cwDm.dsMemberCertLevels, Mode);
                DsMgr(cwDM.dsCounty, Mode);
                AllContactsSubPageDsMgr(Mode); // handle subtabs on this tab
                AllContactsSubNotebookPageChange(NIL);
            END;
            EMajMembersOnly:
            BEGIN
                DsMgr(cwDM.dsMembersOnly, Mode);
                DsMgr(cwDm.dsAgencies, Mode);
                DsMgr(cwDm.dsMemberStatus, Mode);
                DsMgr(cwDm.dsMemberCertLevels, Mode);
                DsMgr(cwDM.dsCounty, Mode);
                MembersOnlySubPageDsMgr(Mode); // do sub-tab
            END;
            EMajAgencies:
            BEGIN
                DsMgr(cwDM.dsCounty, Mode);
                DsMgr(cwDM.dsAgencyRaw, Mode);
                DsMgr(cwDM.dsAgencies, Mode);
                DsMgr(cwDm.dsCountries, Mode);
                DsMgr(cwDm.dsStateProvince, Mode);
                DsMgr(cwDm.dsTngDistrict, Mode);
                AgencySubPageDsMgr(Mode); // do sub-tab
            END;
            EMajSearch:
            BEGIN
                DsMgr(cwDM.dsEmailSearch, Mode);
            END;
            EMajCommittees:
            BEGIN
                DsMgr(cwDm.dsCommitteeMaster, Mode);
                DsMgr(cwDm.dsCommitteeTypes, Mode);
                DsMgr(cwDm.dsAllContacts, Mode);
                DsMgr(cwDm.dsMemberNames, Mode);
                DsMgr(cwDm.dsWebFileMimeTypes, Mode);
                DsMgr(cwDm.dsCommitteeFiles, Mode);
                CommitteeSubPageDsMgr(Mode);
            END;
            EMajApplications:
            BEGIN
                DsMgr(cwDm.dsAgencies, Mode);
                DsMgr(cwDm.dsMemberTypes, Mode);
                DsMgr(cwDm.dsMembersOnly, Mode);
                DsMgr(cwDm.dsApplications, Mode);
                DsMgr(cwDm.dsApplicationType, Mode);
                DsMgr(cwDm.dsApplicationStatus, Mode);
            END;
            EMajWebFiles:
                WebFilesNotebookPageDsMgr(Mode);
            EMajNewsArticles:
            BEGIN
                DsMgr(cwDM.dsNews, Mode);
                DsMgr(cwDM.dsNewsCat, Mode);
            END;
            EMajReports:
            BEGIN
                ActiveReportList.AllowBuildList := TRUE;
                IF Mode = EdsOpen THEN
                    ReportSubNotebookChange(NIL);
            END;
            EMajEmploymentAds:
            BEGIN
                DsMgr(cwDm.dsEmployment, Mode);
                DsMgr(cwDm.dsEmploymentLinks, Mode);
                DsMgr(cwDm.dsEmploymentUnits, Mode);
            END;
            EMajStats:
                StatsSubPageDsMgr(Mode);
            EMajLegislature:
            BEGIN
                DsMgr(cwDm.dsSenators, Mode);
                DsMgr(cwDm.dsRepresentatives, Mode);
                DsMgr(cwDm.dsSenateDistricts, Mode);
                DsMgr(cwDm.dsHouseDistricts, Mode);
                DsMgr(cwDm.dsParties, Mode);
                DsMgr(cwDm.dsSenatorMembers, Mode);
                DsMgr(cwDm.dsRepresentativeMembers, Mode);
            END;
            EMajTrainingOps:
            BEGIN
                DsMgr(cwDm.dsTrainingOps, Mode);
                DsMgr(cwDm.dsTrainingLinks, Mode);
            END;
            EMajMemberServicesLinks:
                DsMgr(cwDm.dsMemberServiceLinks, Mode);
        END;
    FINALLY
        Screen.Cursor := crDefault;
    END;
END;