Speeding Up SIMS Reports

[BFCIT]

Hi all,

 

I have a couple of questions regarding SIMS reports, the nature of how they are run and what we can do to speed them up.

 

We currently upgraded from SQL 2012 Express to SQL 2012 standard - in the hope that this would allow SQL Server to utilise all processing power and RAM that the server had to offer and speed up things. It appears to have made no difference.

 

Server Spec: Window Server 2012 64bit, 16GB RAM, Intel Xeon E5-2609 2.4GHz

 

When we run a SIMS report is all the data processing performed on the Server or is some of it carried out on the host machine? We have reports that can take up to two hours, has anybody else experienced such slow report running times if so what steps have you taken to speed up things?

 

Please advise, I look forward to hearing your responses.

[LosOjos]

First off, are we talking Reporting Engine (Reports > Run Report) or Individual Reports (Focus > Assessment > Individual Report)?

 

There are a few 'gotchas' with both that can help speed things up a lot, but they're very different kettles of fish...

[PhilNeal]

Reports shouldn't take anything like that time to run.

I would take a case with us. It could be an environmental issue or we may need to examine your data.

[BFCIT]

LosOjos we are using the Reports > Run Report option. I would be interested to learn more about these 'gotchas' if it will help speed things up. Thanks for your reply.

 

Phil - Thanks for your comment - we have had reports in the past take up to one hour or more, but recently some can take two hours and access by other staff is being affected during times when reports are being run. Can I provide you or support with further details so this can get looked into?

 

[3s-gtech]

Is the SIMS server sitting on bare metal? I'd say that disk speed and configuration can make a difference, but you have plenty of RAM and it should never be that slow. We had SIMS hosted on a temporary VM while we moved it to our current server, it was desperately under specced but still faster than that.

[CyberDrac]

Didn't I see something about a little tweak involving SQL 2012 when it was originally becoming required?

I'll have a hunt through, see if I can find it.

 

edit : it was SQL 2014 not 2012, so please disregard

Edited November 5, 2015 by CyberDrac

[LosOjos]

OK, using those reports, the big thing to be careful with is subreports (the 'related' items in the report designer).

 

The more subreports you use, the slower your report will get. Makes sense as (I assume) you're asking SIMS to perform a join on multiple tables. Nesting a subreport within a subreport seems to exponentially increase the time the report will take to run also.

 

If you need to filter based on data in a subreport, make sure you do so on the subreport itself. I'm talking about clicking the 'related item' in the right hand side of report designer, then using the 'define settings' window to set your filters. I've seen people make the mistake of adding a subreport (say, 'Classes' to a 'Student' report), then using the reports 'Filter Students' screen to filter the 'Classes' which makes things much slower. Thinking about this from a technical standpoint, I assume what's happening is SIMS is essentially performing the same join twice, once to gather all the matching data from 'Classes', then again to remove entries that don't match the filter. Adding the filters to the subreport itself would (theoretically, I don't really know what SIMS is doing 'under the hood'!) mean SIMS can form a SQL query to filter out irrelevant entries in the first join, saving a lot of time.

 

I'm not great with words, so here are a couple of screenshots to demonstrate what I mean. The first screenshot is how you should filter a subreport, the second is one to avoid (unless you only need to filter on the subreport's values and don't actually need to retrieve those values; that way you're still only performing the join once):

 

 

Both methods achieve the same outcome, but the first is much quicker.

 

 

The other big problem causing slowdown is simply volume of data. Make sure your reports are only pulling the data you need. If they're still slow, can you possibly break it down and make multiple runs? OK, it means more work for you but it can really bring the run time down and stop SIMS crawling for everyone else in school on particularly large reports.

 

For instance, if you're pulling assessment data from SIMS, are you filtering it to only retrieve the aspects you want? Could you run it a year group or even a reg group at a time?

 

 

There may well be some technical issue that Capita can sort for you, but these are some things you can always keep an eye on to get your reports moving as quickly as possible.

[BFCIT]

Thanks for that LosOjos, I Will get back to you with some further info as soon as I get chance, probably tomorrow.

[bobsmith]

Of course the fun part with every management system I've ever used is how slow the reports are compared to some carefully written SQL that does the same job. Bearing in mind how little data we're really talking about compared to how much SQL can handle...

 

Alas using SIMS.Net and the HUGE caveats around playing directly in the SQL DB it looks like I won't be writing SQL queries again anytime soon.

[PhilNeal]

@BFCIT please raise a case with us via your support provider.

[BFCIT]

Hi LosoJos, Please see the attached pics:

 

Structure shows the 4 filters used to collect data for each of 12 subjects so there are 48 filters after the generic stuff at the top.

Filter and Filter 2 images show the type of filter in use - not too complex.

There are also ten further filters at the end which are simply collecting the data held in different aspects but without result sets in use.

A grand total of 65 rows in the report.

 

Thank you

 

[LosOjos]

There's your problem - 4 subreports, 3 of which are based on Assessment - that is never going to be a quick report! just re-read; 10 more based on assessment! 2 hours sounds pretty quick to be honest!

 

It's a bit more manipulation on your part I know, but the way I'd tackle that would be to make one report which has an Assessment subreport; that subreport will return all the assessment data that you need, plus the aspect name.

 

Then I'd have a second report that returns your classes data.

 

In both reports, add a 'Person_id' column (you need to be in the Third Party Reporting permissions group for that) - now you have a guaranteed unique ID to tie the two reports together in Excel.

 

With your assessment report, create a pivot table that has all your student columns as rows and your aspect name as columns - this will give you the layout you currently have. Copy/Paste values so you can manipulate further. Now add a column for your class. Finally, use INDEX/MATCH (or VLOOKUP if you prefer) to pull in the assessment data and class name from your two original reports in to your new table.

 

As I said, it does mean you probably need to spend 10 minutes manipulating the data to get the format you want (probably longer until you get the hang of it), but SIMS will produce the data much more quickly resulting in a net gain in time spent

 

EDIT: also, please do take @PhilNeal's advice and log a case, as there may be a technical issue Capita can help you with (in case you didn't realise, Phil is a good person to listen to where SIMS is concerned )

Edited November 5, 2015 by LosOjos

[BFCIT]

LosOJos, thank you so much for the efforts you have gone to in this. We are trying to automate the process as much as possible but I perfectly understand and accept the points you are making and appreciate you explaining the alternatives that we could have a go at. We will raise a call with Capita Phil just in case there is any technical issues causing this slow reporting.

 

Thanks a bunch,

BFCIT