sql - Query optimization for convert VARBINARY to VARCHAR and charindex on it -

sql - Query optimization for convert VARBINARY to VARCHAR and charindex on it -

i have repository table has around 18.7 1000000 rows , every month around 500 one thousand 100 one thousand rows added. table construction follows

create table [dbo].[my_table]( [id] [bigint] null, [a_timestamp] [datetime] null, [eventid] [bigint] null, [userid] [varchar](255) null, [customerid] [varchar](128) null, [messagetype] [varchar](100) null, [message] [varbinary](max) null ) on [primary] textimage_on [primary]

i have written next query various counts each month. query takes around 10 minutes execute now. need help optimize query , if possible bring time couple of mins.

select dateadd(month, datediff(month, 0,a_timestamp), 0) monthyear, count(*) [count], count(distinct customerid) [unique customers], count(distinct userid) [unique users] [my_table] messagetype = 'outbound' , userid not in ('master', 'admin') , charindex('retrieve document',convert(varchar(max),[message])) > 1 grouping dateadd(month, datediff(month, 0,a_timestamp), 0) order monthyear

i think key reasons long execution time follows

charindex('retrieve document',convert(varchar(max),[message])) > 1 converting varbinary varchar , searching if 'retrieve document' userid not in ('master', 'admin') filtering users other users in list (the actual list longer 2 strings around 10 strings) 18.7 1000000 rows in table

a couple of points note

i don't create table , can't alter it i don't have showplan permission i need utilize query in excel info connections , have user run excel. user have select privileges.

given cannot alter existing table, may improve alter strategy. instead of running query , building new set of results every time. why don't insert new results table (lets phone call accumulatedresults) on monthly basis.

that way handling 500k new recs each time. much faster rebuilding entire result set every time. query little like:

insert accumulatedresults ( monthyear, [count], uniquecustomers, uniqueusers, ) select dateadd(month, datediff(month, 0, a_timestamp), 0) monthyear, count(*) [count], count(distinct customerid) [unique customers], count(distinct userid) [unique users] [my_table] messagetype = 'outbound' , userid not in ('master', 'admin') , charindex('retrieve document', convert(varchar(max), [message])) > 1 -- new status , dateadd(month, datediff(month, 0, a_timestamp), 0) > (select max(monthyear) accumulatedresults) grouping dateadd(month, datediff(month, 0, a_timestamp), 0)

sql sql-server performance sql-server-2012 varbinary