0

I am having a hard time understanding why my query takes too long when using like button using parameter. but when I hard code it it just takes 5 sec to execute but if using a parameter it takes too long 5 mins still not finished this is the query of the hard coded value.

select list, useralph26, count(*) total
from sgmc.q9a_p_sot sot (nolock), sgmc.q9a_p_cot cot
where sot.record_id = cot.record_id 
and suppression = 'NOT'
and response like '20180506%'
group by useralph26, list
order by list, useralph26

This query just runs fast just 5 secs

but when using parameter like I will show my query below

DECLARE @Rundate as datetime = '05/06/2018'
declare @tdate varchar(09);

--SET @tdate = '20180506%'
SELECT @tdate = CONVERT(varchar,@Rundate,112)

select list, useralph26, count(*) total
from sgmc.q9a_p_sot sot (nolock), sgmc.q9a_p_cot cot
where sot.record_id = cot.record_id 
and suppression = 'NOT'
and response like @tdate + '%'
group by useralph26, list
order by list, useralph26

This query running like forever I can't understand whats wrong The query results of response column is like this

20180506 AA E ER

I attached image of query result
Response column

CMinor
  • 397
  • 1
  • 5
  • 14
  • 5
    Just a doubt, is this `sql server` or `mysql`?? – Ricardo Pontual May 07 '18 at 11:59
  • 3
    Possible duplicate of [SQL Server Query: Fast with Literal but Slow with Variable](https://stackoverflow.com/questions/4459425/sql-server-query-fast-with-literal-but-slow-with-variable) – Ricardo Pontual May 07 '18 at 11:59
  • Probably problem optimizing the like with parameter. – jarlh May 07 '18 at 12:00
  • You can't apply `like` predicate in `date-time` column unless or until your `response` column has `varchar/string` type. – Yogesh Sharma May 07 '18 at 12:02
  • I would declare that @rundate as '20180506' (I assumed you meant May 6th). Apart from that I experience that issue when the query has datetime columns in it. My solution was to add "option (recompile)" to the query. – Cetin Basoz May 07 '18 at 12:10
  • I later noticed that you have an inner join there. Maybe that could be expressed in a better manner (5 seconds sound to be slow too - how many rows do you have). Could you give the structures and the need to join? – Cetin Basoz May 07 '18 at 12:16
  • Just update the comment of @RicardoPontual do the trick I just added the OPTION(RECOMPILE) at the end of the query and it runs just less than 1 min. I will still review why that happen in the link that he gave – CMinor May 07 '18 at 12:20
  • Oh OK. That was my suggestion too. I still wonder why you need to join (and after join if you really get the correct count you need). Under 1 min. still might be very slow. – Cetin Basoz May 07 '18 at 12:22
  • Why do you still use this ancient join style ? Start by switching to modern join style. Also you can put the `%`already inside your tdate variable. Last you should post the query plan without this its hard to tell what is going on here. – GuidoG May 07 '18 at 13:05

0 Answers0