[sims] Individual Reports speed bears no relation to available resources/performance?

[pete]

Preface: I'm aware this is highly variable based on how the reports are created and how much data is being pulled, but the problem I'm noticing is as follows:

 

Testing hardware:

 

Client device is a server with 12 physical cores, 72GB of RAM and SSD disks

SIMS server is virtual with 4 cores allocated, 12GB of RAM and using SAN storage fronted by an SSD cache.

Both client and server are connected to the same physical switch.

Exported test files are being saved locally on the client.

 

When I run a report to test speeds the following happens:

 

1. Spike in SQL load of 15-32% for 16-30 seconds during the "extracting" message
   
2. Practically zero load (CPU, RAM, network, disk queue) on the client, server or network for 7 minutes while the reports are generated
   
3. No rhythm or reason as to why it takes 7 minutes because there's lots of capacity that could be used to speed it up
   

 

Running an Interim assessment report (1-pager, all subjects) for 104 pupils in Year 9 takes 7mins 40 secs dumped to .xml file or 12 mins 41 secs dumped to PDF (to simulate printing). SIMS SQL load spikes up to 30% during extraction, but thereafter the server and client are idling.

 

Running the Annual report for 104 pupils in Year 9 takes 6 minutes, 41 seconds (less tags) dumped to an .xml file or 12 mins 39 secs dumped to PDF (to simulate printing). SIMS SQL load spikes to 25% for 16 seconds during extraction, but thereafter the server and client are idling.

 

What sort of times does it take at your school. Is anyone else seeing similar behaviour?

Edited April 13, 2016 by pete

[Esteban_Child_of_the_Sun]

Due to the quality of your hardware your times are mere fractions of what some of our schools report. The longest I remember recently was a school saying a 3 page all subjects interim report for Y8 took just under 4 hours. We found some of the SQL waits were rather high for the period and are having Capita investigate but they seem to be struggling to understand the issue.

[pete]

The problem I'm seeing is that it's not clear where the time is being spent because the bulk of the SQL load appears to happen in the first 30 seconds and the hardware looks as though it's sitting idle.

 

As an additional data point:

 

The interim report has 135 tags to query for each student, so 14,040 queries across the 104 students.

The annual report had 144 tags to query for each student, so 14,976 queries across the 104 students, however the Name, Surname and Reg is pulled for each subject page and I'm assuming they're not being re-requested (and thus the report runs faster).