We have built a comprehensive set of Reports using the 'Design Report' functionality within SIMS. We've done some clever things with excel templates and macros.
I did stumble on something yesterday which I think is worth sharing and may help you.
I had a request to build a report which just reported certain achievements types for all pupils over the academic year.
The reason for the request was to support 'rewards evening' and we wanted to identify the pupils who had gained most points in a subset of achievement types.
So the Report was easy to produce
Report Student with Achievements as a sub report i.e.
Students ----> Achievements (filtering Achievements with Type and Date Range
When I first built the report my filter read :
where date between date1 and date2 (selected at runtime)
Type is one of A /B / C/ etc (selected at runtime)
When I ran the report for the whole year it just hung and hung and would not run.
I then thought back to my days (not too long ago), when I was a Business Analyst / Designer and decided that 'Achievement Type' must be an index on the relationship between Student and Achievement and perhaps the Report Design package might generate more efficient SQL for the SQL Server database if I put Achievement Type type first in my query i.e.
where Type is one of A /B / C/ etc (selected at runtime)
date between date1 and date2 (selected at runtime)
Well as Holly or Cilla would say "surprise surprise" the report ran in a couple of minutes rather than just hanging there.
The morale of the story / posting. Well I think we need to think carefully as to the order of subreport filter parameters. I only thought of reversing the params, because if I was designing a database I would probably have made Achievement Type an index for the child record , so I thought maybe the SIMS designers had done so too (actually I would probably have made date a param too).
Actually it might be worthwhile knowing what the indexes are for various entities in the report designer as this would influence the way we structured our reports.
In fact is there any documentation out there that does this i.e. documents the indexes, unique keys etc?
Anyway, I thought I would share this with you as knowledge shared is what the forum is all about and I have gained quite a bit over the last 18 months from the kind people in the user forum
That's extremely useful information Phil!
I'd always assumed that on the backend of the reporting engine, SQL was generated; you'd expect SQL server to optimise for this kind of thing automatically. Maybe there's actually some kind of intermediate step, where temporary tables are built and then filtered (either by deleting records, or by copying into a new temp table?)
As far as indexing goes, if you pull out a dump of the SQL database you can see what those are (or just look at the database itself).
Indexing dates can be a bit nasty in SQL server unless you're canny about it, so I can see that the date field might not be indexed. On the other hand, it might just be that the filter on achievement type cut out more records earlier on. Weirdly, I've found on some reports that adding a filter can slow things down! Some "flat" tables are actually views pulling multiple tables together (e.g. student <-> student/achievement relation <-> achievements <-> achievement types ), so being able to miss out certain columns can help with speed too.
This is useful to look at indexes - there are a number of variants, but this gives a decent list. If you remove the last where condition there are some 1600.
I wouldn't want to publish as it's giving away a lot of info on the tables / names etc. Not really for public or competitors to see
Code:SELECT TableName = t.name, IndexName = ind.name, IndexId = ind.index_id, ColumnId = ic.index_column_id, ColumnName = col.name FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE ind.is_primary_key = 0 --AND ind.is_unique = 0 --AND ind.is_unique_constraint = 0 AND t.is_ms_shipped = 0 AND t.name like '%achieve%' ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id
Emailing you about it
There are currently 1 users browsing this thread. (0 members and 1 guests)