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 |
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
 Posted - 2002-09-16 : 18:37:39
|
| I have a table with a field EmpName (Employee names) and I can't assume anything about the input. The proper format should be [lastname, firstname] however this is not guaranteed and there could be nulls or even just a comma.I would like this process to run as quickly as possible and at the same time no assume that the format is correct . So I was wondering if anyone sees any optimizations that could be made to the following code.SELECT FirstName = LTRIM(RIGHT(A.EmpName, LEN(A.EmpName) - CHARINDEX(',',A.EmpName))),LastName = REPLACE(LEFT(A.EmpName, CHARINDEX(',',A.EmpName)),',','')FROM dbo.Employees As A |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-09-17 : 03:50:28
|
| I think that I would try to fix the employees table so the name was held in two separate fields. Make your life easier. Other than that I'd probably try and get an ISNULL into that select somewhere so you don't get a list of folk called NULL. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-09-17 : 07:58:04
|
| One of your jobs as a DBA is to maintain data integrity. Clean and normalize your data on the way in and store only good data....Jay White{0} |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-09-17 : 19:02:07
|
quote:
One of your jobs as a DBA is to maintain data integrity. Clean and normalize your data on the way in and store only good data....
True page, but sometimes you inherit...and how  Valter, I can't see that there's anything particularly slow about your code...how big is your table?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-09-18 : 14:02:35
|
| I've modified the sql to be:SELECT FirstName = ISNULL(LTRIM(RIGHT(A.EmpName, LEN(A.EmpName) - CHARINDEX(',',A.EmpName))),''), LastName = ISNULL(REPLACE(LEFT(A.EmpName, CHARINDEX(',',A.EmpName)),',',''),'') FROM dbo.Employees As A The table is about 1500 records which gets three passes one for one for deletes, one for updates, and one for inserts.By the way I would like to hear any comments on the following strategy:I import data from an access database (I inherited it) into sql server into temp tables with the same structure as the access tables which I import.Next, I run delete queries which use joins to check which records no longer exist and then delete them. Next, I run the update queries and update every field no matter what. Finally, I run the insert queries (same strategy as delete).This takes about 15 seconds or so to run and it runs every fifteen minutes. I'm currently having problems with the transaction log andalso with triggers which are in place to audit single record by a front end interface.I would like to hear comments on the strategy and ideas on how to fix the transaction log problem and stop the triggers from firing during this update process. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-09-18 : 15:16:15
|
| sounds like something I just did for our online phone directory ... there are two text files that are exported to the server using NFM and these text files are then DTS'd into my tables after I clear them out ... I then run a merge module ... i don't know but I delete all the records from the merged table and just insert the new data that was in the CSV files... this takes 1-3 seconds ... probably ugly way of doing it but it only happens once every two weeks ... oh the resultant table after the merge is like 5k rows...I was going to search for deletes and inserts but i decided just to dump the table and re build it ... |
|
|
|
r937
Posting Yak Master
112 Posts |
Posted - 2002-09-18 : 16:17:54
|
| can't help with the trigger problem, but the three-pass strategy to delete, update, and insert is sound"update every field no matter what" could be revised to check only for changed rows -- this might eat some cycles, but you'd have fewer updates to logrudyhttp://rudy.ca/ |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-09-18 : 20:09:01
|
| Here is what I have:FirstName = ISNULL(LTRIM(RIGHT(A.EmpName, (DATALENGTH(A.EmpName)/2) - CHARINDEX(',',A.EmpName))),'')LastName = ISNULL(REPLACE(LEFT(A.EmpName, CHARINDEX(',',A.EmpName)),',',''),'')Replaced LEN() because it doesn't count blank spaces in front |
|
|
|
|
|
|
|
|
Optimized Split of Comma Delimited Name.