Last post Jun 13, 2019 10:02 AM by jula
Jun 12, 2019 12:50 PM|jula|LINK
The below query is eating 70% of CPU when running the application.I checked SQL(top queries CPU time) and found the below query is eating the high CPU .
In the below Latfinal and LongFinal are the variables and finding the nearest location from the TABLENAME1.
TABLENAME1 have nearly 2 lakhs records and each latfinal ,Longfinal is comparing with these 2 lakhs records for getting the nearest locationname (LocEn) from the table.
Earlier tablename1 has 50,000 records and no issues found. But now we added more records to 2lakhs and the issue happened.The below query is trying to execute more than 100 times in a second.I tried to add many indexing and not success with any. Can anyone
help me how to solve this issue?
SELECT distinct top(1)
((acos(sin(" & (LatFinal) & " *0.017453292500 )*sin(Latitude*0.017453292500 )+
cos(" & (LatFinal) & " *0.017453292500 )*cos(Latitude*0.017453292500 )*
cos((" & (LongFinal) & "-Longitude)*0.017453292500 )))*6371000) as Distance,
[Longitude], [Latitude], [ID], LocEn,LocSyn
where ((Vis is null) or (Vis='') or (Vis='bis')) order by distance
Jun 13, 2019 03:29 AM|Fei Han - MSFT|LINK
In your query, we can find SIN, COS and ACOS etc Mathematical Functions are using, which might cause the issue.
I tried to add many indexing and not success with any.
Can you clarify more about the index that you added on your table? And what is your expected %CPU?
Besides, this SO thread discussed similar question about getting nearest location, you can refer to it.
Jun 13, 2019 10:02 AM|jula|LINK
I solved my issue by changing the formula to below as mentioned in the link provided by u.
Thanks a lot.
SET @d = sqrt(square(@Lat1-@Lat2) + square(@Long1-@Long2))