473,856 Members | 1,666 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Mysterious mystery -- 2002-11-08/2002-11-09

I have function that returns a table of information about properties. The
data comes from three different tables -- addresses (called PropertyID),
property characteristics , and events concerning those properties (sales,
appraisals, etc.), plus a table that maps one representation of property
types into another. The records are selected on the basis of location
(longitude & latitude), property type, event type, and a range of
event dates (upper and lower date specified). There are tens of millions
of records of all types, and almost any location, property type, event type
and date range will yield records.

The heart of it is a cursor that selects records from joins on this basis:

SELECT <a bunch of fields>
FROM Property d
JOIN PropTypeMap ptm ON ptm.PropertyTyp eID = d.PropertyTypeI D
JOIN PropertyID a ON a.PropID = d.PropID
JOIN Event e1 ON e1.PropID = d.PropID
LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE
d.LastSaleDate >= @LoDate
AND a.GeoLongitude BETWEEN @LowerLon AND @UpperLon
AND a.GeoLatitude BETWEEN @LowerLat AND @UpperLat
AND ptm.PropCategor y = @PropType
AND a.GeoMatch <= @MinGeoQuality

AND e1.EventTypeID = @SaleEventType
AND e1.TransactionT ype = 'R'
AND e1.EventDt BETWEEN @LoDate AND @HiDate
AND e1.EventAmt > 0

AND e2.EventTypeID = @AssessmentEven tType
AND e2.EventDt <= @HiDate
AND e2.EventAmt > 0

Each property has one PropertyID record, one Property record, and N Event
records (average perhaps five).

What is the mystery? If @HiDate, which is the upper end of the time window,
is 2002-11-08 or earlier, nothing is returned. If it's 2002-11-09 or later,
oodles of records are found. I get the same query plan for either one, and
based on the content of the data, they should return almost exactly the same
set of records -- exactly the same set in almost all cases, in fact.

Is 2002-11-08/09 some sort of magic dividing point? I have replicated this
on the large database and on a smaller test version on another SQL Server.
(SQL Server 2000) I dropped the indexes and tried it, and
the same thing happened. This is driving me crazy!
Jul 20 '05 #1
3 1798
[posted and mailed, please reply in news]

Jim Geissman (ji**********@c ountrywide.com) writes:
What is the mystery? If @HiDate, which is the upper end of the time
window, is 2002-11-08 or earlier, nothing is returned. If it's
2002-11-09 or later, oodles of records are found. I get the same query
plan for either one, and based on the content of the data, they should
return almost exactly the same set of records -- exactly the same set in
almost all cases, in fact.

Is 2002-11-08/09 some sort of magic dividing point? I have replicated
this on the large database and on a smaller test version on another SQL
Server. (SQL Server 2000) I dropped the indexes and tried it, and the
same thing happened. This is driving me crazy!


Without access to the database, or a script that reproduces the problem,
there is much left for me, but to guess.

And the obvious guess is that the rows you find are from 2002-11-08, but
have a time porttion, and thus are > '2002-11-08 00:00:00'.

However, I notice one thing with your query which may not be correct:

LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE ...
AND e2.EventTypeID = @AssessmentEven tType
AND e2.EventDt <= @HiDate
AND e2.EventAmt > 0

The idea with a left outer join is that you include rows in the left
table, even if there is no matching rows in the right table. The
columns in the right table are then set to NULL.

Now, the FROM clause is (logically) evaluated before the WHERE clause,
which acts on the filter on the table defined by the FROM clause. This
means that you will filter away those rows with NULL in e2.EventTypeID
that comes from the outer join. Your join is now effectively an inner
join.

There are two ways to handle this:
1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
clause.
2) Move the conditions to the ON clause.
--
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
Thanks, Erland. You hit the nail on the head. I was naively assuming that
the right side of the Left Outer Join didn't matter, but when I removed the
second condition in the excerpt below, the records all appeared. As you
said, it was because the FROM was being evaluated before the WHERE. Moving
the condition to ON fixed it. Thanks. I wonder if I have any other
scripts that make this mistake....
However, I notice one thing with your query which may not be correct:

LEFT OUTER JOIN Event e2 ON e2.PropID = d.PropID
WHERE ...
AND e2.EventTypeID = @AssessmentEven tType
AND e2.EventDt <= @HiDate
AND e2.EventAmt > 0

The idea with a left outer join is that you include rows in the left
table, even if there is no matching rows in the right table. The
columns in the right table are then set to NULL.

Now, the FROM clause is (logically) evaluated before the WHERE clause,
which acts on the filter on the table defined by the FROM clause. This
means that you will filter away those rows with NULL in e2.EventTypeID
that comes from the outer join. Your join is now effectively an inner
join.

There are two ways to handle this:
1) Add conditions with OR clauses or coalesce to handle NULL in the WHERE
clause.
2) Move the conditions to the ON clause.

Jul 20 '05 #3
Jim Geissman (ji**********@c ountrywide.com) writes:
Thanks, Erland. You hit the nail on the head. I was naively assuming
that the right side of the Left Outer Join didn't matter, but when I
removed the second condition in the excerpt below, the records all
appeared. As you said, it was because the FROM was being evaluated
before the WHERE. Moving the condition to ON fixed it. Thanks. I
wonder if I have any other scripts that make this mistake....


A very common mistake to make. I recall that I did it myself a few
times when I switched to the new syntax from the old *= crap.

--
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 #4

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

Similar topics

4
3425
by: Viorel | last post by:
For me is a little bit mysterious how work encoding and decoding functions, what is underneath of their calling? Encoding1.GetBytes(string1); in particularly ASCII.GetBytes(string1) Encoding1.GetChars(string1); Encoding1.GetChars(arrayofbytes1);
7
6249
by: jmm-list-gn | last post by:
Hello, <http://www.asaom.edu> There are some curious spacing issues with the top bar (gray) and the main navigation bar. The most significant is how the nav bar looks in Opera v6 (win2k): the text portion is has extra space at the top that I cannot remove. As a result the text portion is pushed below the background bar by a few pixels. This does not occur in Mozilla/Firefox, or IE6. (I have ordered Opera v7.) Is this a known issue? The...
0
1558
by: public heath vb developer | last post by:
We have a solution with 29 projects including a main menu and 28 dlls. One of the projects (Project B) was created by copying an existing project (Project A), making changes including the assembly name, root namespace, form name, and internal code. When the new project (Project B) was added to the solution and a reference to it was attempted to be added to the main menu project (Project MainMenu), the new project (Project B) reference did not...
0
1775
by: William Wisnieski | last post by:
Hello Everyone: I'm having a very strange problem occurring with my Access 2000 database. I call it the "mystery record." Here's the story: I have a query by form that returns a record set in a datasheet. The user double clicks on a row in that datasheet and a main form (pop up) opens bound to a table with a continuous subform bound to a query.
115
7678
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical transform function. When compiling under gcc on my big-endian PowerPC (Mac OS X), declaring this array as "static" DECREASES the transform throughput by around 5%. However, declaring it as "static" on gcc/Linux/Intel INCREASES the throughput by...
14
2804
by: jojoba | last post by:
Hi, I hope this post is ok for this group. Here's my deal: I have two computers on my LAN at home. One desktop. One laptop. Both computers are wireless enabled (and wired enabled too). I have running a fairly simple HTTP server (written in python) that i
2
2071
by: Yannick Turgeon | last post by:
Hello, I'm using A97 (french version) on XP (english version). Since this afternoon, all the Access built-in french-equivalent function are generating a "Sub or Function not defined" error. An exemple: IIf(1=1,"bob","joe") is ok but it's french equivalent VraiFaux(1=1,"bob","joe") generates the error. It's not linked to the PC since other applications with this function
5
3981
by: Sergey | last post by:
Hi everyone, It looks like a mystery, but I hope there should be some explanation to the issue I experience. Once in a blue moon a random stored procedure stops working the way it was designed. The stored procedure code looks unchanged. Recompiling, altering the code do not help. It looks like it is simply does not execute some part of it or does not run at all. However it returns no errors. One time a procedure entered into infinite...
0
1111
by: Clarence | last post by:
I'm having problems with JPype and am trying to change the way it creates Python classes as proxies for Java classes and interfaces. I'm trying to get around "inconsistent mro" problems, but in doing so, I've run into a real mystery. Here's the original code. It first makes a metaclass, then makes a class using that metaclass. ############## metaclass = type.__new__(type, name+"$$Static", tuple(meta_bases),
1
2394
by: Phil Clingenpeel | last post by:
Hi, I wrote a program to translate a binary file (for which I have the text version) into another binary file (I needed a new format) and text file. There is no division involved, just some nested loops with data being transferred from one buffer to another, to simplify the creation of the desired output files. The outer (main) loop functions properly 9,947 times and then 'something' happens in the middle of an interior loop,...
0
9904
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
9758
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
10692
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7928
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7086
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();...
0
5754
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...
1
4568
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
2
4169
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3195
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.