Sdac 4 slow with lookupfields

tonisanta · Post by **tonisanta** » Wed 04 Apr 2007 17:31

after upgrading from sdac3 to sdac4 all queries containing lookupfields are very slow when opening or when editing a record, too. Only setting the AutoCalcFields to false prevents this. When printing, filtering or displaying the records the lookupfields are very usefull. Please, have a look on this.
Best regards
Toni

Jackson · Post by **Jackson** » Thu 05 Apr 2007 13:25

Please specify how many lookup fields do you have in the main DataSet, the number of records in the main DataSet and in the lookup DataSets.
What options do you change for the main and lookup DataSets?

tonisanta · Post by **tonisanta** » Thu 05 Apr 2007 15:15

in one case the maindataset has 0 records, it's opened only for inserting a new record (select * from tbl where id=0; there are no records with id=0); it contains 7 lookupfields pointing to different lookuptables with a medium of approx. 100 records, only one of them contains 12000 records. All lookupfields are connected through integer-fields and the maindataset-fields point to the primarykey-field of the lookuptable.
in another case the maindataset contains approx. 50 records and has 4 lookupfields, 2 of them points to lookuptables with <40 records and the other 2 to the same lookuptable with about 12000 records. In most cases the maindataset-fields pointing to lookuptables contain Null (no lookupvalue specified).
In both above cases the maindataset contains an integer calculated field which in the OnCalcFields-event is set to a constant value;
Please, let me know if you cannot reproduce the slow behaviour so I'll try to extract the tables from my db and send you a database with a sample project .
best regards
Toni

Jackson · Post by **Jackson** » Fri 06 Apr 2007 09:24

We have made every effort but we can't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem; include definition of your own database objects or send us a database backup; don't use third party components.

Also supply us the following information:
- Exact version of Delphi or C++ Builder
- Exact version of Microsoft SQL Server and OLE DB provider you use. You can see it the Info sheet of TMSConnection Editor

tonisanta · Post by **tonisanta** » Fri 06 Apr 2007 14:51

I'm sorry. I cannot reproduce the slow behaviour with a sample application, still copying the connection- and MS-dataset-components from my main application and working with the same database. Now the latency seems to be resolved: there was a TMSQuery (cities, approx 12000 records) used as lookup-table for calculated fields and lookup-components (infopower) and this query had a lookupfield inside pointing to another TMSQuery. After deleting this lookupfield (it was never used) the performance returned like it was with Sdac3 (the lookupfield was there still with Sdac3).
I'm using D7 Enterprise, SQL-Server 8.00.2039, SQL Native Client 9.0.3042.00; setting the provider to Db Provider for SQL Server 8.00.1117 and connecting to a SQL2005 did not change the behaviour.

tonisanta · Post by **tonisanta** » Tue 10 Apr 2007 11:25

Now I figured out the problem: there is a boolean variable CalcKeyFields in MemDS.InternalLocateRecord not initialized to false; so when locating records it could happen (seems it depends on the PC or the single application if boolean variables have a default of true or false) the dataset enters into the OnCalcFields-event still if not necessary (if there are no calculated fields), executing code attached to this event. Please, initialize this variable to false. For me it resolved the 'speed'-problem.
regards, Toni

Jackson · Post by **Jackson** » Tue 10 Apr 2007 11:56

Thank you for information.
We have fixed the problem.
This fix will be included in the next build of SDAC 4.
Please watch for announcements at the forum.