473,708 Members | 2,404 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 5402
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
5348
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 most recent (non-deleted) comments. Here is the table structure:
5
3904
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
1677
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 index comes severely of the rails and can take up to 2 minutes filling the slow query log for 15 to 20...
1
1650
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 distinct connection between slow network pipes and slow queries. For example - even the simplest...
7
3625
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 >= dbo.HRMABZ.ABZDATF
2
6234
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
4800
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 the function runs on every record. So--is there a way to rewrite the function so it's quicker?...
4
24489
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 Amount Cumulative --------------- ------ ----------
0
2668
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
9840
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 and datasheet view. From these facts, I conclude that Access is evaluating the query when I go to...
0
8787
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 usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8697
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9289
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9060
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
9001
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4454
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4712
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3151
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
3
2096
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.