How to check if a comma seperated column contains the desired word
-
dwain.c (5/22/2012)
Jeff Moden (5/22/2012)
It's all going to suck for performance. Don't store comma separated data in a database. Split the data out and store it correctly.If there are CPU cycles available and no one to use them, would anyone notice a performance-sucking SQL statement? :w00t:
I wasn't going to say anything else about this problem because such words typically fall on ears made deaf by the noise of meeting a schedule.
To answer your question, absolutely not. And that's why this problem won't be fixed until it becomes a real problem. It's also very typical. It's the reason why performance consultants can charge so much. Instead of fixing a problem while it's small and easier to fix, people wait until it becomes a major performance problem on several critical systems. 😀
Of course, you have to ask yourself if this is really so bad even then. I mean, what the hell... it's perfectly acceptable to store highly complex, highly denormalized, even hierarchical data in a table in the form of XML. How is comma delimited data any worse?
Of course, that's also why I generally don't allow XML in any of my databases. 😀
--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) -
Of course, that's also why I generally don't allow XML in any of my databases. 😀
Amen to that - praise be to edgar F codd and Raymond F Boyce!!!!!
MVDBA
-
Jeff Moden (5/23/2012)
Of course, that's also why I generally don't allow XML in any of my databases. 😀I wish I had the authority to enforce that sort of rule
dwain.c (5/22/2012)
Cadavre - Definitely not ticked off at you and didn't mean to sound that way. It's just that I am saddened by the frequency with which I'm confronted with such poor design and the same lame excuse.It's a shame, but often I find that those excuses are true.
In one company I've worked for there was a legacy app that stored items in a table in a comma-deliminated list. I ended up changing the underlying structure of the database and creating sprocs that returned the data in the way that the app was expecting. Then I changed the app code to target the sprocs instead of the tables. So the tables were normalised, but the app thought that it wasn't. Of course, I was given no time to make these changes so had to do it all in my own time. However, once it was complete I managed to get rid of a lot of errors that we were having in reporting from the data so the time and stress it saved me in my work time was such that I didn't mind too much.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
To answer your question, absolutely not. And that's why this problem won't be fixed until it becomes a real problem. It's also very typical. It's the reason why performance consultants can charge so much. Instead of fixing a problem while it's small and easier to fix, people wait until it becomes a major performance problem on several critical systems. 😀
SEE CARTOON TESTER
MVDBA
-
Jeff Moden (5/23/2012)
dwain.c (5/22/2012)
Jeff Moden (5/22/2012)
It's all going to suck for performance. Don't store comma separated data in a database. Split the data out and store it correctly.If there are CPU cycles available and no one to use them, would anyone notice a performance-sucking SQL statement? :w00t:
I wasn't going to say anything else about this problem because such words typically fall on ears made deaf by the noise of meeting a schedule.
I knew that question, while completely rhetorical, would get a rise out of somebody.
It's the reason why performance consultants can charge so much.
Hehe. I now have a new target to shoot at.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (5/23/2012)
Jeff Moden (5/23/2012)
dwain.c (5/22/2012)
Jeff Moden (5/22/2012)
It's all going to suck for performance. Don't store comma separated data in a database. Split the data out and store it correctly.If there are CPU cycles available and no one to use them, would anyone notice a performance-sucking SQL statement? :w00t:
I wasn't going to say anything else about this problem because such words typically fall on ears made deaf by the noise of meeting a schedule.
I knew that question, while completely rhetorical, would get a rise out of somebody.
It's the reason why performance consultants can charge so much.
Hehe. I now have a new target to shoot at.
Heh... us fulltime performance employees do alright, as well. And we don't have to scare up work. There's a constant supply of slow and broken. 🙂
--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) -
dwain.c (5/23/2012)
I knew that question, while completely rhetorical, would get a rise out of somebody.p.s. You have an interesting and non-rhetorical manner of asking rhethorical questions. 😛
--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)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply