Categories
PeopleTools Performance SQL SQL Server

PeopleTools and Parallelism on SQL Server

During a recent discussion with another PS Admin running on SQL Server it became apparent that his efforts to improve application SQL performance through adding more CPUs (with the associated license costs) were based on a fundamentally wrong assumption:

PeopleTools/Application SQL will go parallel if needed.

This is simply not true. In fact, most SELECT SQL can never go parallel under PeopleTools on SQL server due to the fact that they are run through cursors and PeopleTools requests a cursor type of FAST_FORWARD. FAST_FORWARD cursors result in an execution plan with a NonParallelReason value of NoParallelFastForwardCursor.

Categories
Hints and Tips One-liners Oracle SQL

REGEXP Problem

A good friend asked me how to replace the last full stop (period) in a field with another character using a regular expression e.g. a comma. When I asked “what language?” it turned out he wanted to do this using REGEXP_REPLACE in Oracle SQL.

It’s a problem with a couple of solutions that I could immediately think of – one more “Oracle”, the other more “regular expression”.

First the Oracle solution:

REGEXP_REPLACE({FIELD},'\.',',',INSTR({FIELD},'.',-1))

This uses backward search capability of INSTR() to find position of the last ‘.’ in the field and return it so that the REGEXP_REPLACE only starts the replace from that point on-wards.

Now a “pure” regular expression version:

REGEXP_REPLACE({FIELD},'(\.)([^.]*$)',',\2')

This one is a bit more involved as it uses grouping and a negated character class ([^.] = “not a period”) as well as a back reference (\2).

 

Categories
Hints and Tips SQL SQL Server

CROSS/OUTER APPLY and Performance

One quite useful technique to be aware of is the use of CROSS or OUTER APPLY with an in-line SQL e.g.

 

1
2
3
4
5
6
7
8
9
INSERT INTO {TABLE}
 
SELECT A.{FIELDS},APP.{FIELDS}
 
FROM {TableA} A CROSS/OUTER APPLY (
 
SELECT {more FIELDS} FROM {TableB} B WHERE .. A.{some_field} = B.{some_field}
 
) APP

However, with CROSS APPLY, the SQL Server optimizer tends to change the logic into regular joins. Mostly this is a good thing, but not if you want to “force” the driving table to be {TableA}. This can offer a considerable performance boost in some circumstances.

What you can do to force this behaviour is to add TOP 1 to the inner SELECT – assuming you expect only one row back from the CROSS APPLY SQL. Or you ordered the SQL result set and *do* just want the first row e.g. maximum/minimum effective date.

Note: CROSS/OUTER APPLY are SQL Server specific syntax for LATERAL in Oracle and others.

Categories
Hints and Tips SQL

CRLF and “GO”

I frequently find myself writing SQL that generates SQL. On SQL Server, I like to build a string that contains the SQL statement followed by “GO”. To do that I use CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10) at the end of the string e.g.

{some_generated_SQL}+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)

That way, when I paste the query results into SSMS or a text editor I get:

1
2
3
4
5
{first_generated_SQL}
GO
{second_generated_SQL}
GO
...
Categories
Rants SQL

Respect SQL!

I am by no means an SQL guru – there are many people far more skilled in SQL than I am. But I like to think I write well structured and clean SQL that is maintainable and performs well. Sadly, all to often I find poorly thought out SQL written in hard to read styles and with little respect for the language and it’s power.

SQL is not easy to write well but it is easy to write. Unfortunately, many developers think it is easy and place minimal emphasis on improving their skills beyond what they first learned. In the PeopleSoft market in particular, I have met many developers who are extremely skilled in (say) PeopleCode but all too often their SQL is poorly constructed. I’d describe it as “baby SQL”.

I’ll write a longer article with some tips and style guides for SQL at some point, but the message here is:

Respect SQL, Learn it, Embrace it’s strengths and it will reward you.

Categories
Hints and Tips SQL SQL Server

Another SQL Tip – A “DATES” Table

A common requirement is to generate a list of the dates between two dates e.g. on a source row we have a FROM and TO date but we need to expand that into a row for each date. One way to do that on SQL Server is via a CTE:
 

---
--- A generated list of dates from 01-01-2013 until 25-01-2054
--- This would be a useful utility view/table in any PS system
--- Table is preferred - CLUSTERED INDEX ON PROCESS_DT provides
--- Important statistics on distribution AND allows for a CLUSTERED INDEX SEEK
--- in execution plan.
---
 
WITH [PROCESS_DATES] AS (
 
SELECT TOP 15000 CAST(DATEADD(DAY,ROW_NUMBER() OVER ( ORDER BY RECNAME),CONVERT(DATE, '2012-12-31')) AS DATE) AS PROCESS_DT,
DATEPART(weekday,DATEADD(DAY,ROW_NUMBER() OVER ( ORDER BY RECNAME),CONVERT(DATE, '2012-12-31'))) AS PROCESS_DT_WEEKDAY
FROM PSRECFIELDDB --- ANY TABLE WITH A NUMBER OF ROWS >= THE TOP VALUE ABOVE WILL DO
 
)

That works fine, but as the comments above indicate, if you are going to do this it is better to generate the list of values into an table, Apart from anything else, why incur the CPU time recalculating this list every time it is accessed – better as a one-off table populate. Note the use of a table known to have more that 15000 rows – this could be any table – even sys.all_objects provided it has enough rows. But if you are generating rows as a one-off you could cartesian (star join) tables to themselves to get enough rows. The 15000 is arbitrary – you could go further ahead.

