Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
figmo
Starting Member
18 Posts |
 Posted - 2011-04-26 : 17:59:18
|
| SS08: Table has a customer name column that holds names like "Smith, John R" (Last, First, MI). Approx 60k records in the table99% of the time, this is fine. But there are one or two reports that requires the name to be reversed to "John R. Smith" e.g. mailing labels. Customer demands names be FIRST MI LAST for these.I have developed a scalar-valued function that flips the name correctly using some CHARINDEX and SUBSTRING calls. It's not overly complicated (2 CHARINDEX and 2 SUBSTRING calls and 2 LTRIM/RTRIM).When I use it my query times go from 2 secs to 19 secs. For this application - this is too slow. I understand that query time will increase and am willing to accept 5-8 secs. But almost 20 secs is not acceptable to the customer.Does anybody have any suggestions of a way to improve query times when calling a custom function such as this? Or is there a way I can do it straight from the query command without calling a custom function? (not sure if these are inherently slow or not)I'm considering just adding another column for the reversed name but I don't want to find and change all the code that adds/edits data to ensure they also update this new column.Ideas anyone? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-26 : 18:05:48
|
| You should be able to use the code in your function in-line with your query, depending on how you wrote it. If you post the function code and an example query we can fine-tune it for you.Can you redesign the table to separate firstname, lastname, and initial? It's much easier to combine them in a pattern than to parse or rearrange them. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-04-26 : 21:47:33
|
SQL Server 2008 ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-04-26 : 22:23:56
|
| You should be able to get the parsing of the column to be very fast. If the format is always LAST, FIRST M - and you don't have other possibilities (e.g. suffix/prefix like JR, SR, DR, etc...) that need to be parsed out, then you could create a 2 or 3 computed columns (and persist them if needed).Once you have the computed columns defined on the table, then you can easily access those columns for any reporting you need.Jeff |
|
|
|
|
|
|
|
|
Reverse Customer Name