Cache SQL UPDATE query

[Dom]

Hi people,

Need some help on an SQL UPDATE issue I am having...  Not even sure if the database structure is designed good or could be improved in some way...

Say I have two tables : CLUB and MEMBER...

Class club Extends %Persistent [ SqlTableName = CLUB ]

{

Property clubID As %String [ SqlColumnNumber = 2, SqlFieldName = CLUBID ];

...}

and

Class member Extends %Persistent [ SqlTableName = MEMBER ]

{

Property memberID As %Integer [ SqlColumnNumber = 2, SqlFieldName = MEMBERID];

...}

a MEMBER can only belong to one club at a time...

I have created a third table called CONTRACT that holds CLUB (and other information) that the MEMBER belongs too...

Class contract Extends %Persistent [ SqlTableName = CONTRACT ]

{

Property clubID As %Integer [ SqlColumnNumber = 2, SqlFieldName = CLUBID ];

Property memberID As %Integer [ SqlColumnNumber = 3, SqlFieldName = MEMBERID, ];

... }

 

I quite often UPDATE the contract table with a lot of updates, but it can take some time, maybe just under a seconds for each update, and was wondering if I could in some way speed it up - i.e. by having a primary key/index but not sure how to do this..

the update in question is as follows, which can take upto a second for each update to complete - and when I have 50k+ updates to make each day, it can take a while...

&sql(UPDATE CONTRACT (CLUBID,DATA1,DATA2,DATA3) Values (:club,:data1,:data2,:data3) where MEMBERID=:member)

Any help or advice appreciated...

[Brendan Bannon]

I would start with a simple index on MemberID in the Contract table.

 

That should speed things up for the insert.

 

Other changes you could make if this is not live yet.

 

Define an index with IDKey = 1 / true for ClubID and MemberID in those tables

 

Then you can change the 2 properties in the Contract table to be:

 

Property clubID As Club [ SqlColumnNumber = 2, SqlFieldName = CLUBID ];

Property memberID As Member [ SqlColumnNumber = 3, SqlFieldName = MEMBERID, ];

 

 

Brendan

--
--
IRIS, Caché, Ensemble
---
You received this message because you are subscribed to the Google Groups "IRIS, Caché, Ensemble" group.
To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.
To post to this group, send email to intersystems...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.