Historical attributes and Triggers

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Historical attributes and Triggers

Hamid
Hi,

I have entity with 200 different attributes and nost of them (90%) are changable attributes that I need to keep track of changes.

I am looking to see if triggers that genarated by modeler can handle that. instaed of I manually check every single attributes to see if there is any changes.

Any thoughts?

Thanks
Hamid
Reply | Threaded
Open this post in threaded view
|

Re: Historical attributes and Triggers

roenbaeck
Administrator
If you turn on 'idempotency' for the historized attributes, the triggers will silently ignore any attempt to update a value to the same value again. And 200 attributes should be no problem at all for the triggers. They are only limited by the maximum code size in SQL Server, which is at several megabytes of SQL code.
Reply | Threaded
Open this post in threaded view
|

Re: Historical attributes and Triggers

Hamid
Thanks for your reply.

Actually we see the issue with SQL server now that we hit the limit and we have to split to different anchors so we can have smaller attributes and SQL server can handle it.

Has someone tried to use this model for trade (Financial) systems that it could help us for our system?

Thanks
Hamid
Reply | Threaded
Open this post in threaded view
|

Re: Historical attributes and Triggers

roenbaeck
Administrator
Exactly which limit did you hit? I can investigate and see if there are other workarounds or if it can be avoided completely.

As for financial trading systems, there are none that I am aware of. I have implemented a lifetime value system in the financial industry based on AM, and I do now a fair bit about trading, having daytraded myself. If you let me know what you need help with I may be able to answer some of it or find someone else who knows. You can mail me if you'd rather take such a discussion there instead: lars.ronnback@anchormodeling.com.
Reply | Threaded
Open this post in threaded view
|

Re: Historical attributes and Triggers

Hamid
the error message we get is:

"There is insufficient memory in resource pool ;default; to run this query"

we get this error on one of update trigger.

I will send you email to discuss about trade system model. My email is "bsabetfar@yahoo.com"

Thanks
Hamid
Reply | Threaded
Open this post in threaded view
|

Re: Historical attributes and Triggers

roenbaeck
Administrator
Which version of SQL Server are you running, and any Service Packs? It appears that for some people installing SQL Server 2008 SP1 has resolved the problem.

http://support.microsoft.com/kb/968382

The problem is caused by SQL Server running out of RAM, so if you have very little RAM memory in your server it can't execute long SQL statements. Other reports say that turning off the "Resource Governor" in the database made the problem go away. This governor can limit the amount of memory available for execution even if you have lots of RAM. You can read more about the governor here:

http://technet.microsoft.com/en-us/library/bb933866.aspx

Also, if you are on a 32-bit platform, some people also had to double the MemToLeave parameter. It is a startup parameter specified with -g512 (double the normal 256MB). The size of executable SQL statements has to fit in this memory on 32-bit platforms. How to set startup parameters can be found here:

http://msdn.microsoft.com/en-us/library/ms190737.aspx

If you have a lower compatibility mode set on your database than the default, this may also cause the problem, as that database sort of becomes "sandboxed" and gets resource limits different from what is stated for the version you are running.

Please, let us know if any of these helped.