Performance of complex queries as Model Search vs SQLQuery

[Ian Mitchell]

I have a complex query, with lots of joins, which runs fine as search in EA, and in Access as well.
It also runs very quickly in both environments.
When I submit the same query to the EARepository.SQLQuery() API (because a want to process the results myself) then the query just goes away and never returns. No SQL errors, just goes away.
I found the same thing previously, with some wonderful SQL provided by Geert (and so = correct SQL) which also ran prefectly in Access, but really slowly via 'SQLQuery'. Sure, it returned a result, eventually, but after ~30 sec, but the same SQL ran in <1 sec via the other routes.

I this a known issue? Is there a solution?
When I just ran simple SQL, life was good. Now I'm trying to emulate Geert, and it's not going well.

[Geert Bellekens]

Hi Ian,

That is a bit weird. If it works as a search, I would expect the same in SQLQuery() since both use almost exactly the same technology in the background (I believe)

EA executes the query on the database, and then puts the results in a xml string and then the client processes this xml.

One of the thing that does trip it is if you use aliases with spaces. So something like select o.Name as [alias with a space] from t_object o will generate an xml error.
Other then that I wouldn't know.
I write and execute comples queries all the time, and I've never witnessed a performance difference between direct or through SQLQuery()

On the other hand, I work almost exclusively with SQL Server repositories, so it might be a .eap related problem.

Geert

[Ian Mitchell]

Thanks Geert,
 The SQL is executing fine in all cases - it's purely the performance.
All cases are working with a local EAPX file, which is why I'm so confused.
Has anyone else seen this? As an example, try the 'Find Orphans' SQL, as on your website.

[Ian Mitchell]

OK, so it' just me then?
Really? Nobody else sees a difference in performance where you submit a complex query (with multiple joins) via the API.

[OpenIT Solutions]

Hi Ian,

Are you using a cloud connection? I have the same issue over a cloud connection. When i run with a direct db connection the Repository.SQLQuery is fine. I think its an http timeout issue, so have posted a question on PCS group re setting the timeout - no answer yet. I'll submit a support ticket for it and let you know...

[Ian Mitchell]

No - just a simple local EAPX file. That's why I'm so confused.