Toad World® Forums

Connecting to PeopleSoft running on Sql Server

Is there a way to speed up my connection to PeopleSoft. It takes just over 30 minutes to connect. All other Sql Server databases take no time at all. I am guessing it has to do with the number of tables and views in PeopleSoft, but even with a filter that should return 1 table it takes 30+ minutes.

i would suggest adding a filter to only show what databases and tables you work with most often.

Thanks for the suggestion Debbie, but you can’t apply the filer until after you connect to the database. I will have double digit end users accessing this database and if they have to wait 30 minutes each time they connect it will be unacceptable.

filters are stored in a file and could be given to other users. They are stored in the path below.

C:\Users%username%\AppData\Roaming\Quest Software\Toad Data Point 4.5\Context\Filter

Can you give me general size of this PeopleSoft DB. Number of databases? Number of tables in the database that you connect to most often. I never experienced any performance issues when connecting to SQL Server. It is normally a very fast database.

Another idea --> If you are using TDP 4.3, try connecting using ODBC. For this connector only, we cache the database metadata to disk and retain this beyond Toad Sessions. This would make huge difference in performance AFTER you take that first 30 minute hit. When we connect and you have the object explorer or navigation manager open we query the database for the objects and cache the metadata. That is why you only get the huge delay when you first connect. But for native providers we delete this cache after you disconnect. We added to ODBC to persist the cache. Try that out and see if you like this better. You would loose some of the SQL Server specific features but that might be a better trade-off for you.

Hi Debbie,

I think this message was for someone else.

Thank you for looking into the highlighting issue.

S

From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]

Sent: Thursday, January 18, 2018 11:45 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Connecting to PeopleSoft running on Sql Server

RE: Connecting to PeopleSoft running on Sql Server

Reply by Debbie Peabody

filters are stored in a file and could be given to other users. They are stored in the path below.

C:\Users%username%\AppData\Roaming\Quest Software\Toad Data Point 4.5\Context\Filter

Can you give me general size of this PeopleSoft DB. Number of databases? Number of tables in the database that you connect to most often. I never experienced any performance issues when connecting to SQL Server. It is normally a very fast database.

Another idea --> If you are using TDP 4.3, try connecting using ODBC. For this connector only, we cache the database metadata to disk and retain this beyond Toad Sessions. This would make huge difference in performance AFTER you take that first 30 minute hit. When we connect and you have the object explorer or navigation manager open we query the database for the objects and cache the metadata. That is why you only get the huge delay when you first connect. But for native providers we delete this cache after you disconnect. We added to ODBC to persist the cache. Try that out and see if you like this better. You would loose some of the SQL Server specific features but that might be a better trade-off for you.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

Click here for Anadarko’s Electronic Mail Disclaimer

I agree with you that TDP is very quick with all of my Sql Server databases, except for PeopleSoft. With the ODBC connection it connects quickly and even with a filter on tables as soon as I click on the tables it hangs for 30 minutes. The PeopleSoft database is 480GB and contains 71,312 tables in production. Below is what one of my DBAs found on the query that is executing during the 30 minute connection time.

From: Gene

Sent: Friday, December 22, 2017 10:11 AM

To: Jim

Subject: RE: Background query running after fs92dev connection

GM Jim,

If you comment out/take out piece of code that calculates [RowCount] column in SELECT list (see highlighted code below) the query executes in 5 seconds! It kind of understandable, since for each returned row in main query (71,312 rows for this db) it is executing another correlated SELECT, which in its turn being executed 71,312 times!

So it is not that our db is not tuned up, but rather the way this query is written. As I said they probably debugged it on much smaller dbs, so it was not the issue for them.

So, I am afraid, opening up a ticket with them is the only option. And you can point out to them that column [RowCount] as culprit.

Thanks,

Gene

From: Jim.

Sent: Friday, December 22, 2017 9:34 AM

To: Gene

Subject: RE: Background query running after fs92dev connection

Here it is.

Thanks, Jim

SELECT

obj.name COLLATE database_default AS [Name],

sch.name COLLATE database_default AS [Owner],

CASE

WHEN obj.is_ms_shipped = 1

THEN 'S'

ELSE obj.type

END as [Type],

ISNULL(obj.is_filetable, 0) AS [IsFileTable],

