Gigasoft wrote:
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;