Jump to content
saeedbay

hourglass and freezing problem with Firedac

By saeedbay, in Databases

Recommended Posts

saeedbay    0

saeedbay
Posted (edited)

i'm converted ADO Connections and AdoQuerys To FDConnection and FDQuerys in MyProject to Improving Connection Quality to MS SQL Server 2012 Database

The previous Ado version worked fine in Running Query Commands   But in the current FD version, the system hangs most of the time.
Cursor Shows Hourglass Icon and Sql Word Below HourGlass Cursor and The program is suspended.so that the user is forced to use the َ Alt+Ctrl+Del keys
maybe FireDac Not Compatile with sql 2012?

Edited by saeedbay

Share this post

Link to post

Lars Fosdal    1792

Lars Fosdal
Posted

It is compatible with all MS SQL servers. Currently using the same FireDAC integration with 2008, 2012, 2016,2017 and 2019.

 

How do you parameterize your connection?

Have you double checked that your query doesn't pull back a massive amount of data?

Check the FireDAC Monitor to see if it offers any clues.

 

Which driver are you using? I recommend using the ODBC drivers. 

class function TPSDFireDatabasePoolMSSQL.FindBestDriver(const Link: TFDPhysMSSQLDriverLink): String;
const // Constants copied from implementation section of FireDAC.Phys.MSSQL
  C_SQL_SERVER = 'SQL Server'; // DO NOT TRANSLATE
  C_2019_ODBC = 'ODBC DRIVER 19 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2018_ODBC = 'ODBC DRIVER 18 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2017_ODBC = 'ODBC DRIVER 17 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2016_ODBC = 'ODBC DRIVER 13 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2012_ODBC = 'ODBC DRIVER 11 FOR SQL SERVER'; // DO NOT TRANSLATE
{$IFDEF POSIX}
  C_FreeTDS = 'FreeTDS'; // DO NOT TRANSLATE
{$ENDIF}
{$IFDEF MSWINDOWS}
  C_2012_NC = 'SQL SERVER NATIVE CLIENT 11.0'; // DO NOT TRANSLATE
{$ENDIF}
var
  DriverList : TStringList;
  WantedList : TArray<String>;
  Driver: string;
begin
  Result := ''; // Blank = Default

  WantedList := {$IFDEF MSWINDOWS}
                  {$IFDEF SQLNative}
                    [C_2012_NC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC]
                  {$ELSE}
                    [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_NC, C_2012_ODBC]
                 {$ENDIF}
               {$ENDIF}
               {$IFDEF POSIX}
                 [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC, C_FreeTDS]
               {$ENDIF};

  DriverList := TStringList.Create;
  try
    Link.GetDrivers(DriverList);

    DebugOut('Available SQL drivers'); // DO NOT TRANSLATE
    for Driver in DriverList
     do DebugOut(' "' + Driver + '"');

    for var Wanted in WantedList
     do for Driver in DriverList
      do begin
        if CompareText(Wanted , Driver) = 0
        then begin
          DebugOut('Selected driver: "' + Driver + '"'); // DO NOT TRANSLATE
          BestDriver := Driver;
          Exit(Driver);
        end;
      end;
  finally
    DriverList.Free;
  end;
end;

 

Share this post

Link to post

Die Holländer    45

Die Holländer
Posted (edited)

Maybe it depends on which SQL driver the FD is using on the users machine.

It uses a loop from the most recent driver (Delphi 11 : from version 17) to the least one

and tries to find the most recent one. Look in the ODBC settings which drivers are

installed on the machine. I've used many versions of MS SQL servers (also 2012) and

had never problems with FD connecting them. Look at SQL Sever Native Client 10. for older SQL sever, like 2012.

 

MSSQL Driver overview

Edited by Die Holländer

Share this post

Link to post

saeedbay    0

saeedbay
Posted
11 hours ago, Lars Fosdal said:

It is compatible with all MS SQL servers. Currently using the same FireDAC integration with 2008, 2012, 2016,2017 and 2019.

 

