0

In a huge sql server table which is having 2,00,000 records, does the query like below should be fine considering performance perspective. Please provide your suggestions to improvise this query.

INSERT INTO @Results (Name, URL, MSA)    
select top 10 Name, URL, MSA from NearByCity where NearByCityId in
(select NearByCityId from dbo.NearByCity where MSA like '%Atlanta-Sandy Springs-Roswell, GA Metropolitan Statistical Area%'  )   
order by newid()
Uttam Kumar Roy
  • 2,060
  • 4
  • 23
  • 29
Maheswari Thiagarajan
  • 1
  • Why not just use one select instead of two and use the where condition in the second query in the first itself ( the 'like' clause )? – Ravindra HV May 17 '16 at 20:48
  • if both tables named 'NearbyCity' are in same schema (I am asking because in only one query you mentioned username. and not in other) then why can't you merge the queries. IMO, 200K records is not a huge number for SQL server table. your query should do fine given all stats are in place. – Nachiket Kate May 18 '16 at 05:42
  • Thanks for all your response on this request. The references are for the same schema. Used two queries because hav learnt newid() would degrade performance as the table grows & cost of key scan. http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table I cannot use where clause along with percent query like, select * from [yourtable] where [yourPk] in (select top 10 percent [yourPk] from [yourtable] order by newid()) So I narrowed down the resultset and then applied newid(). Please advise, if I can correct it or my perspective isn't right. – Maheswari Thiagarajan May 18 '16 at 17:36

0 Answers0