Hi,
Can't post specifics at the moment but if this seems like a common
problem any help would be appreciated.
When querying with ~6 tables, using mostly left outer joins, I get
standard performance with the where clause
where XXX is not null
however, if I try the clause
where XXX = 4
the query takes upwards of 5-6 minutes (I just stop it running at that
point.
The field XXX is in the 'main' table (to which the joins attach), it's
an integer field too so I can't see too many problems there.
There's no index on the XXX field, but if I remove the joins, I get
standard performance doing either query.
So why would the second query's performance differ so significantly?
Cheers,
Chris
3  1629 
Not Me (no*****@da.com .hk.org) writes: Can't post specifics at the moment but if this seems like a common
problem any help would be appreciated.
...
So why would the second query's performance differ so significantly?
Well, if you can post the specifics, the answer you get will neither
be very specific.
All I can say is that SQL Server uses a cost-based optimizer that makes
it descisions from statistics collected about the data in the table. From
these statistics it makes estimates about which plan is the best. Statistics
can be out of date, and even if they are not, the statistics are samples
that can be skewed. And, in the course of computing plans for a six-way
join a small error in an initial estimate can give a huge error in the end.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog wrote: Not Me (no*****@da.com .hk.org) writes:
Can't post specifics at the moment but if this seems like a common
problem any help would be appreciated.
...
So why would the second query's performance differ so significantly?
Well, if you can post the specifics, the answer you get will neither
be very specific.
Yup, I understand that.
Well I first tried putting the where clause into a subquery where that
table joined the others, this didn't affect the results. Then I tried
performing that query seperately, creating a temporary table, then using
that in the join for the second query. This gave me what I wanted, a
result in seconds rather than minutes.
Cheers,
Chris
Not Me (no*****@da.com .hk.org) writes: Well I first tried putting the where clause into a subquery where that
table joined the others, this didn't affect the results. Then I tried
performing that query seperately, creating a temporary table, then using
that in the join for the second query. This gave me what I wanted, a
result in seconds rather than minutes.
Yes, sometimes that can be a useful strategy to impose a join order that
the optimizer does not find itself. When you do this you should be fairly
confident that this is the right order today, but also tomorrow.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Robert |
last post by:
I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table...
|
by: M Wells |
last post by:
Hi All,
I have a table that currently contains approx. 8 million records.
I'm running a SELECT query against this table that in some
circumstances is either very quick (ie results returned in Query
Analyzer almost instantaneously), or very slow (ie 30 to 40 seconds to
return results), and I'm trying to work out how I improve performance.
Essentially the query I'm running is nothing more complex than:
|
by: gizmo |
last post by:
I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.
Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance...
|
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...
|
by: Yonatan Goraly |
last post by:
I am in the process of adding PostgreSQL support for an application, in
addition to Oracle and MS SQL.
I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III board.
I have a query that generally looks like this:
SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND t2.p='string'
AND t2.q=1
This query is strikingly slow (about 100 sec when both t1 and t2 has
| | |
by: diffuser78 |
last post by:
I have just started to learn python. Some said that its slow. Can
somebody pin point the issue.
Thans
|
by: Andy_Khosravi |
last post by:
I'm having a problem trying to optimize the performance of one of my
A97 databases. I have very slow record navigation after a change I made
to the table structure, and I'm not sure how best to correct it.
For purposes of explanation, lets say I have two tables: tblIssues and
tblLocationHistory. The tblIssues table contains 'incidents' along with
the incident header information. The tblLocationHistory table contains
a list of all...
|
by: wizofaus |
last post by:
I previously posted about a problem where it seemed that changing the
case of the word "BY" in a SELECT query was causing it to run much much
faster.
Now I've hit the same thing again, where basically almost any change I
make to how the query is executed (so that it still performs the same
function) causes the performance to jump from a dismal 7 or 8 seconds
to instantaneous. It's a very simple query of the form:
SELECT Min(MyValue)...
|
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...
|
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...
|
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,...
| | |
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