How do you parameterize your connection?

Have you double checked that your query doesn't pull back a massive amount of data?

Check the FireDAC Monitor to see if it offers any clues.

 

Which driver are you using? I recommend using the ODBC drivers. 


class function TPSDFireDatabasePoolMSSQL.FindBestDriver(const Link: TFDPhysMSSQLDriverLink): String;
const // Constants copied from implementation section of FireDAC.Phys.MSSQL
  C_SQL_SERVER = 'SQL Server'; // DO NOT TRANSLATE
  C_2019_ODBC = 'ODBC DRIVER 19 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2018_ODBC = 'ODBC DRIVER 18 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2017_ODBC = 'ODBC DRIVER 17 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2016_ODBC = 'ODBC DRIVER 13 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2012_ODBC = 'ODBC DRIVER 11 FOR SQL SERVER'; // DO NOT TRANSLATE
{$IFDEF POSIX}
  C_FreeTDS = 'FreeTDS'; // DO NOT TRANSLATE
{$ENDIF}
{$IFDEF MSWINDOWS}
  C_2012_NC = 'SQL SERVER NATIVE CLIENT 11.0'; // DO NOT TRANSLATE
{$ENDIF}
var
  DriverList : TStringList;
  WantedList : TArray<String>;
  Driver: string;
begin
  Result := ''; // Blank = Default

  WantedList := {$IFDEF MSWINDOWS}
                  {$IFDEF SQLNative}
                    [C_2012_NC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC]
                  {$ELSE}
                    [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_NC, C_2012_ODBC]
                 {$ENDIF}
               {$ENDIF}
               {$IFDEF POSIX}
                 [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC, C_FreeTDS]
               {$ENDIF};

  DriverList := TStringList.Create;
  try
    Link.GetDrivers(DriverList);

    DebugOut('Available SQL drivers'); // DO NOT TRANSLATE
    for Driver in DriverList
     do DebugOut(' "' + Driver + '"');

    for var Wanted in WantedList
     do for Driver in DriverList
      do begin
        if CompareText(Wanted , Driver) = 0
        then begin
          DebugOut('Selected driver: "' + Driver + '"'); // DO NOT TRANSLATE
          BestDriver := Driver;
          Exit(Driver);
        end;
      end;
  finally
    DriverList.Free;
  end;
end;

 

I was using ُSQL Native Client 11 until last week but But I have been using ODBC 18 recently 
But Result are same.
 

Share this post

Link to post

Lars Fosdal    1792

Lars Fosdal
Posted

Capture your query and run it in SSMS to get the execution plan. Perhaps you can improve performance with indexes.

Share this post

Link to post

saeedbay    0

saeedbay
Posted (edited)
On 5/2/2024 at 9:56 AM, Lars Fosdal said:

It is compatible with all MS SQL servers. Currently using the same FireDAC integration with 2008, 2012, 2016,2017 and 2019.

 

How do you parameterize your connection?

Have you double checked that your query doesn't pull back a massive amount of data?

Check the FireDAC Monitor to see if it offers any clues.

 

Which driver are you using? I recommend using the ODBC drivers. 


class function TPSDFireDatabasePoolMSSQL.FindBestDriver(const Link: TFDPhysMSSQLDriverLink): String;
const // Constants copied from implementation section of FireDAC.Phys.MSSQL
  C_SQL_SERVER = 'SQL Server'; // DO NOT TRANSLATE
  C_2019_ODBC = 'ODBC DRIVER 19 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2018_ODBC = 'ODBC DRIVER 18 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2017_ODBC = 'ODBC DRIVER 17 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2016_ODBC = 'ODBC DRIVER 13 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2012_ODBC = 'ODBC DRIVER 11 FOR SQL SERVER'; // DO NOT TRANSLATE
{$IFDEF POSIX}
  C_FreeTDS = 'FreeTDS'; // DO NOT TRANSLATE
{$ENDIF}
{$IFDEF MSWINDOWS}
  C_2012_NC = 'SQL SERVER NATIVE CLIENT 11.0'; // DO NOT TRANSLATE
{$ENDIF}
var
  DriverList : TStringList;
  WantedList : TArray<String>;
  Driver: string;
