BorlandTalk.com

Rodrigo Zabotto · Guest

Dear friends,

I have here a SQLmonitor to study what bold do when I execute an OCL.

It´s crazy.

If I use de EvalInPS it reads ALL the instances on memory in the same
way. Worse, If I have 25000 instances it executes the same SQL 1000
times, because the clause IN can use only 250 parameters.

The myclass.allinstances->select(myfied = 1) executes:

SELECT BOLD_ID, BOLD_TYPE FROM MYCLASS
(25000 registers to memory)

SELECT D(ALL TABLE´S FIELDS), C.READ_ONLY
FROM MYCLASS D, BUSINESSCLASSESROOT C
WHERE D.BOLD_ID = C.BOLD_ID AND D.BOLD_ID IN (A LIST OF 250 BOLD_ID´s) -
it´s executed 1000 times.

and finally

SELECT (ALL TABLE´S FIELDS) FROM MYCLASS D
WHERE D.MYFIELD = 1

If I have columns on my grid that calls a myclass association with other
class, it executes the same sequence to each association and reads all
to memory.

Conclusion:

It takes more than 30 minutes to execute the query when the system is
running on a LAN.

If I made SELECT * FROM MYCLASS WHERE MYFIEL = 1 on IBOConsole it takes
some seconds (or minutes, depends on JOIN´s that I use).

What solution could I use to solve this problem? I´m trying to implement
direct SQL´s instead of OCL in hard queries. But it´s very difficult
to write some SQL´s if I have a Object Model.

What Do you do in this case (queries very large)? Is there a bold solution?

I´m using the normal link between bold and database and Firebird.

Thank´s for help.

PS: The EvalInPS is not a trust solution. I have some examples that I
create a register on DB and when I seek it after updating the listhandle
returns me count = 0.

Peter Morris [Droopy eyes · Guest

I haven't used it myself but there is a SQLHandle or something. Have you
tried using that instead?

--
Pete

Blessed are the geek, for they shall public class GeekEarth : Earth {}
====
Audio compression components, DIB graphics controls, ECO extensions,
FastStrings
http://www.droopyeyes.com
====

Efim Mett · Guest

Hi Rodrigo,

GetAllWithCondition is what you could use too, especially in business logic
layer.
The restriction remains though your result set should be conform with a
business object or BO list, so you can't use joins etc

If it is not sufficiant for your goals, you can execute SQL in TQuery or so
and manually import result BOLD_IDs into the object space. There was a handy
routine to do this: google for BoldObjectID

/Efim

Daniel Mauric · Guest

Hi Rodrigo,

As you have noticed, Bold generated SQL is not optimal. The options you have
are OCL in PS, TBoldSQLHandle (or TBoldSystem.GetAllInClassWithSQL, which is
the same thing), and finally pure SQL.
One thing that Bold does help with in case of pure SQL is database
abstraction, so that you can use it on different backends. You can get
IBoldDataBase from a TBoldAbstractDatabaseAdapter descendant and use it to
perform manual queries. You want your queries to return list of BOLD_ID and
BOLD_TYPE. You can then create TBoldObjectID for each and add them to a
TBoldObjectIdList which can then be loaded by Bold. Here's a code sample to
get you going:

lBOLD_ID := // BOLD_ID from SQL
lTopSortedIndex := // BOLD_TYPE from SQL
lObjectId := TBoldDefaultID.CreateWithClassID(lTopSortedIndex, true);
lObjectId.AsInteger:= lBOLD_ID;
lBoldObject := lBoldSystem.CreateExistingObjectByID(lObjectId);

that would be fetching them one by one, if you want to fetch them all at
once, add ObjectIDs to a TBoldObjectIdList and use
TBoldPersistenceController.PMFetch to get them all.

Regards,
Danny

Konstantin Gribachev · Guest

Hi, Rodrigo

My <Bold SQL Pack> has been especially designed for
any SQL-based actions with Bold Objects. It consists of

1. BoldSQLListHandle - Receiving objects collection according to
SQL-query.
2. BoldSQLListHandleSP - Receiving objects collection according to
the result of the stored procedure execution.
3. BoldSQLVariableHandle - Generating value of the Bold-variable
according to SQL-query.
4. BoldSQLVariableHandleSP - Generating value of the Bold-variable
according to the result of the stored procedure execution.

Now I design some examples/help for this pack.
It will be finished in January

Best regards
Konstantin Gribachev

On Wed, 13 Dec 2006 18:59:34 -0300, Rodrigo Zabotto
<rodrigo (AT) isiengenharia (DOT) com.br> wrote:

Rodrigo Zabotto · Guest

Hi friends,

Thank for aswers.

But something is happing here:
I create a SQL viewer in my app.

I execute a SQL = select * from myclass and it shows me data.

I made some changes on myclass atributes by BOLD and updatedatabase.

I open a query by OCL (myclass.allinstances) and changes are there.

I execute again the same SQL and the changes AREN´T there.

If I go to IBOConsole and execute the same SQL the changes ARE there (it
means the changes are saved on database).

Any Idea???

I´m use a TIBQuery linked to a IBDatabase (the same of Bold) and
IBTransaction.

Thanks again

Rodrigo

Rodrigo Zabotto wrote:

Yuri · Guest

Hello, dear friend Konstantin

Your <Bold SQL Pack> is ready?
As we can see, the problem is general.
Thanks

Yuri

"Konstantin Gribachev" <grikon (AT) yandex (DOT) ru> escreveu na mensagem
news:6be0p2tm16rrv1vhckpe2jrbs72uqmmuke (AT) 4ax (DOT) com...

Jan Norden [ECO group] · Guest

Rodrigo Zabotto wrote: