Wacky Query Execution time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Wacky Query Execution time

Author

Topic

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-01-25 : 11:23:53

I have the following query:

select a.PartyID as ID1, b.PartyID from
	
		(	
			select top 500000 (FirstName + LastName + LTrim(RTrim(Address1))) as MatchString, PCM1.partyid			
				from 
					Pn1 	
					inner join PCM1	                on Pn1.partyid = PCM1.partyID
					inner join  CM1 	        on PCM1.CmID = CM1.CmID
					INNER join postaladdress e 	on CM1.cmid = e.cmid 
		) a 
		Inner JOIN 	
		(
			select top 500000 (FirstName + LastName +LTrim(RTrim(Address1))) as MatchString, PCM2.partyid					
				from 
					Person Pn2	
					inner join PCM2 	      on Pn2.partyid = PCM2.partyID
					inner join CM2 	              on PCM2.CMID = CM2.CMID
					INNER join postaladdress d    on CM2.cmid = d.cmid
		 )  b 
		ON a.MatchString = b.MatchString and a.partyId < b.partyid

Each of those derived tables returns a result set of approx 286000 rows. If I remove the TOP clause and try to run the query on all the data it runs forever never returning anything ( well, never being as long as I was willing to wait, about 15minutes). If I put TOP 500000, which is there now, it runs in 14 seconds. I am guessing this has something to do with query optimization? TOP 500000 should return the same resultset as the query without TOP at all, as 500000 is far more than the actual number of rows returned without TOP. Am I misunderstaning how TOP works? Is this an execution plan issue?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-25 : 11:37:19

1. if you need to get all data use "top 100 percent"
2. try using join hints and see if that helps you. Look them up in BOL.

Go with the flow & have fun! Else fight the flow

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-01-25 : 11:48:09

nevermind

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-25 : 12:10:50

ok...

Go with the flow & have fun! Else fight the flow

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-01-25 : 12:22:14

I only meant nevermind on that last comment. So you were still helpful.

Joining on each individual field being equal seems to solve the problem and removes some potential incorrect results, so I will go with that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources