I have a commercial management system and a much used module is the agenda. The programming (.net + Sql) worked very well, but now the table has about 1M of registers, and it is getting almost impractical to use the tool. The number of concurrent users is around 70.
Information:
Bank: SQL Web 2008
Table: 1 million records
Users: 70 concurrent
Server: Amazon Ec2 c3.xlarge (4vCPU of 2Ghz, and 8GB RAM)
O select:
select pf.prof_id,pf.prof_nome,[dbo].[FN_AGENDA](pf.prof_id,'2016-10-18') from profissionais pf where pf.prof_ativo=1 and pf.prof_unidade=0 order by pf.prof_nome
And the function FN_AGENDA:
BEGIN
DECLARE @concatValues varchar(Max);
SET @concatValues ='';
SELECT @concatValues = @concatValues
+ CAST(ag.agenda_hr_inicio as varchar(5))
+ ';' + CAST(ag.agenda_hr_termino as varchar(5))
+ ';'+ CAST(ag.agenda_id as varchar(12))
+ ';'+ CAST(ag.agenda_status as varchar(2))
+ ';'+ CAST(ag.agenda_comanda as varchar(10))
+ ';'+ CAST(cli.cliente_nome as varchar(28))
+ ';'+ CAST(sv.servico_nome as varchar(30))
+ ';'+ CAST(Coalesce(cm.comanda_status,3) as varchar(1))
+ ';'+ CAST(Coalesce(cm.comanda_codigo,0) as varchar(10))
+ '|'
from agenda ag
inner join clientes cli on (cli.cliente_id=ag.agenda_cliente)
inner join servicos sv on (sv.servico_id=ag.agenda_servico)
left join comandas cm on (cm.comanda_id=ag.agenda_comanda)
where
ag.agenda_profissional = @id_sala and
ag.agenda_data=@data and
ag.agenda_status<>5
;
RETURN(@concatValues)
END
As I said, everything was fine so far, where the "calendar" table has reached 1M records, and is very slow. In performance tests, the query takes 1 minute!
Much of this is due to the use of the CPU, which at various times reaches 100% use. The average is 80%. I honestly do not think the problem is on Amazon, and I also do not know if you really need to extend the vCPU number of the instance.
What else can be done? Change the syntax? Increase the number of vCPU? Abandon everything and sell popsicles on the beach?