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 |
|
tskmjk
Starting Member
11 Posts |
 Posted - 2009-11-04 : 08:00:28
|
| I am joining two tables Phone numbers and STD Codes and I have to take care of around 12 phone numbers (residence, office, acquired) for a domestic project. Only the numbers which are less than 10 and more than 1 should be appended std codes that too land line.I have the following query:update master as a JOIN std as b ON a.resi_city=b.city SET a.resi_phone2=concat(b.std_code,a.resi_phone2) where char_length(a.resi_phone2)>1 and char_length(a.resi_phone2)<10 and a.resi_phone2 <>'' and calltablename=<Calltablename> and calltablename1=<calltablename1>I have around 12 statements like this and its taking long time. Is there any another method to achieve this faster? |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2009-11-04 : 10:10:42
|
| Give the table structure, and why you have 12 such query what are you updating?========================================Project Manager who loves to code.===============Ask to your self before u ask someone |
 |
|
|
tskmjk
Starting Member
11 Posts |
Posted - 2009-11-05 : 00:50:55
|
| Thanks for replying. This is actually a data massaging tool where a raw excel sheet is read and the excel sheet has the following columns and with sample data:Loan Num POS MonDue Bkt_Td EMI No_EMI_due Del_String Agency Supervisor ID Last Disposition Code Res_Phone1 Res_Phone2 Res_Phone3 Off_Phone1 Off_Phone2 Off_Phone3 Mobile Resi_City Off_City Pin RegNo STD QueueID Reason of Bounce TZ CAN1 CAN2 CAN3 CAN4 CAN5 CAN6123 23434 0 1 22167 1 517 8066172350 8066172386 9986444092 HYDERABAD HYDERABAD 500049 IN NSTRB1 INSUFFICIENT FUNDS 1 124 23435 0 1 22168 1 517 8066172351 8066172387 9986444093 HYDERABAD HYDERABAD 500050 IN NSTRB1 INSUFFICIENT FUNDS 1 In the above upload map, following columns will have phone numbers Res_Phone1 Res_Phone2 Res_Phone3 Off_Phone1 Off_Phone2 Off_Phone3 Mobile CAN1 CAN2 CAN3 CAN4 CAN5 CAN6If std code is appended its length will be 10 digit so thats already taken care. My code should take care of numbers whose length is less than 10 and more than 1 (that means STD code is not appended). I am executing twelve update queries for the above columns. Kindly suggest me as this execution of the queries is taking 8 minutes for 2500 records? |
|
|
|
|
|
|
How to optimize the query?