search

FirebirdSQL / NETProvider

Firebird ADO.NET Data Provider
https://www.firebirdsql.org/en/net-provider/
Other
152 stars 63 forks source link

Update FbDataReader.cs GetSchemaTable #1114

Closed BFuerchau closed 1 year ago

BFuerchau commented 1 year ago

Modified GetSchemaTable/Async to a single request to the FB-Server. At the moment, in a loop is made a request for each field. If no transaction is specified, additional internal transaction is created. So if you have N fields in the result, you made N requests to the server, perhaps including many transactions. If a query is made with derived tables, the relation of field get empty, but the request is done. So you can be faster with a single call and single transaction to the server and load all fields of involved relations. The FetchSize can increase to the max to get all at once. The final build for the SchemaTable can be done within one loop. Also GetSchemaTable/GetSchemaTableAsync can share the code for building the schema. And real, the GetSchemaTable for 100 columns is less then 10ms. Also the modifikation of "IsKey/IsUnique" depends on the count of relations. But if you have no relation in a field or more then one relation is used, the IsKey/IsUnique should be false. Furthermore, if you have not all primary or unique fields of the relation in the result, IsKey/IsUnique should also be false. This can now be checked without additional requests.

cincuranet commented 1 year ago

This is replaced by #1115, #1116, #1117, right?

BFuerchau commented 1 year ago

Perhaps you can help me with combine #1115 and #1116 ?

cincuranet commented 1 year ago

Close these and create new PR.

BFuerchau commented 1 year ago

Additional discussion for GetSchemaTable and concerning the IsKey and IsUnique. In the moment, it is only checked, if a field is in a primary key or is in a unique key. We have compound primary and unique keys. So if in the resulttable not all fields are selected which included in the primary, the result should not set IsKey. This is also for IsUnique, because each IsUnique can be in another index. So only if one unique key exists, all selected fields which belongs to these index, can be set to IsUnique.

For example: We have a primary key for the Identity column, but also two additional unique keys. E.g. Identity + CreationDate, "Company" + "Plant" + "Customer". And many combinations more. So only the developer of an App have knowlegde which unique key is used for query/update.

BFuerchau commented 1 year ago

Sorry, i didn't find a solution, to combine the two commits to one pull request. https://github.com/FirebirdSQL/NETProvider/commit/03c7434ede2add1fcdeae3c74eecd314d9cad7bb https://github.com/FirebirdSQL/NETProvider/commit/46a311c18af0a03a02e55d66cbc53becb8573420 May you help me or give me some hints?

cincuranet commented 1 year ago

You need to have them in same branch.

BFuerchau commented 1 year ago

I will try it complete new.