HomeActive Query BuilderVery Slow Speed if using Active Query Builder

Very Slow Speed if using Active Query Builder


Hello

Now I am evaluating your excellent Active Query Builder VCL version before purchasing.

I am using ODAC to connect to Oracle server, and scripts are stored in a TOraScript, executing through a TOraQuery. I can immediately get the script result from Oracle server.

But if I have ever passed these Oracle script to the Active Query Builder, whose MetadataProvider is assigned to a acCrODACMetadataProvider which is further linked to the OraSession I am using, then I will have speed problem.

1. very very slow speed for OraScript execution with CPU stay high.

To avoid possible data "flicking", even I have set following code but with no improvement.

acQueryBuilder->Enabled=false;
acQueryBuilder->MetadataProvider =NULL;
acQueryBuilder->BeginUpdate();
// now start script execution
OraScript->Execute();
ShowResult();

acQueryBuilder->MetadataProvider =acCrODACMetadataProvider;
acQueryBuilder->EndUpdate();
acQueryBuilder->Enabled=true;

2. At closure of TForm, the speed is very very slow, even I have added following code:

acQueryBuilder->Enabled=false;
acQueryBuilder->BeginUpdate();
acQueryBuilder->MetadataProvider =NULL;
acQueryBuilder->Clear(); // <-- very slow at this point. It will stay for 20 seconds to finish Clear();
//
ClearUpOthers();

acQueryBuilder->EndUpdate();

Please kindly check where is the speed trick?
liang rong | email
January 25 2011, 09:31 AM
Dear Liang,

Unfortunately we do not understand your first piont problem. The OraScript->Execute() and the ShowResult() calls are not related to Active Query Builder, right? Can you explain, how Active Query Builder relates to slow script execution by a third-party data access components? Please note that Active Query Builder is intended to build and parse SQL queries only. The only slow operations on loading Acitve Query Builder may be loading of metadata in case of large database schema and parsing of large SQL query on assigning it to the SQL property.
Regarding the second point, can you send us a query you have loaded to the component before clearing it, so we can investigate the problem?
Sergey Smagin | email
15 hours, 24 mins since original post
Hello

1. The first problem seems to be linked with a third-party Memo. After I switch to TSynMemo, this problem disappeared.

2. for the second problems, time is spend on Clear() and EndUpdate(). Since there a quite a lot oracle tables, so could be in the part of freeing the treenode of metadata.
//-- here is the code I used at destorying form
acQueryBuilder->Enabled=false;
acQueryBuilder->BeginUpdate();
acQueryBuilder->MetadataProvider =NULL;
acQueryBuilder->Clear(); // <-- very slow at this point. It will stay for 20 seconds to finish Clear();
//
ClearUpOthers();

acQueryBuilder->EndUpdate();// <-- very slow at this point


//----- here is the scripts used
<< SELECT statement skipped >>
LIANG | email
16 hours, 4 mins since original post
Dear Liang,

We agree that clearing large queries takes much time than loading, but it takes less than a second to clear your query. It becomes a problem on large queries (of 50k bytes text and bigger). We will investigate this problem.
Do you have large database schema loaded to the Metadata Container? If so, please send us the result of TacQueryBuilder.MetadataContainer.SaveToXMLFile method execution to support@activedbsoft.com.
Thank you in advance.
Sergey Smagin | email
1 week, 5 days since original post
Dear Liang,

unfortunately, I still can not reproduce your issue. I tried your SQL, but in my simple testing application AQB loads and clears it almost instantly.

I suppose MetadataContainer content can affect the performance, can you save your database metadata (using qb->MetadataContainer->SaveToXMLFile(filename)) and send it for testing?
Vitaly Sotnikov | email
1 week, 5 days since original post
This topic is closed