SQL Reports

All Things Being SQL – SQL Reports and SQL Reporting Resources

How to Turbo-Charge your SQL Report Speed

I have seen just about anything & everything someone can do with SQL report, document generation, dashboards, etc. And from this perspective I’ve learned a number of basic do’s and dont’s that I think hold across all reporting, docgen, & dashboard systems.

Report Design

  1. If nothing else follow this rule – do not fight your SQL report designer. If you use a system that assumes banded reports (Crystal, SSRS, etc.) then create a banded report. If you try to work at cross-purposes you will find it difficult, frustrating, and the final design will be a series of compromises.
  2. Use the appropriate approach. For example, people designing spreadsheets using Word tables and designing documents with a couple of tables in Excel will find that an exercise in frustration. When they switch from Word to Excel (or vice-versa), then something that was hard becomes very easy.
  3. Understand and accept the limitations of your designer. For example, SSRS treats sub-reports as separate reports so you cannot render a sub-report in the context of the report pulling it in. You need to keep this in mind when architecting your template structure.
  4. K.I.S.S. – Keep It Simple (Stupid). I’ve had numerous cases where, when diving into the issues a customer had, the core issue was they were not clear about what information they were trying to get across, and what data represented that information. Try to be very clear on what you are presenting and what that information is.
    1. Pie charts are evil.

System Architecture

  1. If the reporting engine runs in the context of your application, create multiple threads for multiple simultaneous report requests. SQL Report generation tends to be heavily I/O bound so you get a big win here. Our rule of thumb is have twice as many threads as you have cores on your system. (And Hyper-threading counts as distinct cores for this measure.)
  2. Buy sufficient memory. If you are generating a 10,000 page report, you need more than ½Gig.
  3. Watch your dependence on network I/O. If you are pulling in images or sub-reports over the network, that network connection can easily be the gating factor in your speed.
  4. Run a profiler against your system under load. Generally the largest time hit will be a surprise, often one you can address.

Data Access

  1. Only pull in the data you need. A SQL select should almost never start with “select *…” because you rarely need every column. Select only the columns you need to display in the report.
    1. You do not need to select columns you use in your joins, order by, or where criteria (I see this a lot). You may use columns conditionally in the select without returning them.
  2. For a complex join, add it to the database as a view if possible. If that cannot be done, create it as a dataset. A database is optimized to handle complex joins as a view and most reporting systems will build a dataset as a temporary view.
  3. For XML data, minimize the size of the XML dataset. Using XPath or XQuery requires that the entire XML structure be read in to memory as a DOM or XPath optimized structure. A larger dataset means larger memory usage and more nodes to traverse on a query. (We’ve found several hundred megabytes is usually fast, but when you get into gigabytes it starts to have an impact.)
  4. Don’t pull data twice. If you are iterating through rows of data (or nodes in XML), then for each iteration pull all the needed data for that row/node in the main iterator and then access it directly. It should be rare that you have a select statement inside an iterative loop.
  5. For SQL report make sure all columns used for joins and the key parts of a sort or where clause are indexed. (Most people will say all, and disk space is cheap. But if you always sort by last_name, first_name then while last_name must be indexed, first_name should be indexed if space allows. Life is trade-offs.)
  6. For XML create a schema with data typing. This avoids ambiguity where XPath/XQuery not only have to make assumptions, but they have to do extra processing determining what they should assume.

Vendor Best Practices

  1. BIRT Best Practices (more why you need a 3 day class to learn all of them)
  2. Crystal Reports Best Practices
  3. Microsoft Sql Server Best Practices
  4. Oracle B.I. Publisher Best Practices
  5. Windward Reports Best Practices
  6. General tips on designing a report

7 comments

  • lester co on March 25, 2011 at 3:58 pm said:

    Reply

    sql database is very reliable and easy to use. it is open source so it is free. sql is very applicable when using xml language.

  • for now I am working system in place to use sql and reportnya using crystal bother, I faced problems when it reaches 100 page report, the system often does not work, this method may be solved my problem, I’ll try it. Greetings!

  • mtrguanlao on March 25, 2011 at 8:39 pm said:

    Reply

    I like the meaning of KISS in this article,it may sound funny but it’s very much true! Simple but clear is more understandable and easy to present.

  • Jerry Palmer on March 26, 2011 at 2:02 am said:

    Reply

    SQL reports is a very important tools for database programming and these tips to turbo charge the SQL report speed will help a lot especially in a case where a huge database is involved.

  • Sql is a superlyk database…i used it many times and it’s just too good with good results..i luved using it…n moreover the upper post also so informative..i got much information regarding it…

  • Gabriel Madison on March 26, 2011 at 1:52 pm said:

    Reply

    SQL is a very good system for reports! This article only helps me now with even FASTER report speed/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>