asked on

query optimization

how could i improve below query performance .in test server it takes more than 2 minutes .In prod busy server( millions of rows ) it will take more time to execute .any good thoghts would help .

DECLARE @Date varchar(10) = '3/16/2017';
DECLARE @Client varchar(50) = 'logix';
SELECT count(*) AS 'SSN Checks'
FROM vwEvent
WHERE EventType = 'SigningPing'
AND EventDate = @Date
AND EventParam4 = @Client
AND EventParam2 like '%/NameSSN/%'

thanks all in advance

Jim Horn

For starters, post the T-SQL for view vwEvent into this question

>DECLARE @Date varchar(10) = '3/16/2017';
Bad developer. Very bad. Why are we storing dates as a varchar? This at minimum forces implicit conversion which adds to query processing time.

>AND EventParam2 like '%/NameSSN/%'
Lose the LIKE comparison if possible, as these by definition are slow.

MD SHAMIM

ASKER

thank you .could you please make a change to the query .what should i use for date instead of varchar ? are you suggesting something like

AND EventParam2 like 'NameSSN/%' ?

Jim Horn

>what should i use for date instead of varchar ?
Use date. This assumes that the EventDate column in your view is a date data type.

>are you suggesting something like .. AND EventParam2 like 'NameSSN/%' ?
I'm suggesting not using LIKE and a wildcard % of any kind if that is possible.

MD SHAMIM

ASKER

do you think below query is better than original .and any other suggestion please ?

DECLARE @Date date = '3/16/2017';
DECLARE @Client varchar(50) = 'logix';

SELECT count(*) AS 'SSN Checks' FROM vwEvent
WHERE EventType= 'SigningPing'
AND EventDate = @Date
AND EventParam4 = @Client
AND EventParam2 = 'NameSSN'

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MD SHAMIM

ASKER

after all conversation with JIm Horn got my solution .thank you GURU