Categories
Hints and Tips SQL SQL Server

A SQL Tip .. In-Line UNION for previous “n” Months

Here’s an interesting requirement:

“A list of Business Units with the period (YYYYMM) and the monthname (e.g. October) against each one for the prior 3 months”.

A simple problem, but here’s my solution:

SELECT SETCNTRLVALUE AS [BUSINESS_UNIT] , 
CONVERT(VARCHAR(6),PREVIOUS_MONTHS.MYDATE,112) AS [YYYYMM],
DATENAME(MONTH,PREVIOUS_MONTHS.MYDATE) AS [MONTH_NAME]
FROM PS_SET_CNTRL_TBL S,
( SELECT DATEADD(MONTH,-1,GETDATE()) AS [MYDATE]
UNION ALL
SELECT DATEADD(MONTH,-2,GETDATE()) 
UNION ALL
SELECT DATEADD(MONTH,-3,GETDATE()) 
) PREVIOUS_MONTHS
ORDER BY 1,2

Of course, the in-line inner UNION ALL could be created as a view if it was going to be reused, but it suffices for this example. Key points to note:

  1. The use of UNION ALL to eliminate the SORT you get with UNION – we know the values are unique but the optimizer doesn’t (it probably should though)!
  2. Perform the bare functions on the date value (CONVERT,DATENAME) in the outer loop rather than create multiple columns in the in-line SQL.
  3. Yes this is a cartesian product but that is perfectly acceptable when you know your data.
  4. This example is for the prior 3 months with reference to GETDATE() – extending to prior “n” months and a different reference date is trivial. But be careful what table you use to get the reference date). Typically you might want the “n” prior months before (say) the MAX() date on some transactional table – if so make sure you have an index to make finding that value as quick as possible.
  5. This is SQL server syntax – on Oracle you would have to SELECT from DUAL etc.

Enjoy.

 

 

 

Categories
HRMS Peoplesoft Performance SQL Tuning

HOLIDAY_SCHEDULE SQL

If you come across this as one of your top (if not THE top) SQLs in your HR system:

SELECT HOLIDAY_SCHEDULE, HOLIDAY, 
(CONVERT(CHAR(10),HOLIDAY,121)), DESCR 
 FROM PS_HOLIDAY_DATE 
WHERE HOLIDAY_SCHEDULE=@P1 
  AND HOLIDAY=@P2 
ORDER BY HOLIDAY_SCHEDULE, HOLIDAY

then look no further than the ABSW_WRK page which is a hidden page on most absence related components. There is a scroll showing the holiday dates. Unfortunately, some developer decided to put a related display in the grid, resulting in “n” executes of the above SQL – where “n” is the number of rows you have in PS_HOLIDAY_DATE for the given employees’s HOLIDAY_SCHEDULE.

In the case I came across there were some 200+ rows in the table and as a result after just 3 working days the above SQL had been executed 21 million times. This actually makes this more frequent than the PSVERSION check!

Personally, I’d remove the related display field from the scroll completely. It isn’t referenced nor visible so why take the overhead of 200+ SQL lookups?

Update: Further analysis revealed an issue with a cartesian product on a customized scroll based on the standard ABSW_WRK page. That was causing the majority of the 21 million lookups by filling the scroll with “n” times the 200+ rows in the holiday table. But my point is still very valid – why lookup something you never show the users? If you really need it – put it in the view used in the scroll so it comes “for free” with the population of the grid.

Categories
Hints and Tips SQL SQL Server

GETDATE()-1

Ever noticed some “odd” predicates in execution plans using GETDATE()-n where “n” is the number of days? Things like:

SOME_DATETIME_FIELD >= GETDATE()-‘1900-01-02 00:00:00.000’

It may look a bit odd but it does make sense where you think about the data type precedence rules in SQL Server. In this case, as GETDATE() is a DATETIME, there is an implicit conversion of the “n” into a DATETIME. So, if we run:

SELECT CAST(1 AS DATETIME)

what do we get? ‘1900-01-02 00:00:00.000’

And yes, “2” gives ‘1900-01-03 00:00:00.000’ as you might expect.

As the subtraction of the two dates is actually just using decimal subtraction, the GETDATE()-1 does work to give you “yesterday”. It’s just not quite as clear (in my view) as using:

DATEADD(day,-1,GETDATE())

Categories
Hints and Tips One-liners SQL

List of Alphabetic Values in SQL Server

I really like this use of the undocumented but widely used master.dbo.spt_values table to give a list of characters e.g. a-z or A-Z:

SELECT CHAR(NUMBER) FROM master.dbo.spt_values
WHERE NUMBER BETWEEN 97 AND 122 --- Lowercase a-z 
--WHERE number between 65 and 90 --- Uppercase A-Z

Other ranges such as “number between 48 and 57” are also useful (that gives 0 through 9).

There are of course other techniques to address this requirement such as creating a permanent table of the numbers/letters required. I’m not advocating the above over others – I just like the “neatness” of the above approach. Perhaps using sys.all_objects or some other documented table/view would be safer – although I doubt microsoft will remove master.dbo.spt_values any time soon. Here’s another approach you might prefer:

SELECT TOP 26 
CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+64) AS upper_case,
CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+96) AS lower_case
FROM sys.all_objects