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.
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.
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.