repository object access performance

[rayt]

Hello

I'm writing a scripted search plugin. Right now though I'm stumped because I've found that access through the Repository object seems to be too slow to be an option. A search for connectors by conveyed items took around 5 minutes to finish for our (ca. 20 MB) database.

Is there a way to speed things up, possibly accessing the database or getting SQL results through scripting?

Unfortunately using SQL directly is not possible since we have to do be able to do rather complex boolean combined queries.

Thanks for any tips.
Ray

[qwerty]

See my book :-)

[Geert Bellekens]

Ray,

Indeed, when you need to do searches like that you have to choice but to use SQL.
Fortunately the API provides Repository.SQLQuery() so at least you don't have to worry about connectionstrings and stuff like that.
qwerty's book is a good start.
You can also look at my open source EA Add-in framework.
in the Model class (line 243) I've implemented a couple of getXXXbyQuery() operations that allow me to quickly find some objects (or connectors, or...) based on an sql query.
I use these kind of operation in my add-in EA Navigator e.g. when looking for all messages that call an operation.

Geert

[rayt]

Qwerty, Geert, thanks for your quick replies.

The SQLQuery function looks very promising, I somehow missed it (I'm pretty new to this )

I'll definately take a look at your framework and EA Navigator, Geert, and at your book, qwerty.

Cheers,
Ray

[stao]

you can try out our sql repository.

http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1322259105

it can be used exactly like the common repository class
but will be much faster for big database searches

[rayt]

Thanks, stao, I'll definitely have a look at this too.

Btw using SQLQuery and an xmlDOM object works great together... Thanks again for the tip.

Code: [Select]

var xmlDOM = new ActiveXObject( "MSXML2.DOMDocument.4.0" );

xmlDOM.loadXML(Repository.SQLQuery("SELECT * FROM t_object"));
      
root = xmlDOM.documentElement;
oNodeList = root.childNodes;
for (var i=0; i<oNodeList.length; i++) {
      Item = oNodeList.item(i);
      Session.Output(Item.nodeName);
}