SEO

Excuse me in advance fo my little English.

I've got this stored procedure

************************************************** **************************
**********

declare @Azienda as varchar(3), @Utente as varchar(20),
@DataDa as datetime, @DataA as datetime,
@AreaDa as varchar(3), @AreaA as varchar(3),
@LineaDa as varchar(3), @LineaA as varchar(3),
@TipoDa as varchar(3), @TipoA as varchar(3),
@FamigliaDa as varchar(3), @FamigliaA as varchar(3),
@ProdottoDa as varchar(20), @ProdottoA as varchar(20),
@AgenteDa as varchar(4), @AgenteA as varchar(4),
@NazioneDa as varchar(50), @NazioneA as varchar(50),
@ZonaDa as Varchar(3), @ZonaA as Varchar(3),
@ProvinciaDa as varchar(2), @ProvinciaA as varchar(2),
@ClienteDa as Varchar(12), @ClienteA as Varchar(12),
@DestinDa as varchar (5), @DestinA as varchar (5),
@TipoDestinDa as varchar(1), @TipoDestinA as varchar(1),
@FlagProdNoTarget as varchar(5),
@GrAcqDa as varchar(10), @GrAcqA as varchar(10),
@TipoCliDa as varchar(3), @TipoCliA as varchar(3),
@SettMercDa as varchar(3), @SettMercA as varchar(3)


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 = ''
Set @TipoA = 'ZZZ'
Set @FamigliaDa = ''
Set @FamigliaA = 'ZZZ'
Set @ProdottoDa = ''
Set @ProdottoA = 'ZZZZZZZZZZZZZZZZZZZZ'
Set @AgenteDa = ''
Set @AgenteA = 'ZZZZ'
Set @NazioneDa = ''
Set @NazioneA = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZ'
Set @ZonaDa = ''
Set @ZonaA = 'ZZZ'
Set @ProvinciaDa = ''
Set @ProvinciaA = 'ZZ'
Set @ClienteDa = ''
Set @ClienteA = 'ZZZZZZZZZZZZ'
Set @DestinDa = ''
Set @DestinA = 'ZZZZZ'
Set @TipoDestinDa = ''
Set @TipoDestinA = 'Z'
Set @FlagProdNoTarget = 'Vero'
Set @GrAcqDa = ''
Set @GrAcqA = 'ZZZZZZZZZZ'
Set @TipoCliDa = ''
Set @TipoCliA = 'ZZZ'
Set @SettMercDa = ''
Set @SettMercA = 'ZZZ'

Select WSDFR.AreaCommerciale,
WSDFR.Agente,
WSDFR.NazDestin,
WSDFR.ZonaDestin,
WSDFR.ProvDestin,
WSDFR.Cliente,
WSDFR.DescrCliente,
WSDFR.GruppoAcq,
WSDFR.TipoCli,
WSDFR.SettMerc,
WSDFR.CDestin,
WSDFR.DescrDestin,
WSDFR.TipoDestin,
WSDFR.EsclStatis,
WSDFR.EsclTarget,
WSDFR.ValoreNetto,
WSDFR.TpDocum,
WSDFR.VCambioITL,
WSDFR.VCambioEUR,
WSDFR.MeseFatt,
WSDFR.Posizione

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


************************************************** **************************
************************

"W_St_DocFatt_Righe" is a view.

This query run on my SQL7 server and it takes about 10 seconds.
This query exists on another SQL7 server and until last week it took about
10 seconds.
The configuration of both servers are same. Only the hardware is different.

Now, on the second server this query takes about 30 minutes to extract the s
ame details, but anybody has changed any details.

If I execute this query without Where, it'll show me the details in 7
seconds.
This query still takes about same time if Where is


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

or

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

or

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 is a real puzzle!

What happen?

Is there someone that had such as problems and have the right solution?

Thanks in advance.
Bye
Eugenio

Eugenio (Ciao@Eugenio.it) writes:
> This query run on my SQL7 server and it takes about 10 seconds.
> This query exists on another SQL7 server and until last week it took about
> 10 seconds.
> The configuration of both servers are same. Only the hardware is
> different.
>
> Now, on the second server this query takes about 30 minutes to extract
> the s ame details, but anybody has changed any details.
>
> If I execute this query without Where, it'll show me the details in 7
> seconds.
> This query still takes about same time if Where is

With out knowledge about the underlying tables in the view and their
indexes, I can only answer in general terms.

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.