CAST(CASE

     WHEN dsidx.type = 'PS'

     THEN 1

     ELSE 0

   END AS bit) AS [Partitioned],

ISNULL(obj.is_memory_optimized, 0) AS [IsMemoryTable]

,obj.create_date

, (select count(name) from sys.columns cols where cols.[object_id] = obj.[object_id]) AS [ColumnCount]

, CASE WHEN EXISTS (SELECT 1 FROM fn_my_permissions ('sys.dm_db_index_usage_stats', 'object') WHERE permission_name LIKE '%SELECT%')

          THEN (SELECT stat.last_user_update from sys.dm_db_index_usage_stats stat

                where stat.[object_id] = idx.[object_id] and stat.index_id = idx.index_id and stat.database_id = DB_ID() )

                ELSE null END as [Last Updated], obj.modify_date

, case

                                                                                               when(obj.is_memory_optimized = 1) then

                                                                                             ISNULL((SELECT SUM(spart.row_insert_attempts) - SUM(spart.row_delete_attempts)

                                                                                                     FROM sys.dm_db_xtp_object_stats spart

                                                                                                     WHERE(spart.object_id = obj.object_id)), 0)

                                                                                            else

                                                                                            ISNULL((SELECT SUM(spart.rows)

                                                                                                     FROM sys.partitions spart

                                                                                                     WHERE(spart.object_id = obj.object_id) AND

                                                                                                           (spart.index_id < 2)), 0)

                                                                                            END as [RowCount] 

            ,ISNULL(eprop.[value], '')

FROM

sys.tables obj

INNER JOIN sys.schemas sch ON sch.schema_id = obj.schema_id

INNER JOIN sys.indexes idx ON (idx.object_id = obj.object_id) AND

                              (idx.index_id < 2 or idx.type = 7)

LEFT OUTER JOIN sys.data_spaces dsidx ON dsidx.data_space_id = idx.data_space_id

 LEFT OUTER JOIN sys.extended_properties eprop on obj.[object_id] = eprop.major_id and eprop.[name] COLLATE database_default = 'MS_Description' AND eprop.minor_id = 0

WHERE

1 = 1

AND ((obj.is_ms_shipped = 0))

ORDER BY

sch.name COLLATE database_default ASC, obj.name COLLATE database_default A

From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]

Sent: Thursday, January 18, 2018 1:45 PM

To: toaddatapoint@toadworld.com

Subject: [E!] - RE: [Toad Data Point - Discussion Forum] Connecting to PeopleSoft running on Sql Server

RE: Connecting to PeopleSoft running on Sql Server

Reply by Debbie Peabody

filters are stored in a file and could be given to other users. They are stored in the path below.

C:\Users%username%\AppData\Roaming\Quest Software\Toad Data Point 4.5\Context\Filter

Can you give me general size of this PeopleSoft DB. Number of databases? Number of tables in the database that you connect to most often. I never experienced any performance issues when connecting to SQL Server. It is normally a very fast database.

Another idea --> If you are using TDP 4.3, try connecting using ODBC. For this connector only, we cache the database metadata to disk and retain this beyond Toad Sessions. This would make huge difference in performance AFTER you take that first 30 minute hit. When we connect and you have the object explorer or navigation manager open we query the database for the objects and cache the metadata. That is why you only get the huge delay when you first connect. But for native providers we delete this cache after you disconnect. We added to ODBC to persist the cache. Try that out and see if you like this better. You would loose some of the SQL Server specific features but that might be a better trade-off for you.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

The PeopleSoft database is 480GB and contains 71,312 tables in production.

I agree with you that TDP is very quick with all of my Sql Server databases, except for PeopleSoft. With the ODBC connection it connects quickly and even with a filter on tables as soon as I click on the tables it hangs for 30 minutes. Below is what one of my DBAs found on the query that is executing during the 30 minute connection time. I cannot include the SQL code in the post but an more than willing to email it.

If you comment out/take out the piece of code that calculates [RowCount] column in SELECT list, the query executes in 5 seconds. It is kind of understandable, since for each returned row in main query (71,312 rows for this db) it is executing another correlated SELECT, which in its turn being executed 71,312 times.

Yes please, sent SQL code to debbie.peabody@quest.com