473,575 Members | 3,470 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query too slow


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),
@FlagProdNoTarg et 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 = 'ZZZZZZZZZZZZZZ ZZZZZZ'
Set @AgenteDa = ''
Set @AgenteA = 'ZZZZ'
Set @NazioneDa = ''
Set @NazioneA = 'ZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZ'
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 @FlagProdNoTarg et = 'Vero'
Set @GrAcqDa = ''
Set @GrAcqA = 'ZZZZZZZZZZ'
Set @TipoCliDa = ''
Set @TipoCliA = 'ZZZ'
Set @SettMercDa = ''
Set @SettMercA = 'ZZZ'

Select WSDFR.AreaComme rciale,
WSDFR.Agente,
WSDFR.NazDestin ,
WSDFR.ZonaDesti n,
WSDFR.ProvDesti n,
WSDFR.Cliente,
WSDFR.DescrClie nte,
WSDFR.GruppoAcq ,
WSDFR.TipoCli,
WSDFR.SettMerc,
WSDFR.CDestin,
WSDFR.DescrDest in,
WSDFR.TipoDesti n,
WSDFR.EsclStati s,
WSDFR.EsclTarge t,
WSDFR.ValoreNet to,
WSDFR.TpDocum,
WSDFR.VCambioIT L,
WSDFR.VCambioEU R,
WSDFR.MeseFatt,
WSDFR.Posizione

From W_St_DocFatt_Ri ghe WSDFR
inner join UniP_Prodotti UPP on
WSDFR.prodotto= UPP.Cod
Where WSDFR.Dtdocum between @DataDa and @DataA and
WSDFR.AreaComme rciale between @AreaDa and @AreaA and
WSDFR.LineaProd otto between @LineaDa and @LineaA and
WSDFR.TipoProdo tto between @TipoDa and @TipoA and
WSDFR.FamigliaP rodotto between @FamigliaDa and @FamigliaA and
WSDFR.Prodotto between @ProdottoDa and @ProdottoA and
WSDFR.Agente between @AgenteDa and @AgenteA
*************** *************** *************** *************** *************** *
*************** *********

"W_St_DocFatt_R ighe" 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.AreaComme rciale between @AreaDa and @AreaA and
WSDFR.LineaProd otto between @LineaDa and @LineaA and
--WSDFR.TipoProdo tto between @TipoDa and @TipoA and
--WSDFR.FamigliaP rodotto 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.AreaComme rciale between @AreaDa and @AreaA and
--WSDFR.LineaProd otto between @LineaDa and @LineaA and
--WSDFR.TipoProdo tto between @TipoDa and @TipoA and
WSDFR.FamigliaP rodotto 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.AreaComme rciale between @AreaDa and @AreaA and
--WSDFR.LineaProd otto between @LineaDa and @LineaA and
--WSDFR.TipoProdo tto between @TipoDa and @TipoA and
--WSDFR.FamigliaP rodotto 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

Jul 20 '05 #1
11 5386
Eugenio (Ci**@Eugenio.i t) 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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

"Erland Sommarskog" <so****@algonet .se> ha scritto nel messaggio
news:Xn******** **************@ 127.0.0.1...
Eugenio (Ci**@Eugenio.i t) 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, so****@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
Jul 20 '05 #3
>
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, so****@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

Jul 20 '05 #4
Eugenio (Ci**@Eugenio.i t) 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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
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.AreaComme rciale between @AreaDa and @AreaA
subclause, when your set statement sets them to the same value? Why not
WHERE ... WSDFR.AreaComme rciale = 'UNI'
?
Jul 20 '05 #6

"Ross Presser" <rp******@imtek .com> ha scritto nel messaggio
news:gr******** *************** ****@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.AreaComme rciale between @AreaDa and @AreaA
subclause, when your set statement sets them to the same value? Why not
WHERE ... WSDFR.AreaComme rciale = '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
Jul 20 '05 #7
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

Jul 20 '05 #8
Eugenio (Ci**@Eugenio.i t) 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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9

..

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
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5340
by: Tim Fountain | last post by:
We've recently enabled slow query logging on a server and it's proving interesting seeing which queries are bogging things down. This one is puzzling me a little: SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status = 'normal' ORDER BY posted DESC LIMIT 5; The purpose of this query is to list the five...
5
3897
by: Jason | last post by:
The following stored procedure is taking too long (in my opinion). The problem seems to be the SUM line. When commented out the query takes a second or two. When included the response time climbs to minute and a half. Is my code that inefficient or is SUM and ABS calls just that slow? Any suggestions to spead this up? Thanks, - Jason
1
1668
by: Gary Wales | last post by:
We have two main query types running against a table of some 2 million rows and have gotten query response down to well under a second by using the right indexes. Problem is that we are running an advertising campaign that brings a concentrated block of users to the site. When this happens one of the queries which relies on a particluar...
1
1639
by: Thomas Bartkus | last post by:
Is it possible that the elapsed time being measured includes waiting for the client to acknowledge that it has received all the data? In *addition* to the server execution time? Documentation seems to *imply* that the slow query log only looks at server execution time. But, it doesn't acknowledge this directly and there seems to be a...
7
3620
by: Thomi Baechler | last post by:
Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB , dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT FROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZ ON dbo.HRM_CALENDER.Datum >=...
2
6225
by: AG | last post by:
I am able to run a query which runs FAst in QA but slow in the application.It takes about 16 m in QA but 1000 ms on the Application.What I wanted to know is why would the query take a long time in the application when it runs fast on SQL server? How should we try debugging it? Ajay
3
4783
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. Obviously not workable! I know where the problem is, I just don't know how to fix it. The query calls a function, and I assume it gets slow because...
4
24450
by: cefrancke | last post by:
Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?). For example, I want to sum up all values under 'Amount', and also have a field call 'Cumulative' and have the sum grow with each record. Using "MyTable", the query results are... ID or Date ...
0
2665
by: Dave Hammond | last post by:
Hi All, I'm trying to use the slow-query-log (with --log-queries-not-using-indexes enabled) to determine if any queries need optimization, and have a few questions about some entries I'm seeing. First question: given a table defined as: CREATE TABLE `oa_location` (
2
9828
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes to open in datasheet view. As an experiment, I deleted all rows in all tables; after that, the query took only seconds to open in both design view...
0
7845
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7775
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8281
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7868
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8143
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3778
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2286
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1107
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.