locked
SQL Query -High CPU RRS feed

  • Question

  • User564383478 posted

    Hi,

    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
    FROM [DBANME].[dbo].[TABLENAME1]
    where ((Vis is null) or (Vis='') or (Vis='bis')) order by distance

    Wednesday, June 12, 2019 12:50 PM

Answers

All replies

  • User61956409 posted

    Hi jula,

    In your query, we can find SIN, COS and ACOS etc Mathematical Functions are using, which might cause the issue. 

    jula

    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.

    https://stackoverflow.com/questions/17551156/get-the-nearest-longitude-and-latitude-from-mssql-database-table

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 13, 2019 3:29 AM
  • User564383478 posted

    Dear  Friend,

    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))
    Thursday, June 13, 2019 10:02 AM