begin
  Result := ''; // Blank = Default

  WantedList := {$IFDEF MSWINDOWS}
                  {$IFDEF SQLNative}
                    [C_2012_NC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC]
                  {$ELSE}
                    [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_NC, C_2012_ODBC]
                 {$ENDIF}
               {$ENDIF}
               {$IFDEF POSIX}
                 [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC, C_FreeTDS]
               {$ENDIF};

  DriverList := TStringList.Create;
  try
    Link.GetDrivers(DriverList);

    DebugOut('Available SQL drivers'); // DO NOT TRANSLATE
    for Driver in DriverList
     do DebugOut(' "' + Driver + '"');

    for var Wanted in WantedList
     do for Driver in DriverList
      do begin
        if CompareText(Wanted , Driver) = 0
        then begin
          DebugOut('Selected driver: "' + Driver + '"'); // DO NOT TRANSLATE
          BestDriver := Driver;
          Exit(Driver);
        end;
      end;
  finally
    DriverList.Free;
  end;
end;

 

I was using ُSQL Native Client 11 until last week but But I have been using ODBC 18 recently 
But Result are same.
i used FDPhysMSSQLDriverLink and set 

ODBCDriver=ODBC Driver 18 for SQL Server

ODBC Advanced=TrustServerCertificate=yes

and set drivername of FDConnection to it.

 

The hanging problem is almost solved But sometimes the following error occurs

odbc driver 18 for sql server connection is busy with results for another command

 

or

 

cannot make a visible windows modal

 

---

In addition, the Mars option in MyFDConnection is set to TRUE

error.jpg

Edited by saeedbay

Share this post

Link to post

Anders Melander    1782

Anders Melander
Posted
44 minutes ago, saeedbay said:

odbc driver 18 for sql server connection is busy with results for another command

...

In addition, the Mars option is set to TRUE

You might want to search for info on that. You know, in case you aren't the first with that problem...

Share this post

Link to post

Lars Fosdal    1792

Lars Fosdal
Posted

@saeedbay Ref MARS - Don't explicitly set it to 'Yes'. Leave it to the driver default. 

  // Multiple Active Result Sets http://msdn.microsoft.com/en-us/library/ms131686.aspx
  if DisableMARS
   then Params.Values['MARS'] := 'No';

Do you use threads?

Do you call CoInitialize/CoUninitialize per thread?

Do you exec your queries in the same thread as you create them?

Do you reuse queries? Do you make sure to reuse them only in the thread they were created?

Do you reuse the queries that are already in use? - Don't.

Do you only do reads with the query? - Test if Query.FetchOptions.Unidirectional := True; gives you more speed.

 

1 hour ago, saeedbay said:

cannot make a visible windows modal

Is the form created in the .dpr? Don't do that.   

procedure TSomeParent.ShowTheForm;
begin
  var form := TYourForm.Create;
  // feed it with values if needed
  try
    if form.ShowModal = mrOK
    then ; // deal with the results if needed
  finally
    form.free;
  end
end;

If you want to have only one instance of the form at a time, deal with that, f.x. with critical sections or similar.

 

Do you use MadExcept or EurekaLog or similar? If not, you should - it really helps for pinpointing issues

Share this post

Link to post

saeedbay    0

saeedbay
Posted

hi again
I have not created any thread and I am using the default string.
maybe i resuse queries .but But first I close and clear them.
Also, keep in mind that this structure was already working correctly with ADO,

I changed the ADO Components to FD  just to get a better and faster build.
alse my quries rows has read and edit.
Thank you for your time to solve this problem
MyDataMudule is following pic.

error2.jpg

  • Sad 1

Share this post

Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Go To Topic Listing Databases
×