March 8, 2012 at 6:43 am
Lynn Pettis (3/7/2012)
Am I wrong or is this a PeopleSoft database?
Good Eye!
March 8, 2012 at 7:23 am
Leeland (3/8/2012)
Lynn Pettis (3/7/2012)
Am I wrong or is this a PeopleSoft database?Good Eye!
I provided SQL Server support to a PeopleSoft development/support staff for 5 years. The PeopleSoft tools generate SQL code using the ANSI SQL-89 standard. Hated looking at that code as I always had to convert it to ANSI-92 for me to understand it better.
Also, any indexes (or other database objects for that matter) you add need to be added to the tools as enhancements or you have to maintain them separately from the application. That is why I worked closely with the PeopleSoft Administrator when we would identify index changes so that they were included. Made her job much easier during upgrades.
I will say that it helps to rewrite the code to SQL-92 standard. Just be sure to test the code to ensure you get the same results. Once you have that then work on tuning the query.
March 8, 2012 at 8:14 am
Lynn Pettis (3/8/2012)
Leeland (3/8/2012)
Lynn Pettis (3/7/2012)
Am I wrong or is this a PeopleSoft database?Good Eye!
I provided SQL Server support to a PeopleSoft development/support staff for 5 years. The PeopleSoft tools generate SQL code using the ANSI SQL-89 standard. Hated looking at that code as I always had to convert it to ANSI-92 for me to understand it better.
Also, any indexes (or other database objects for that matter) you add need to be added to the tools as enhancements or you have to maintain them separately from the application. That is why I worked closely with the PeopleSoft Administrator when we would identify index changes so that they were included. Made her job much easier during upgrades.
I will say that it helps to rewrite the code to SQL-92 standard. Just be sure to test the code to ensure you get the same results. Once you have that then work on tuning the query.
Hi Lynn,
I am kind of in the same boat except I am the inherited PeopleSoft Admin due to a change in the landscape (been learning for the past 3 years)...I kind of had to learn the job on the fly...initially we just supported the server (database and app).
What we inherited was a real mess of an install and are now moving to all new servers, de-customizing a BUNCH of things, and upgrading PeopleTools from 8.47 to 8.51.
On top of that the database was/is in the wrong collation so...we made the decision to port all the data out into a new database that is in the correct collation.
After 4 months of work and setting up everything they are now testing which is leading to finding and fixing poorly performing code.
I know what you mean as far as creating the objects with the app designer otherwise it will show up on the audit reports.
Once I get some other stuff out of the way today I am going to come back to that query to test some of the suggestions and I will hopefully be able to report good news.
Thanks all for the suggestions I will update the post with my results for reference.
Lee
March 9, 2012 at 12:59 pm
Just a thought, since you are getting degraded performance in a query that executes using parrallelism on a new server install. Have you checked the max degee of parallelism setting for the server?
March 9, 2012 at 1:12 pm
rick.gotner (3/9/2012)
Just a thought, since you are getting degraded performance in a query that executes using parrallelism on a new server install. Have you checked the max degee of parallelism setting for the server?
I did, for testing purposes, I ran it a couple of different times as is with a MAXDOP 1 and the performance was a 3 fold increase.
The process that this is part of is a 'delivered' process so they don't want to have to modify the delivered making it custom...I suppose it would have to happen if I can't get it figured out...
Other tasks for the upgrade took over this week so I haven't been able to get back to the query...I hope to dig into it early next week.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply