|
|
Posted by Erland Sommarskog on 01/26/07 22:56
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
Navigation:
[Reply to this message]
|