> I have a very very strange situation with a particular application and sql
> server 2005 enterprise.
>
> This application combines numerical data from multiple tables. User can
> make query over this kind of tables, can build queries with "group by"
> "order by" and "join", "sum, count(*) ecc. on many many columns.
>
> I cannot know the query that application is going to build, so I do not
> know how to create indexes.
> I cant make indexes on all the columns of course, so I'm creating some
> index over columns that should be statistically used in the join, but
> when there is a group by on a column chosen from the user, I realize
> that the plan become non efficient with the famigerate "TABLE SCAN".
>
> Can somebody, give me an idea, to optimize this situation.

> Massimo (mastino@hotmail.it) writes:
> > I have a very very strange situation with a particular application and
sql
> > server 2005 enterprise.
> >
> > This application combines numerical data from multiple tables. User can
> > make query over this kind of tables, can build queries with "group by"
> > "order by" and "join", "sum, count(*) ecc. on many many columns.
> >
> > I cannot know the query that application is going to build, so I do not
> > know how to create indexes.
> > I cant make indexes on all the columns of course, so I'm creating some
> > index over columns that should be statistically used in the join, but
> > when there is a group by on a column chosen from the user, I realize
> > that the plan become non efficient with the famigerate "TABLE SCAN".
> >
> > Can somebody, give me an idea, to optimize this situation.
>
> My gut reaction is that I would want more information about how
> the users will use the system. Will all combinations be equally common?
> Are some important than others? Are some combinations so completely
> meaningless, that the users should even be prevented from trying them
> (as doing that could cause performance issues).
>
> Maybe you should pre-aggregate data. But that leads to the thought that
> this is something which should be in Analysis Services instead. (I have
> no experience of Analysis Services, so I cannot say much more.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

> Thanx for your answer, it's a very particular application, it wants to
> explode some result from money activities.

> Oracle with the same database, the same data and the same indexes, is more
> flexible and often succeed in using indexing, without any kind of HINTS in
> the queries.
>
> The sql 2005 engine doesnt understand, the way to use an index even if it
> has a subset of the columns he needs, as oracle does.

> With a particular query the DTA suggests to index all the columns in the
> WHERE + all the columns in the GROUP BY + all the columns in the ORDER
> BY + use the INCLUDE clause with the columns in the SELECT statement
> with functions like SUM, ecc.

> Massimo (mastino@hotmail.it) writes:
> > Thanx for your answer, it's a very particular application, it wants to
> > explode some result from money activities.
>
> Data mining? In such case, Analysis Services is definitely your guy.
>
> > Oracle with the same database, the same data and the same indexes, is
more
> > flexible and often succeed in using indexing, without any kind of HINTS
in
> > the queries.
> >
> > The sql 2005 engine doesnt understand, the way to use an index even if
it
> > has a subset of the columns he needs, as oracle does.
>
> The bitter truth is that if you try to implement an Oracle solution on
> SQL Server, it is not going to turn out well. Just like an SQL Server
> solution on Oracle would be bad.
>
> > With a particular query the DTA suggests to index all the columns in the
> > WHERE + all the columns in the GROUP BY + all the columns in the ORDER
> > BY + use the INCLUDE clause with the columns in the SELECT statement
> > with functions like SUM, ecc.
>
> Yes, that gives you a covering index. This means that when the optimizer
> has to scans the data, it only has to scan the relevant columns. This
> means fewer pages to read, which means better performance. But if the
> index is constrained to the WHERE clause only, locating the rows will
> be faster. However, if the WHERE clause qualifies many rows, the
> bookmark lookup for each row will be so expensive that a table scan
> is better.
>
> Unfortunately, with the tiny inforamtion I have about your system, it's
> difficult to give very detailed advice.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx