+ Post New Thread
Results 1 to 6 of 6
MIS Systems Thread, Designing A Report to Run Quickly / Effiecently - Info' to share with you in Technical; Hi All We have built a comprehensive set of Reports using the 'Design Report' functionality within SIMS. We've done some ...
  1. #1

    Join Date
    Apr 2013
    Location
    Nottingham
    Posts
    25
    Thank Post
    11
    Thanked 6 Times in 2 Posts
    Rep Power
    4

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

    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

    Regards

    Phil

  2. 5 Thanks to bwfc_nottingham:

    LosOjos (24th June 2014), Marci (25th June 2014), MattMitchell (24th June 2014), tombry (24th June 2014), vikpaw (25th June 2014)

  3. #2

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    254
    Thank Post
    23
    Thanked 38 Times in 29 Posts
    Rep Power
    20
    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.

  4. #3

    Join Date
    Apr 2013
    Location
    Nottingham
    Posts
    25
    Thank Post
    11
    Thanked 6 Times in 2 Posts
    Rep Power
    4
    Quote Originally Posted by MattMitchell View Post
    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

  5. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,635
    Thank Post
    683
    Thanked 1,399 Times in 1,159 Posts
    Rep Power
    353
    Quote Originally Posted by MattMitchell View Post
    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

    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

  6. #5

    Join Date
    Apr 2013
    Location
    Nottingham
    Posts
    25
    Thank Post
    11
    Thanked 6 Times in 2 Posts
    Rep Power
    4
    Quote Originally Posted by vikpaw View Post
    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

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

  7. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,635
    Thank Post
    683
    Thanked 1,399 Times in 1,159 Posts
    Rep Power
    353
    Emailing you about it

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 14
    Last Post: 26th June 2013, 03:50 AM
  2. Replies: 3
    Last Post: 21st March 2012, 12:10 PM
  3. Replies: 0
    Last Post: 13th May 2008, 06:46 PM
  4. Replies: 4
    Last Post: 7th March 2007, 03:37 PM
  5. sims times out when trying to run various reports
    By projector1 in forum MIS Systems
    Replies: 2
    Last Post: 5th July 2006, 12:54 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •