How optimize query?

DECLARE

@page INT=-1, @size INT=-1, @search_query VARCHAR (50)=NULL, @genericSearch INT=1

--AS

IF (@page = -1)

BEGIN

SET @page = 1;

SET @size = (SELECT IIF (COUNT(1) > 0, COUNT(1), 1)

FROM user_device_location

WHERE is_active = 1);

END

BEGIN

WITH activeUserDetails

AS (SELECT udl.id,

udl.user_id,

u.FirstName,

u.LastName,

u.FirstName + ' ' + u.LastName AS user_full_name,

udl.mac_address,

udl.latitude,

udl.longitude,

udl.level,

udl.device_type,

udl.device_token,

udl.user_access_token,

udl.battery_level,

udl.is_active,

udl.added_by,

udl.added_on,

udl.modified_by,

udl.modified_on,

ROW_NUMBER() OVER (PARTITION BY udl.mac_address ORDER BY udl.added_on DESC) AS recencey

FROM user_device_location AS udl

INNER JOIN

users AS u

ON u.UserId = udl.user_id

)

SELECT aud.id,

aud.user_id,

aud.FirstName,

aud.LastName,

aud.user_full_name,

aud.mac_address,

aud.latitude,

aud.longitude,

aud.level,

aud.device_type,

aud.device_token,

aud.user_access_token,

aud.battery_level,

aud.is_active,

aud.added_by,

aud.added_on,

aud.modified_by,

aud.modified_on,

Count(*) OVER () AS total_rows

FROM activeUserDetails AS aud

WHERE aud.recencey = 1

AND aud.is_active = 1

AND ((@genericSearch = 1

AND (aud.FirstName LIKE '%' + isnull(@search_query, aud.FirstName) + '%'

OR aud.LastName LIKE '%' + isnull(@search_query, aud.LastName) + '%'

OR aud.device_type LIKE '%' + isnull(@search_query, aud.device_type) + '%'

OR aud.mac_address LIKE '%' + isnull(@search_query, aud.mac_address) + '%'

OR aud.user_full_name LIKE '%' + @search_query + '%'))

OR (@genericSearch <> 1

AND aud.FirstName LIKE '%' + isnull(@search_query, aud.FirstName) + '%'

OR aud.LastName LIKE '%' + isnull(@search_query, aud.LastName) + '%'

OR aud.device_type LIKE '%' + isnull(@search_query, aud.device_type) + '%'

OR aud.mac_address LIKE '%' + isnull(@search_query, aud.mac_address) + '%'

OR aud.user_full_name LIKE '%' + @search_query + '%'))

ORDER BY aud.id DESC

OFFSET ((@page - 1) * @size) ROWS FETCH NEXT @size ROWS ONLY;

END

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sourav Mukherjee7y ago
Hello, This is an example of CTE. I would advise to read through the way CTE and derived query works and check by extracting each section of the code. For optimizing query based way to rewrite the query and if the overall physical IO count or the execution count looks good, this is proved to be a great query!
Sreekanth Reddy7y ago
Hi Atul,

If you are new to "Execution Plan" :
Please have a look over the "Execution Plan" option by enabling it.

So that we will get the additional tab in your console which shows, where the cost is taking more.

So that you can directly workout on that portion.

For more details of finding "Execution Plan" refer the below URL:
https://www.tutorialspoint.com/ms_sql_server/ms_sql_server_execution_plans.htm

If it helps mark as answer.

Thank you.