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 |
|
mpolaiah
Starting Member
24 Posts |
 Posted - 2014-04-28 : 02:50:30
|
| Dear friends, my table have 100000000 records. i update the data with query like update ALL_MOBILE_DATA_PART set DistrictID=3 where FULL_ADDRESS like '%KARIMNAGAR%'it takes time 30 min .how can improve my query fast. |
|
|
WAmin
Starting Member
16 Posts |
Posted - 2014-04-28 : 06:20:24
|
| For 100 million records and wild card % on both side of search, 30 minutes seems very reasonable.Do you have cluster index on this table and any index on Full_Address? |
 |
|
|
mpolaiah
Starting Member
24 Posts |
Posted - 2014-04-28 : 06:27:38
|
| Yes..i am created non-cluster index on Full_Address and also one cluster index as primary key is created |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-28 : 08:35:37
|
| If you are not able to change your table structure, you may not be much you can do to speed up the query other than to get better/faster/more powerful hardware. As WAmin pointed out, when you have a where clause such as FULL_ADDRESS like '%KARIMNAGAR%', SQL Server has no choice but to read every record and look through the full address to see if it contains the string KARIMNAGAR. If you can restructure your table so the house number is in one column, street in another, city in another and so on, then you would be able to query using "WHERE City = 'KARIMNAGAR'". That would be much faster. |
|
|
|
mpolaiah
Starting Member
24 Posts |
Posted - 2014-04-29 : 00:23:07
|
| Thanks James... |
|
|
|
|
|
|
update query very slow sql server