Top or Count? please help - 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

Transact-SQL (2000)

Top or Count? please help

Author

Topic

kien
Starting Member

27 Posts

Posted - 2002-07-16 : 12:06:31

Hi everyone.
If you can find it in yourself to help me, your kindness would be much appreciated.

I'm trying out Page47's code from an earlier posting (as oppose to Frib's code), cause this is the one that I understand.

Basically what the code below does is it searches through a table of cities and coordinates and retrieves all of the neighboring cities within a given latitude and longitude.

The problem I'm encountering is that all data is being inputed in a temp table called #worktable at the SAME time. My computer is not able to handle all this computing. I am trying to break down the computation (ie compute 1000 entries at a time).

I've tried "top 1000", but this only cuts off up to 1000 neigboring cities. What i'm really trying to do is to cut off at 1000 distinct RecordIDs as oppose to 1000 recordID rows. I've tried "count" in various places, but there is always an error.

Can someone please guide me.
Thanks a whole bunch

------------------------------------------
--some variable we'll need
declare @query varchar(7000), @lastID int
select @query = '', @lastID = -1

--create a temp table to work with
select distinct top 300
k.RecordID,
k2.FullName as neighbor,
space(7000) as query
into
#worktable
from
Cities k
inner join Cities k2
on (k2.FeatureCode=14 and abs(k.DLONG - k2.DLONG) <= .5 and
abs(k.DLAT - k2.DLAT) <= .5)

where
k.query is null and
dbo.Distance(RADIANS(k.DLAT), RADIANS(k.DLONG), RADIANS(k2.DLAT), RADIANS(k2.DLONG)) <= 1000
order by
k.RecordID, k2.FullName

-- You may or may not want to index tempdb . . . I'll let you play with that
create index idx_worktable on #worktable(RecordID,neighbor)

-- update the temp table building the query column
update
#worktable
set
@query = query = case
when @lastID <> RecordID then neighbor
else @query + ' ' + neighbor
end,
@lastID = RecordID

-- now update the final table from the temp table
update
Cities

set query = (select max(query) from #worktable where RecordID = k.RecordID)
from
Cities k
where k.RecordID in(select RecordID from Cities where query is null)

-- clean up
drop table #worktable

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-16 : 12:46:29

couple of items for you to consider....

1. if you JUST want to prove the concept/solution works....reduce the input data to 1000 rows....not the output data....it'll be a lot easier doing a
set rowcount 1000 (or 100???)
select * into citiesshort from cities (using approp syntax)
set rowcount 0
and changing the query to work on citiesshort may improve things.

after that it's just a matter of asking/buying for a better spec pc/server....

2. if you do want to break up the processing into sets of 1000...then you have another problem....how do you control re-starting from input record 1001, 2001, etc??...you would need to introduce some form of (permanent) control variable to initiate each loop....you may need to add a row number to each row...to make this easier....and if there are gaps, so what....it's only serving a purpose....whether a batch contains 1000, 999 or 1001 records may not matter to you as long as the full set of processed data adds up to the full set of all input data....

might be easier than working with top, rowcount, max of whatever....if the data causes problems in finding a solution....try to enhance the data legitimately....

kien
Starting Member

27 Posts

Posted - 2002-07-16 : 13:41:00

Thank you Andrew

Yup, I've tried the code with fewer records previously and it works.
Doesn't the "where k.query is null" and other lines similar to this bybass the restart problem you were talking about?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-16 : 14:56:51

but isn't that approach a bit like....

"look at all data not yet processed (and oh stop when I've done something to 1000) and then i'll mark the 1000 as processed"

whereas i'm advocating, numbering each input record (the order etc doesn't matter)...and then processing multiple times something like the following...(in each case i'm looking for an explicit set of input records, not just those who happen to be read first or come to the 'top first'...they are 2 different things....)

set startvalue = 0
set endvalue = 1000
do while recordupdate > 1
set recordupdate = 0
select * from table where key >= startvalue and key <= endvalue
'process result set and set recordupdate =1
set startvalue = endvalue + 1
set endvalue = endvalue + 1000
end

unless you get very unlucky with your numbering sequence over time, it should work fine as long as you don't have gaps of more than 1000.....but if you do then you can always re-number, if the key (sequence number) has no inherant meaning.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources