selecting a large amount of data

Question

selecting a large amount of data

Jibi John

Grasshopper

Points: 18
More actions
July 3, 2007 at 11:41 pm

#177327

I want to select abt 27k rows from a table of 100k rows and sort the selection in a tree format in the least amount of time . what should i do ?
this database keeps growing at faster rate.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

See "expanding hierarchies" in books online. There's lot's of other methods but that one is pretty easy.

For wicked speed, try the following URL with the understanding that it takes a bit to learn the technique and setup... I believe there's a bug in a couple of the pieces of code, but the principle is absolutely sound. Doesn't take much to fix the code.

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jibi John Grasshopper Points: 18 More actions · Answer 2

Hi! Thank you. That was really helpfull.

is there any technique to make this work faster.presently it takes around 12 minutes for selecting and arranging the data in trees.( selecting 27k rows from 100k rows). can u help me on that area.

Jibi

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

If you're talking about the method in the URL I provided and not the BOL solution, some indexes will certainly help once the tree for the whole DB is constructed. It'll do a 27k hierarchical lookup in almost no time (literally). But, you have to build the tree for the whole DB...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)