[sims] Designing A Report to Run Quickly / Effiecently - Info' to share with you

[bwfc_nottingham]

Hi All

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)

AND

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)

AND

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 http://support.capitaes.co.uk/newforum/images/smiley1.gif

 

Regards

 

Phil

[MattMitchell]

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.

[bwfc_nottingham]

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.

 

Matt thanks for the reply, I don't have access to the DBMS as our servers are managed by Capita and locked down ! I would love a peek inside the database though

[vikpaw]

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?)

I'd put money on that the intermediate step involves an Excel file or a CSV

 

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

 

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

[bwfc_nottingham]

I'd put money on that the intermediate step involves an Excel file or a CSV

 

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

 

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

 

Vikpa, is there any chance of you sending me the output of the SQL .

 

Our DBMS severs are locked down and I have no access.

 

I will treat the data with respect d with confidentiality.

 

I understand if you are unwilling to do so.

 

Many Thanks

 

Phil

[vikpaw]

Emailing you about it