presenting 25 sorted records from table with 150,000 records

Question

Post reply

presenting 25 sorted records from table with 150,000 records

sam-433147

Ten Centuries

Points: 1221
More actions
August 22, 2007 at 2:09 pm

#178142

I have a client table with 150,000 records
for presenting clients on a form (top 25) i use a procedure
"select top 25 * from Clienttable
where ClientName>=@name
order by ClientName"
with big over 50,000 records the actoin takes to mutch time
has any body an idea how to manage it faster?
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Do you have a clustered index on clientname?

N 56°04'39.16"
E 12°55'05.25"

sam-433147 Ten Centuries Points: 1221 More actions · Answer 2

how can i have a clustered index on clientname when clientname can't be uniqe?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Clustered index has nothing to do with uniqueness.

I suggest you read about indexes in Books Online, just to refresh your knowledge a little.

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

This smacks of a "paging" problem... is that what you're trying to do?

--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)

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 5

select * will mess you up everytime - by selecting * you're forcing SQL to go back to the base row in the table regardles of indexes including including covering indexes.

Take a look at your query, determine exactly which fields need to be present in your select/joins/where clause and adjust accordingly - the build a covering index to contain all the data required to satisfy the query as written.

Joe

sam-433147 Ten Centuries Points: 1221 More actions · Answer 6

thank's a lot, you were very helpfull

is there a way to know the location (row number)

of a certain record in a clustered table?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

The concept of row number is a meaningless one. In a table with a clustered index, the clustering key is the only 'identifier' of row location

Try a nonclustered index on Clientname. 25 out of 150000 should be selective enough for the optimiser to do a non-clustered index scan with bookmark lookups.

Oh, and get rid of that *.

If you can limit down the required firlds to 2 or 3, add them to the NC index on client name

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sam-433147 Ten Centuries Points: 1221 More actions · Answer 8

i mean how to use the clustering key so i can do something like:

select clientname from tbl where "clustering key" =@x

or

select "clustering key" from tbl where clientname=@y

in shortly how i get and use the "clustering key" value?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

The clustering key refers to the columns that make up the clustered index. Nothing fancier than that.

So, if your clustered index is on the field ClientID, your sample queries would be

select clientname from tbl where ClientID = @x

select ClientID from tbl where clientname = @y

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 10

If you're paging, then you can do

select top 25 clientname from tbl order by clientname

for the first page. You can add a WHERE clause ot limit things as needed. Then for the next page, you need to pass in the last clientname

select top 25 clientname from tbl where Clientname > @x

order by clientname

and so on, passing in the first or last clientname, depending on which way you're going in the paging. You can also look up ROW_NUMBER in SQL Server 2005 if you move there and some other options become available.