Stored Procedures: Não é somente best-pratices, é necessidade!

Um cliente meu, me pediu para corrigir um modulo asp que ele tem em seu sistema, que tem como função adicionar usuário. Além de criar o usuário, existe uma copia de perfil, que consiste basicamente em copia de registros SQL (entre 80 e 200 registros). A reclamação do meu cliente, era que essa criação de registro estava levando em torno de 10 - 12 minutos para ser executado. Uma eternidade para uma aplicação web.

Quando abri o código para analisar, encontrei algo assim:

sSQL = "SELECT NEWSLETTER_SUBSCRIPTION_ID,NEWSLETTER_SUBSCRIPTION.SUBSCRIPTION_END_DATE, NEWSLETTER_SUBSCRIPTION.SUBSCRIPTION_START_DATE, NEWSLETTER_SUBSCRIPTION.SUBSCRIPTION_MONTH FROM NEWSLETTER_SUBSCRIPTION INNER JOIN ORGANIZATION_CONTACT ON ORGANIZATION_CONTACT.CONTACT_ID = NEWSLETTER_SUBSCRIPTION.CONTACT_ID WHERE ORGANIZATION_CONTACT.ORGANIZATION_ID=" & session("OrgID")
rsTemp.Open sSQL, conn,1,3
set rsTemp2=server.CreateObject("ADODB.Recordset")
while Not rsTemp.eof
start_date = rsTemp("SUBSCRIPTION_START_DATE")
subscription_id = rsTemp("NEWSLETTER_SUBSCRIPTION_ID") + 1
end_date = rsTemp("SUBSCRIPTION_END_DATE")
subscription_month = rsTemp("SUBSCRIPTION_MONTH")
tSQL = "INSERT INTO NEWSLETTER_SUBSCRIPTION(NEWSLETTER_SUBSCRIPTION_ID,SUBSCRIPTION_START_DATE,SUBSCRIPTION_END_DATE,SUBSCRIPTION_MONTH) VALUES(" & subscription_id & nUSERID &"," & subscription_start_date & "," & subscription_end_date & ",'" & subscription_month & "')"
rsTemp2.Open tSQL, conn,1,3
Wend

Um select, percorre-se todos recordsets e se faz um novo insert com os dados do select. Isso funciona, para 5 registros, 10 registros, mas para aplicações onde o volume de dados é alto, isso acaba se tornando um problema.

Solução: através de uma simples stored procedure usando tabelas temporarias, o tempo de execução, caiu de 10 - 12 minutos para 3 segundos :-)

Codigo da stored procedure:

USE [bowersp_testdb]
GO
/****** Object: StoredProcedure [bowersp_testuser].[setnews] Script Date: 07/19/2008 09:49:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <18/06/2008>
-- Description:
-- =============================================
ALTER PROCEDURE [bowersp_testuser].[setnews]
@OrgID int,
@UserID int
AS
CREATE TABLE #TEMPTABLE (CONTACT_ID INT,NEWSLETTER_SUBSCRIPTION_ID INT,SUBSCRIPTION_END_DATE datetime, SUBSCRIPTION_START_DATE datetime, SUBSCRIPTION_MONTH varchar(10),NEWSLETTER_ID varchar(5))

INSERT INTO #TEMPTABLE(CONTACT_ID,NEWSLETTER_SUBSCRIPTION_ID,SUBSCRIPTION_END_DATE, SUBSCRIPTION_START_DATE, SUBSCRIPTION_MONTH,NEWSLETTER_ID) SELECT NEWSLETTER_SUBSCRIPTION.CONTACT_ID,NEWSLETTER_SUBSCRIPTION_ID,NEWSLETTER_SUBSCRIPTION.SUBSCRIPTION_END_DATE, NEWSLETTER_SUBSCRIPTION.SUBSCRIPTION_START_DATE, NEWSLETTER_SUBSCRIPTION.SUBSCRIPTION_MONTH,NEWSLETTER_SUBSCRIPTION.NEWSLETTER_ID FROM NEWSLETTER_SUBSCRIPTION INNER JOIN ORGANIZATION_CONTACT ON ORGANIZATION_CONTACT.CONTACT_ID = NEWSLETTER_SUBSCRIPTION.CONTACT_ID WHERE ORGANIZATION_CONTACT.ORGANIZATION_ID=@OrgID

UPDATE #TEMPTABLE SET CONTACT_ID = @UserID

INSERT INTO NEWSLETTER_SUBSCRIPTION(CONTACT_ID,NEWSLETTER_SUBSCRIPTION_ID,SUBSCRIPTION_END_DATE, SUBSCRIPTION_START_DATE, SUBSCRIPTION_MONTH,NEWSLETTER_ID) SELECT CONTACT_ID,NEWSLETTER_SUBSCRIPTION_ID,SUBSCRIPTION_END_DATE, SUBSCRIPTION_START_DATE, SUBSCRIPTION_MONTH,NEWSLETTER_ID FROM #TEMPTABLE

Claro que passou pela minha cabeça, tirar todo código asp (45p 5uck5, L1nux Rul3z) e colocar o mod_security, mas isso é papo para outra conversa :-PPPPP