|
|
Posted by Massimo on 01/26/07 22:38
Thanx for your answer, it's a very particular application, it wants to
explode some result from money activities.
It is used by banks. I cannot say more.
I'm an oracle dba (ocp), and I would use Partitioning as I do sometimes in
oracle, on the column in join in this case, to try to increase at least some
kind of parallelism, but the Database Tuning Advisor, with a trace of a day
of activity never suggests me, this kind of approach. So I think it is not a
good way.
So the only way, I'm thinking to follow is to speed up "table scan", but how
?
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
"Erland Sommarskog" <esquel@sommarskog.se> ha scritto nel messaggio
news:Xns98C4F2F38270AYazorman@127.0.0.1...
> 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]
|