Use a string or a number?

Question

Use a string or a number?

jixi

SSC Rookie

Points: 34
More actions
April 18, 2008 at 2:37 am

#379995

Hi all
I hope you can offer some advice with this :
We need to write perhaps a million URLs to a table and really need to allow 255 for them. Because we then need to search them we're thinking it will help performance if we convert the string to a number.
Is there a standard way of doing this or do we need to write our own little encoder?
If I'm barking up the wrong tree completely then please do tell!
Many thanks guys
Jix

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

While number searches can be faster than string searches, you'll probably loose all your gains during the encode/decode.

A million rows aren't much to sQL server, providing your query is well written and your indexes are properly designed for the queries.

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

GSquared SSC Guru Points: 260824 More actions · Answer 2

I don't think converting them to numbers would help. It might speed up the select, but then you're going to have to do two-way translation (to and from numbers), which will probably cost more than the gain.

As Gail says, 1-million rows isn't a big deal in SQL. It's more a question of table structure, the right indexes, and well-written queries.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jixi SSC Rookie Points: 34 More actions · Answer 3

Thanks for the replies.

The database and the queries are very, very simple so I don't expect any problems there.

However, we're talking about SQL Express and the database will be generally busy doing other stuff so I'm thinking perhaps a I'd be relieving workload on SQL server by doing the en/decoding? If it's going to be a close call then it's probably not worth doing the en/decode I guess.

Thanks again for the replies.