Updating text valued(varchar) column with better performance

Question

Updating text valued(varchar) column with better performance

bvsc

Ten Centuries

Points: 1258
More actions
August 8, 2011 at 12:32 pm

#255471

I have around 15 columns in the table..
In that two columns has varchar(6000) data type.
Here the texe valued columns update very frequently.
But I know It should be lack of performance while updating the text valued column than integer valued column
So Can some one please help me here to solve th issue.

Viewing 15 posts - 1 through 15 (of 21 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

What kind of data gets stored in those columns?

- 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

bvsc Ten Centuries Points: 1258 More actions · Answer 2

bvsc

Ten Centuries

Points: 1258

August 8, 2011 at 12:35 pm

#1365458

It just usernames

Lowell SSC Guru Points: 323518 More actions · Answer 3

6000 is pretty wide for a single username...is it a comma delimited list of names, or just an oversized column?

if you do max(datalength(ThatColumn)), what's the largest current value?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bvsc Ten Centuries Points: 1258 More actions · Answer 4

Thank you for the quick response.

There are multiple names stored there with comma delimiter and the max length of the column is 512 It would be more in future.

pietlinden SSC Guru Points: 63512 More actions · Answer 5

I would normalize and create a child table for the names. Then you can set the size to something sensible, like VARCHAR(25) or smaller for FirstName and LastName columns.

GSquared SSC Guru Points: 260824 More actions · Answer 6

Definitely split it out into a sub-table. That'll make it a lot easier to work with, and it will be faster to update, query, etc.

- 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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

As suggested you need to normalize your data and you need a unique identified as a primary key for there could be more than one person with the same name.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

bvsc Ten Centuries Points: 1258 More actions · Answer 8

I agree all of you. I am sorry might not explain you properly.. Please follow this.

The scenario...

We will get the info. in the main page who visited us(like Facebook)

The same way I have one column for my sake..and two for visitors as like below

Create table visitors(

Username varchar(20),

visitors varchar(6000),

visitorsThrough varchar(6000),

crDate datetime,

UpDate datetime)

and also have 10 other columns which related to the usernames..

in the process of the flow, That table have updates, inserts and few deletes.

And I have more updates on those two columns visitors and visitorsThrough.

eventually the data is growing in that column.. It should be hard to update further.

Ofcourse we can do that but as matter of time.

Now the question is : Can I get better performance on this scenario while applying

something(other possibilities-If we have) to that table...

The least case I prefer is normalization.. I know it will work But it requires some other things like joins..

Any other solution Please

I really appreciate it.

pietlinden SSC Guru Points: 63512 More actions · Answer 9

Maybe I don't get it, but are you saying "I understand that I should normalize, but I don't want to."? It would be easier to delete a single child record or a few child records (visitors/linked visitors) than it would be to update a single column and remove random values from a delimited list. If you wanted to do any kind of analysis on the "connection" between people, that would be almost impossible with your proposed structure and stupid easy if you split that column out into (Foreign Key, VisitorName) or something similar.

That's how I would do it... normalizing just gives you MUCH more flexibility and ease of use, I think. your DML statements would be super simple and would not need updating almost ever. (unless you have radical redesign)... Up to you, though.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Create table visitors(

Username varchar(20),

visitors varchar(6000),

visitorsThrough varchar(6000),

crDate datetime,

UpDate datetime)

and also have 10 other columns which related to the usernames..

in the process of the flow, That table have updates, inserts and few deletes

You are going to create a single column for User Name?

How about a First Name, Last Name and a User Name?

What is the definition of visitors and visitorThrough and why are they defined as varchar(6000)?

What about the 10 other columns related to usernames?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

bvsc (8/8/2011)
I agree all of you. I am sorry might not explain you properly.. Please follow this.
... snip ...
The least case I prefer is normalization.. I know it will work But it requires some other things like joins..
Any other solution Please
I really appreciate it.

SQL is joins. It's optimization, index usage, and statistical data retrieval. The engine is built around these concepts. If you're looking to ignore the reason to use the data engine, those of us who use it really can't help you. Take it to the code, delimited strings work better there.

You're asking us how do you get the system to work better when you want to use it against the design. I'm sure a few of us could figure it out, but I'm pretty sure none of us want to. There are rare, special occassions to have to work with data like this at the storage level. I deal with it constantly as proc parameters, but that's a different story. In each case, it takes a lot of work to 'buck the system' to get it to do what you need it to.

If you don't like joins, you're in the wrong software. Might I recommend a flat datafile storage like DB2 or csv files?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 12

I think normalizing is your least costly fix to the performance issue. Taking the time up front to do the database right will save more time than you could imagine.

You mentioned the JOINs that will be required. That is simply how the database works and I don't understand the issues that would make using JOINs an undesirable thing.

GSquared SSC Guru Points: 260824 More actions · Answer 13

The only good solution will be to normalize. You could get some very slight performance improvement out of doing your string manipulations at the application layer instead of in the database, but it will be tiny compared to the benefits of normalizing.

- 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

bvsc Ten Centuries Points: 1258 More actions · Answer 14

Hi Evil Kraig F,

I am sorry If I made you to understand differently..

I am saying I would do normalization as well as joins..It is the only option which I can do as per my knowledge..

I just thought of double checking with you guys, If we have any other possibilities as we have lot of new concepts adding these days.

Thanks for your response.