Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 1 to 10 of 11 total

Sat, Jun 13 2009 11:59 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim I've been building in triggers for audit logs for some tables, and encrypting some tables as well and I found it was suddenly taking c10secs for my app to complete its logon stuff (it was c1/4sec). For one table here are the tickcounts for opening: Encrypted with triggers 702 UnEncrypted with triggers 655 UnEncrypted without triggers 31 Testing in EDBManager I don't get this sort of difference. Anything that can be done (apart from eliminating triggers). Fileserver mode: ElevateDB 2.02b14 D2006 Vista Roy Lambert
Sun, Jun 14 2009 8:29 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim A bit more. With triggers on some tables then opening the email form in my app takes c4 secs without them c1.5secs. Strangely enough none of the tables I removed triggers from are opened in this forms oncreate or linked events. Roy Lambert Trigger example - there are three like this for 6 tables EXECUTE IMMEDIATE 'CREATE TRIGGER "InvoicesAfterInsert" AFTER INSERT ON "Invoices" BEGIN DECLARE ShortName VARCHAR; DECLARE LongName VARCHAR; DECLARE LogLevel VARCHAR; DECLARE Logging SENSITIVE CURSOR FOR LogTbl; DECLARE Naming SENSITIVE CURSOR FOR Names; DECLARE Config SENSITIVE CURSOR FOR ConfigParams; DECLARE BatchNo GUID; PREPARE ConfigParams FROM ''SELECT CAST(_ParamData AS VARCHAR(5) ) AS Level FROM Config WHERE _ID = ''''alCompanies''''''; OPEN Config; FETCH FROM Config(Level) INTO LogLevel; IF LogLevel <> ''None'' THEN SET BatchNo = CURRENT_GUID; PREPARE LogTbl FROM ''SELECT * FROM TfRLog''; PREPARE Names FROM ''SELECT Name AS ShortName, CAST(Description AS VARCHAR(30)) AS LongName FROM Configuration.Users WHERE Name = ?''; OPEN Naming USING CURRENT_USER; FETCH FROM Naming(ShortName) INTO ShortName; FETCH FROM Naming(LongName) INTO LongName; OPEN Logging; IF LogLevel = ''Field'' THEN INSERT INTO Logging(_Table, _Batch, _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _After) VALUES(''Invoices'',BatchNo,''Insert'',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._InvNo,''_ID'',NEWROW._ID); .... .... INSERT INTO Logging(_Table, _Batch, _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _After) VALUES(''Invoices'',BatchNo,''Insert'',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._InvNo,''_InvoiceLines'',NEWROW._InvoiceLines); ELSEIF LogLevel = ''Table'' THEN INSERT INTO Logging(_Table, _Batch, _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo) VALUES(''Invoices'',BatchNo,''Insert'',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._InvNo); END IF; END IF; END'; END
Mon, Jun 15 2009 1:41 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Roy, << I've been building in triggers for audit logs for some tables, and encrypting some tables as well and I found it was suddenly taking c10secs for my app to complete its logon stuff (it was c1/4sec). >> Are the triggers actually getting "triggered" ? If so, are they updating any tables that you also just encrypted ? -- Tim Young Elevate Software www.elevatesoft.com
Tue, Jun 16 2009 2:13 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim >Are the triggers actually getting "triggered" ? If so, are they updating >any tables that you also just encrypted ? Not that I can see. I have a number of data modules, one of which is for lookup tables. Some of these tables I can be fairly certain are going to be needed no matter which forms are used and those are opened as part of the start up process. Its these tables that are taking a lot of time to open. The table I quoted times for isn't used in any way in the main form, or the login form (which is the only other form called as part of the login process). The triggers are AFTER UPDATE, INSERT, DELETE. The table I quoted times for is Companies and I can guarantee that no companies are inserted and deleted as part of the logon process and I can 99.99999% guarantee that no companies are altered either. Actually I think I'll make that 100% since there are no entries in the log file and there would be since the default logging level is set to Field for this table. Roy Lambert
Tue, Jun 16 2009 1:55 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Roy, << Not that I can see. I have a number of data modules, one of which is for lookup tables. Some of these tables I can be fairly certain are going to be needed no matter which forms are used and those are opened as part of the start up process. Its these tables that are taking a lot of time to open. The table I quoted times for isn't used in any way in the main form, or the login form (which is the only other form called as part of the login process). The triggers are AFTER UPDATE, INSERT, DELETE. The table I quoted times for is Companies and I can guarantee that no companies are inserted and deleted as part of the logon process and I can 99.99999% guarantee that no companies are altered either. >> And in the EDB Manager you don't see anything at all in terms of a slower open time on these same tables ? If so, then it's got to be something outside of the engine/catalog level, i.e. at the TDataSet layer or higher. Are there any data-aware controls attached to these tables ? -- Tim Young Elevate Software www.elevatesoft.com
Wed, Jun 17 2009 2:35 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim >And in the EDB Manager you don't see anything at all in terms of a slower >open time on these same tables ? If so, then it's got to be something >outside of the engine/catalog level, i.e. at the TDataSet layer or higher. >Are there any data-aware controls attached to these tables ? That was my first thought - check for events, controls etc. but these lookup tables are attached to nothing in the GUI sense, have no events attached. They do have persistent fields defined but I can't see how that would affect things. In EDBManager opening the Contacts table, and populating the grid seems faster than just opening the table in my app. OK just done a couple more tests 1. to eliminate the possibility of it being the persistent fields dropped a brand new TnlhTable onto the datamodule, hooked it up to the contacts table, wrapped in gettickcount - result 2184 in the IDE 2044 outside 2. then thought it could be something weird with my subclassed table so dropped a bog standard TEDBTable on the datamodule, linked up to Contacts table wrapped in gettickcount - result 2169 in the IDE 3. Created a new app, new form, dropped engine, session, database and table components on c218 ticks to open Conclusion is that its something in my app, but what can it be? Its nothing linked directly to the table (my experiments with "clean" tables proves that) so it has to be something that can have an affect on the opening of tables, only when there are triggers attached (I think) and isn't directly linked or connected to the table. Any suggestions as to where to start looking? Roy Lambert
Wed, Jun 17 2009 3:20 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim More research. I was about to start deleting forms etc to isolate the problem when I thought MadExcept & FastMM debug mode. Turned off MadExcept, still taking 2secs+, turned off FastMM debug mode Contacts.Open drops to c350 ticks (still to long in my view but a marked improvement). It looks as though the culprit is FastMM's debug mode. Next I'll try downloading the latest version and see what happens. Roy Lambert
Wed, Jun 17 2009 3:28 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim Just had another thought, yonks back when I added FastMM debug mode I found I needed sharemem as well to get it to run (don't know why now). Just commented that out and I get an open of 359 ticks. So the question is what is happening with sharemem and triggers? Roy Lambert
Wed, Jun 17 2009 12:15 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Roy, << So the question is what is happening with sharemem and triggers? >> It's most likely just the memory allocation/deallocation that occurs during compilation of the triggers. It may simply be intense enough to cause a slight slowdown with sharemem included. Sharemem may introduce overhead that is not normally there. -- Tim Young Elevate Software www.elevatesoft.com
Thu, Jun 18 2009 2:03 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim Fair enough. I think I had sharemem in because otherwise leak reports wern't being generated. What's your opinion on 350 ticks to open a table with triggers? About right, to slow or something else? Roy Lambert

	Page 1 of 2	Next Page »
Jump to Page: 1 2