Eugenio (Ci**@Eugenio.it) writes:
I send at your mailbox the text files you've asked me, because I can't
sent you a reply with an attachment into the newsgroup. I don't know
why, but it's impossibile.
I had a look at your tables, and I am afraid that your set of tables and
views is far too huge for me to try to dig into it all. But I can share
some observations. Before I go on, I like to stress that there is a limit
on how much help you get in the newsgroups. Also, help here is based on
the idea that you do some of the work yourself. You don't always get the
answer - sometimes you only get the information so that you can find out
on your own. If you are not prepared to investigate on your own, you are
not likely to get your issue sorted out. This does not the least apply in
a complex case like this one.
1) The query you posted was this one (abbreviated)
declare @Azienda as varchar(3), @Utente as varchar(20),
@DataDa as datetime, @DataA as datetime,
...
Set @Azienda = '900'
Set @Utente = 'Eugenio'
Set @DataDa = '2004-01-01'
Set @DataA = '2004-01-10'
Set @AreaDa = 'UNI'
Set @AreaA = 'UNI'
Set @LineaDa = ''
Set @LineaA = 'ZZZ'
Set @TipoDa = ''
...
Select WSDFR.AreaCommerciale,
WSDFR.Agente,
WSDFR.NazDestin,
...
From W_St_DocFatt_Righe WSDFR
inner join UniP_Prodotti UPP on
WSDFR.prodotto=UPP.Cod
Where WSDFR.Dtdocum between @DataDa and @DataA and
WSDFR.AreaCommerciale between @AreaDa and @AreaA and
WSDFR.LineaProdotto between @LineaDa and @LineaA and
WSDFR.TipoProdotto between @TipoDa and @TipoA and
WSDFR.FamigliaProdotto between @FamigliaDa and @FamigliaA and
WSDFR.Prodotto between @ProdottoDa and @ProdottoA and
WSDFR.Agente between @AgenteDa and @AgenteA
It was not clear to me, if this is the actual query you run in
production, or a simplification of the stored procedure, and the
variables are parameters in real life. This has very big importance,
because when SQL Server builds a query plan it uses the values of
the input parameters as a hint, but it is completely blind to the values
of variables and assumes default values. This means that with variables,
you will get the same plan, no matter what you put into the variables.
If you are using parameters, there is a chance that adding WITH RECOMPILE
to the procedure definition that you get the best plan each time.
2) It seems that you are doing some kind of dynamic search where there
the user can specify many different conditions. This is a tricky
case to handle, and often you get best performance with dynamic SQL.
However, dynamic SQL requires the users to have direct permissions
on the involved views and tables, so it may not be an acceptable
solution. In any case, on
http://www.sommarskog.se/dyn-search.html,
I have an article which discusses this topic in detail.
3) When looking at your data model, it appears unfinished. There are
some foreign keys defined, but judging from the queries there is for
instance a foreign key from P_FatturaT.Tipodoc to TB_TipologiaDoc.Tipo.
Adding foreign keys may not add performance in itself, but a thorough
study of the data model, may lead to a more precise and effective
data model.
4) The inner view, W_St_DocFatt_TestDoc, has a GROUP BY which only serves
as a DISTINCT. Maybe it is needed, maybe it is not. Since the key
information was incomplete I could not deduce that. But there certainly
is a cost for getting the distinct values, so if you can get than one
out, you may win a lot. Also, thanks to the GROUP BY, you cannot make
this view an indexed view, which could increase the speed of the
query radically.
5) None of the tables has a clustered index. Is this really a concious
decision? In such case, I think you should review it. If you would
change the indexes for the most common search indexes, that could
do wonders to performance, not the least with dynamic SQL.
6) There is a lot of NOLOCK. This may be good for performance, but it
is not good for correctness. If there is indeed traffic going on
in the database when you run your query, you can get incorrect or
inconsistent results back. If you make your view indexed, you should
remove the locking hints, because it's not clear what they would mean.
I also like to repeat what I said in my original posting:
SQL Server uses a cost-based optimizer. This optimizer evaluates a number
of possible query plans, and estimates which plan will give the best
performance. As basis for its decisions it uses statistics about the
table which holds the distribution of the data in the various columns.
By default these statistics are updated automatically, usually in
conjunction with SQL Server querying the tables.
There are plenty of possibilities for optimizer to go wrong. For instance
if the statistics are somewhat skewed, a small error in the first table
when computing the cost of a certain join order, may be a large error
in the last, and incorrectly lead to the wrong plan. There are also
systematic errors; the optimizer does not know about correlation between
columns, for instance that OrderDate and InvoiceDate tend follow each
other.
Because of this, it can well happen that a query that executed well
yesterday, suddenly executes much slower, because you've execeeded some
threshold which causes SQL Server to pick a bad plan.
I repeat this, because given the very complex nature of your query,
there are very many ways for the optimizer to go astray.
I hope these observations should give you some input for your
further investigations.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
