search

babelfish-for-postgresql / babelfish_extensions

Babelfish for PostgreSQL provides the capability for PostgreSQL to work with applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query and procedural language, so you don’t have to switch database drivers or rewrite all of your application queries.
https://babelfishpg.org/
Apache License 2.0
271 stars 92 forks source link

[Question]: SYS.VIEWERS IN SSMS AND APPLICATION LEVEL IS VERY SLOW COMPER TO MSSQL #1973

Open mlabarno opened 10 months ago

mlabarno commented 10 months ago

What happened?

Hello, so after migrate database schema and data to babelfish version 3.2 running in ubuntu 20.04 and optimize configs postgresql to memory buffer cache etc, and try to run selects in ssms connection or in application level using adodb or ado.net with sys.viewers like:

SELECT SYSCOLUMNS.NAME,SYSCOLUMNS.SCALE,SYSTYPES.Name as Tipo FROM SYSCOLUMNS, SYSOBJECTS , SYSTYPES WHERE SYSOBJECTS.name = 'TABLENAME' AND SYSCOLUMNS.ID = SYSOBJECTS.ID AND SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE ORDER BY SYSCOLUMNS.COLID or SELECT sys.columns.name, sys.columns.user_type_id FROM sys.columns INNER JOIN sys.objects ON sys.objects.object_id = sys.columns.object_id INNER JOIN sys.types ON sys.types.user_type_id = sys.columns.user_type_id WHERE sys.objects.name = 'TABLENAME' AND sys.types.name <> 'sysname' ORDER BY sys.columns.column_id

The response is VERY SLOW, more than 3 to 5 seconds to awnser in SSMS or Application using ADO.

So analyze execute plan in babelfish is very clear this execute of sys.viewers is very slow sorts etc.

Is there any option or solution without having to change the vb6 or .net application code a lot since it uses a lot of sys.viewers like those indicated above with adodb or ado.net?

I tried using pg_attribute similar to PostgreSQL, the performance really improves and is ideal, but the problem is that the adodb application via recordset does not accept this syntax.

I appreciate any help or information to fix this issue. thanks.

Version

BABEL_3_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

No response

Code of Conduct

Deepesh125 commented 10 months ago

Hi @mlabarno, Could you please share how many objects are available in the database (rough number would also work)? And what is your expectation in terms of performance?

mlabarno commented 10 months ago

Hello Dipesh.

Firstly, thank you for your response and I am available to help with whatever you need in this performance implementation, because we are interested in migrating several customer databases into mssql.

Summary: According to your question, below is a summary of objects in the test schema that we created for our poc in babelfish, I created another schema and also another user to be the downer of the database which we are using with adodb connections in VB6 and ado.net in C# .net framework and core.

Total objects migrated to the test schema in babelfish: 2130 Tables: 831 Indexes: 1182 Sequences: 117

Regarding the desired performance, example: the two selects that I sent in the initial email post, lead to ADODB.Recordset via ADODB.Connection using provider "SQLOLEDB.1" average of 10ms to 16ms, ideal for application. On ado.net you follow similar media. example: -- ssms connected in sql server 2019------------------------------------------------------------------------------------------ set statistics time on; SELECT syscolumns.name, syscolumns.xtype FROM syscolumns INNER JOIN sysobjects ON sysobjects.id = syscolumns.id INNER JOIN systypes ON systypes.xtype = syscolumns.xtype WHERE sysobjects.name = 'NameTable' AND systypes.name <> 'sysname' ORDER BY syscolumns.colid; set statistics time off;

Tempo de análise e compilação do SQL Server: Tempo de CPU = 0 ms, tempo decorrido = 0 ms.

(412 rows affected)

Tempos de Execução do SQL Server: Tempo de CPU = 16 ms, tempo decorrido = 10 ms.

-- ssms connected in babelfish 3.2

Already using the connection via ssms to babelfish port 1433, the same select above takes 3 to 4 seconds. See attached image as set statistics does not work in babelfish.

Just as the application has several requests for this in several tables, a registration process that in ADODB.Recordset it takes 3 to 5 seconds. So, to execute the same registration process via babelfish it takes between 1 minute 10 seconds and 1 minute 17 seconds. very slow to application. The same performance occurs in the vb6 and .net application.

Performance settings for babelfish were made, such as 4GB buffer cache, etc. REINDEX SCHEMA was also executed on the schema.

Thank you and I look forward to your response and information. Best Regards Marcelo Abarno.

Em ter., 7 de nov. de 2023 às 06:44, Dipesh Dhameliya < @.***> escreveu:

Hi @mlabarno https://github.com/mlabarno, Could you please share how many objects are available in the database (rough number would also work)? And what is your expectation in terms of performance?

— Reply to this email directly, view it on GitHub https://github.com/babelfish-for-postgresql/babelfish_extensions/issues/1973#issuecomment-1797907614, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALI5YW4EQN6EOOZACNMPEFTYDHKD3AVCNFSM6AAAAAA6ZWN7RGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOJXHEYDONRRGQ . You are receiving this because you were mentioned.Message ID: <babelfish-for-postgresql/babelfish_extensions/issues/1973/1797907614@ github.com>