FDO-Provider connection to SQL Server slow

[tm4FVE8]

Hi!

Our customer uses two programs (ACAD 2019 Map 3D and QGis) which display Spatial data stored in an MS SQL Server. In both programs he accesses the database through some connection provider (in ACAD it is the FDO-Provider) and loads Spatial data (and its metadata) from different SQL tables to a Layer each.

Now QGis is much more efficient doing this than ACAD (e.g, if QGis displays the data of one of these tables it tooks it less than 10sec. ACAD needs 52secs for the same data). The two softwares run on the same computer and we can exclude network problems (the problem is persistent on every time of the day on all PCs which have ACAD installed, even freshly installed, and a network specialist was in the office and checked the network performance which was no problem at all). The same problem occurs of course, if the layer was refreshed.

Now I asked myself what ACADs loading mechanism for Spacial data is like. Important to know is that for some reason the Spatial data are not stored as Geography data type but as Geometry data type. Furthermore, there are lots of SQL tables containing lots of Spatial data but only few tables must be accessed by ACAD (or QGis). Does (for some reason unknown to me) ACAD evaluate all accessible tables if one layer is refreshed (I got the idea because after establishing connection ACAD also tries to determine the coordinate system of each table which fails since there are only Geometry data but takes some time)? Does the problem maybe come from using Geometry data types? Is it not good practice to have a collections of data of one type (e.g., parcels) in one single SQL table (which are some 30.000 pieces) and read it from the database to a single layer in ACAD? If so, how do I divide it up or filter it in advance?

Maybe, you see from my questions, I'm by far not an ACAD (power-)user. So excuse me, if some of these questions sound silly to you. I'm concerned with these ACAD problem only indirectly since I'm supposed to work with the Spatial data "on the other side" of the SQL Server in a software (but my customer also confronts me with these issues). And please, don't blame me for setting up the wrong Spatial data model since this is an old database, I'm just taking over 😉

Any hint or idea which points me in the right direction would help and is much appreciated!

Best regards!