Advice for using VirtualQuery

Advice for using VirtualQuery

Hello,

We are developing software under Embarcadero Sydney with TUniQuery component on Firebird or SQL Server DataBase. We have noticed that using a VirtualQuery is much faster (especially when sorting).

From there I have two questions :

1. Question
----------
So for more process speed, in some case, instead of doing this:
  1. OpenDBQueryFilterUniDir(qryValeurMes,UniCnct_CTRL_,TxtSQLTest_,'','ORDER BY NO_ID ASC'');
We do this (without sorting the "base" query, and sorting the VirtualQuery) :
  1. VirtualqryValeurMes:=TVirtualQuery.Create(Nil);
  2. OpenDBQueryFilterUniDir(qryValeurMes,UniCnct_CTRL_,TxtSQLTest_,'','');
  3. AddToVirtualQuery(vqryATraiter_,[InfoVQueryChild(QueryDtSetSource_,NomVirtQry_,True)],False);
  4. OpenDBVQueryFilter(VirtualqryValeurMes,'SELECT * FROM VIRTU_TBL_ORDER','',ORDER BY NO_ID ASC',False,[])
  5. vqryATraiter_.First;
Procedure AddToVirtualQuery :
  1. Procedure AddToVirtualQuery(vqryATraiter_ : TVirtualQuery; dseAAjouter_ : Array Of TInfoVQueryChild; ClearStructure_ : Boolean);
  2. Var
  3. IDataSet : Integer;
  4.  
  5. Begin
  6.  If ClearStructure_ = True Then CloseDBVQuery(vqryATraiter_,True);
  7. For IDataSet:=Low(dseAAjouter_) To High(dseAAjouter_) Do
  8. If dseAAjouter_[IDataSet].Ajouter = True Then
  9. With vqryATraiter_.SourceDataSets.Add Do
  10. Begin
  11. DataSet:=dseAAjouter_[IDataSet].DataSetComponent;
  12. SchemaName:='';
  13. TableName:=dseAAjouter_[IDataSet].DataSetName;
  14. End;
  15. End;
Function InfoVQueryChild :
  1. Function InfoVQueryChild(DataSetComponent_ : TDataSet; DataSetName_ : String; Ajouter_ : Boolean) : TInfoVQueryChild;
  3. Begin
  4. Result.DataSetComponent:=DataSetComponent_;
  5. Result.DataSetName:=DataSetName_;
  6. Result.Ajouter:=Ajouter_;
  7. End;

Function OpenDBVQueryFilter :

  1. procedure OpenDBVQueryFilterInfoCast(vqryATraiter_ : TVirtualQuery;  CodeSQL_,Fltr_,TriF_ : String; RequestLive_ : Boolean; FieldsAsString_,FieldsAsInt_ : Array Of String);
  3. Var
  4.  IField : Integer;

  6. Begin
  7. CloseDBVQuery(vqryATraiter_,False);
  8.  vqryATraiter_.UniDirectional:=False;
  9. vqryATraiter_.Options.StrictUpdate:=False;
  10. vqryATraiter_.Options.LongStrings:=False;
  11. vqryATraiter_.Filtered:=False;
  12. vqryATraiter_.Filter:='';
  13. vqryATraiter_.FilterSQL:='';
  14. vqryATraiter_.SQL.Add(CodeSQL_);
  15. vqryATraiter_.FetchRows:=NbrFetchRows;
  16. vqryATraiter_.MasterSource:=Nil;
  17. vqryATraiter_.DetailFields:='';
  18. vqryATraiter_.MasterFields:='';
  19. vqryATraiter_.ReadOnly:=Not RequestLive_;
  20. vqryATraiter_.RefreshOptions:=[];
  21. vqryATraiter_.DataTypeMap.Clear;
  22. If Length(FieldsAsString_) > 0 Then
  23. For IField:=Low(FieldsAsString_) To High(FieldsAsString_) Do
  24. vqryATraiter_.DataTypeMap.AddFieldNameRule(FieldsAsString_[IField],ftString);
  25.  
  26. If Length(FieldsAsInt_) > 0 Then
  27. For IField:=Low(FieldsAsInt_) To High(FieldsAsInt_) Do
  28. vqryATraiter_.DataTypeMap.AddFieldNameRule(FieldsAsInt_[IField],ftInteger);
  29. If Fltr_ <> '' Then
  30. Begin
  31. vqryATraiter_.SQL.Add('WHERE');
  32. vqryATraiter_.SQL.Add('('+Fltr_+')');
  33. End;
  34. If TriF_ <> '' Then vqryATraiter_.SQL.Add(TriF_);
  35. vqryATraiter_.Prepare;
  36. vqryATraiter_.Open;
  38. End;
Is it right to do that ? Do you have any advice to do that better ? 

2. Question
----------
An other problem : We have a version problem too. With the version 9.4.0 of Unidac this process works well. 
We have tried with the version 10.3.1 and we have a problem when we try to open a sorting VirtualQuery, it comes with an error : 

In french : "Enregistrement non trouvé". When we remove the sorting from the VirtualQuery, it's working.