Unexpected result with method Repository.SQLQuery

[carapa8]

Hi, i'm writing an Importer-Script for some xml files. The files are about 50-90 MBytes and contains a high amount of data that is stored within the EA-DB as Tagged Values. These are associated to components, interfaces and attributes.  Currently i have to wait about 30 minutes for an import process. I think the COM Interface is responsible for the slow process.

Before i write the data from the input xml file i synchronize it in a self written script. Therefore i have to read the complete EA model (package, element, operation, attribute class and all associated tagged values). To increase these process step i would like to read the data with the Repository.SQLQuery method but the method return an reduced xml response.

For example:
Following SQL Query inside EA: SELECT * FROM t_object
--> The result inside EA contains all columns that the t_object table contains.

When i use the same query with the method Repository.SQLQuery some required columns are missing like Alias. I think the implementation is limited  

Is this correct?

However maybe somebody else have another idea how to increase the performance.

Bye

[Paulus]

The implementation of SQLQuery is indeed limited but in a more tricky and less obvious way: the returned XML does not include an XML element for null(/empty?) values, on a row-by-row basis. .

Actually this is good news for you since you can use that knowledge to create your own wrapper SQLQuery method that handles such cases (at least, that's what i did, if you're interested check out http://community.sparxsystems.com/resources/scripts/execute-sql-query-and-return-result-dictionary-rows).

But if you've already written script that parses the returned XML it's probably a lot easier to simply modify your SQL so that it will -always- return a non-empty value for columns (e.g. using something like IsNull(Alias,"NULL!!!") or whatever is appropriate for your brand of DB), and check on that. 

My bet is that Alias will now appear in your XML result with a value of NULL!!! for all rows.

best regards,

Paulus

[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

[carapa8]

Thank you both for your help. I'l try both solutions and post my results soon.

[Geert Bellekens]

Another thing is that you should never use "select *" unless you really need ALL columns, AND you don't know which columns the table contains.

So, do you really need the alias column? Probably not since it is empty most of the time.

And yes, traversing the model using the API is very, very slow. Avoid that at all costs. Are you sure you need to load the whole model in order to synchronize?
My Excel VBA importer also does a kind of synchronization, but without the need to load the whole model. You might want to check that little tool as it is rather fast. (I think it imports attributes at a rate of a couple of thousand per minute)

Geert

[carapa8]

Hi Geert,
I have used your Excel script a few months ago an it was a great help. My problem is that I have a number of different input formats to handle for example AUTOSAR XML, an Model Based Simulink CSV description file, requirements from different management systems and so on. To handle all of these different formats i have designed an own data model in python that is able to process all of these information from different input files to different destination files. The EA model is only one possible output destination.

I agree with you that for a synchronization process it's not required to load the complete model to memory. This has been an design decision to get an generic tool that is able to generate different output formats that shall be synchronized with different input file formats.

I hope you understand me  

[Geert Bellekens]

Sure I understand.

In that case I would suggest you try the SQL repository from stao.
I've never used it myself, but it should be a lot faster when building a complete model in memory.

Geert

[carapa8]

Hello everybody,

thank you again for the cool tipps. The project of stao looks great but because i use the regular Python and not IronPython it's currently not usable for me. I started to change my framework and read data with the Method Repository.SQLQuery. The result for reading is great. To read about 8000 attributes, 200 components, 600 interfaces my old implementation needs about 5 minutes by usage of the COM data structure. Now i need a few seconds :-)

I start to think about to port the current implementation of stao to Python :-)

[stao]

Actually i have no idea what you mean with Python or IronPyhon :/
My Project is a simple Visual Studio 2010 Project.