0

We have a product that runs Sql Server Express 2005 and uses mainly ASP.NET. The database has around 200 tables, with a few (4 or 5) that can grow from 300 to 5000 rows per day and keep a history of 5 years, so they can grow to have 10 million rows.
We have built a reporting platform, that allows customers to build reports based on templates, fields and filters.
We face performance problems almost since the beginning, we try to keep reports display under 10 seconds but some of them go up to 25 seconds (specially on those customers with long history).
We keep checking indexes and trying to improve the queries but we get the feeling that there's only so much we can do. Off course the fact that the queries are generated dynamically doesn't help with the optimization. We also added a few tables that keep redundant data, but then we have the added problem of maintaining this data up to date, and also Sql Express has a limit on the size of databases.
We are now facing a point where we have to decide if we want to give up real time reports, or maybe cut the history to be able to have better performance.
I would like to ask what is the recommended approach for this kind of systems.
Also, should we start looking for third party tools/platforms? I know OLAP can be an option but can we make it work on Sql Server Express, or at least with a license that is cheap enough to distribute to thousands of deployments?

Thanks

pauloya
  • 2,535
  • 3
  • 30
  • 50

2 Answers2

0

We face performance problems almost since the beginning

Before your tables got large? This makes me think that you have underlying problems in your reporting application or SQL queries. Do these wait times happen with only 1 user on the system?

Have you used SQL trace to document long running queries and fix them? How have you gone about adding indexes?

There is the open source OLAP suite - http://www.pentaho.com/index.php But I cannot vouch for its ease of use or performance.

Sam
  • 7,543
  • 7
  • 48
  • 62
  • Yes, out application is mostly accessed by 1 person. We worked several time on query improvement, analyzing the execution plan and adding indexes. We have improved many queries, but for some reports it seems impossible to go down to 1 second queries. – pauloya Mar 30 '10 at 17:37
  • To be able to build the queries dynamically we use views that gather the fields may they come directly from tables or from transformations of data trough calculations or grouping. I guess this prevents full flexibility on the query building, but even if we try to build a different query manually to achieve the same result it doesn't seem like performance get's much better. – pauloya Mar 30 '10 at 17:41
  • Our tables were large since the beginning though, because data was migrated from a previous system. – pauloya Mar 30 '10 at 17:52
  • We are currently working on query optimizations and things are getting faster, either by changing the queries or adding indexes. But still I get the feeling that this approach is difficult and expensive (in time and resources), I wonder how easier and faster things would if we had more of a OLAP like system in place. – pauloya Apr 07 '10 at 13:34
  • OLAP will be a whole other initiative, even setting up a simple one will take quite a bit of time - but maybe it is better in the long run. Have you explored options to reduce the number of joins happening? http://technet.microsoft.com/en-us/library/cc917715.aspx . This is what Dave Swersky is referring to in theory - the indexed view might let you add this without actually adding denormalized tables. – Sam May 03 '11 at 16:50
  • Looks like indexed views are not available in express, so if you want to do such a thing, you'd need to do the joins and store the results in a table, which may be a large waste of space. – Sam May 03 '11 at 16:54
0

Query execution in a case like this can be improved by maintaining a denormalized version of your database. SQL Express does not offer much in the way of BI "out of the box," so your best option (within the constraints of SQL Express) is to design a solution manually.

This means designing a denormalized version of your database, to which you export data for reporting. Be aware that denormalized databases take up a lot more space. Here is a book that deals with designing data warehouses.

You should also look into architectural options that would centralize the data in a fully-featured RDBMS.

Dave Swersky
  • 34,502
  • 9
  • 78
  • 118