The first you should do is to verify that the involved tables have the
same set of indexes in both servers. You could also try UPDATE STATISTICS
WITH FULLSCAN on the problematic server, this may remove inaccuracies in
the statistics. You should also use DBCC SHOWCONTIG on the tables and
observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Erland Sommarskog" <sommar@algonet.se> ha scritto nel messaggio
news:Xns94F09D378E44FYazorman@127.0.0.1...
> Eugenio (Ciao@Eugenio.it) writes:
> > This query run on my SQL7 server and it takes about 10 seconds.
> > This query exists on another SQL7 server and until last week it took
about
> > 10 seconds.
> > The configuration of both servers are same. Only the hardware is
> > different.
> >
> > Now, on the second server this query takes about 30 minutes to extract
> > the s ame details, but anybody has changed any details.
> >
> > If I execute this query without Where, it'll show me the details in 7
> > seconds.
> > This query still takes about same time if Where is
>
> With out knowledge about the underlying tables in the view and their
> indexes, I can only answer in general terms.
>
> 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.
>
> The first you should do is to verify that the involved tables have the
> same set of indexes in both servers. You could also try UPDATE STATISTICS
> WITH FULLSCAN on the problematic server, this may remove inaccuracies in
> the statistics. You should also use DBCC SHOWCONTIG on the tables and
> observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp


Thanks Erland
I'll try it immediatly.
Bye
Eugenio

>
> With out knowledge about the underlying tables in the view and their
> indexes, I can only answer in general terms.
>
> 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.
>
> The first you should do is to verify that the involved tables have the
> same set of indexes in both servers. You could also try UPDATE STATISTICS
> WITH FULLSCAN on the problematic server, this may remove inaccuracies in
> the statistics. You should also use DBCC SHOWCONTIG on the tables and
> observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp



I did that, but it's changed nothing.
Can you give me others suggestion about this.


Thanks a lot
Eugenio

Eugenio (Ciao@Eugenio.it) writes:
> I did that, but it's changed nothing.
> Can you give me others suggestion about this.

No. Not without more information. Please post the following:

o CREATE TABLE and CREATE INDEX statements for the involved tables.
o The output when you run the procedures with SHOW STATISTICS PROFILE ON
on *both* servers. Since the output of the query plan is very wide,
please but this output in an attachment. (In a text file.)


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On Tue, 25 May 2004 09:01:31 +0200, Eugenio wrote:

> I did that, but it's changed nothing.
> Can you give me others suggestion about this.

Recompile the view so that the stored plan will take into account the
updated statistics.

Use the Query Plan Optimizer in Query Analyzer to get suggestions about
indexes to add.

Shouldn't all those Set @var... statments be in a single SELECT statement?

Does it make sense to have a
WHERE ... WSDFR.AreaCommerciale between @AreaDa and @AreaA
subclause, when your set statement sets them to the same value? Why not
WHERE ... WSDFR.AreaCommerciale = 'UNI'
?

"Ross Presser" <rpresser@imtek.com> ha scritto nel messaggio
news:gr5k7s2nbwbe.gzvmi45ehns7.dlg@40tude.net...
> On Tue, 25 May 2004 09:01:31 +0200, Eugenio wrote:
>
> > I did that, but it's changed nothing.
> > Can you give me others suggestion about this.
>
>
> Recompile the view so that the stored plan will take into account the
> updated statistics.
>
> Use the Query Plan Optimizer in Query Analyzer to get suggestions about
> indexes to add.

I did that, but it's changed nothing

>
> Shouldn't all those Set @var... statments be in a single SELECT statement?
>
> Does it make sense to have a
> WHERE ... WSDFR.AreaCommerciale between @AreaDa and @AreaA
> subclause, when your set statement sets them to the same value? Why not
> WHERE ... WSDFR.AreaCommerciale = 'UNI'
> ?

No, because the first is a part of a stored procedure. It's the query who
doesn't work.
I extracted it to try with Query Analyzer.
An Access programm calls this SP giving a several parameters.

Thanks

Eugenio

> > I did that, but it's changed nothing.
> > Can you give me others suggestion about this.
>
> No. Not without more information. Please post the following:
>
> o CREATE TABLE and CREATE INDEX statements for the involved tables.
> o The output when you run the procedures with SHOW STATISTICS PROFILE ON
> on *both* servers. Since the output of the query plan is very wide,
> please but this output in an attachment. (In a text file.)
>

Hi, Erland

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.

Thanks
Eugenio

Eugenio (Ciao@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.

Got the mail. Since the tables and the plans are complex, I may take a
few days before I get the occasion to look at it. In the mean while,
if you cannot post it as an attachment, you could put it on a web site
and post a URL, in case someone else is interested in taking a stab at it.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

..
>
> Got the mail. Since the tables and the plans are complex, I may take a
> few days before I get the occasion to look at it. In the mean while,
> if you cannot post it as an attachment, you could put it on a web site
> and post a URL, in case someone else is interested in taking a stab at it.
>
>

These are the URL where you can find my text files.

Tables and Views structure http://www.unicars.it/eugenio/Tables&Views.sql

Statistic profile of server where the query works
http://www.unicars.it/eugenio/SpeedyStatProf

Statistic profile of server where the query doesn't work
http://www.unicars.it/eugenio/SpeedyStatProf


Thanks
Eugenio