How can I improve a proc fcmp?

[zetitas]

Hello,

I passed a sql function to sas code and everything is ok, but in SQL it takes a few seconds and when I load my sas code It takes more than hour. I tried a lot of things to impruve it but anything it was ok. I don´t know what can I do more and for that reason write here. Thank you. 

I use Enterprise Guide 7.15 HF2 (7.100.5.6112) (64-bit).

I attach 3 programs: First, the create calls the function document, and this calls to the MacroSelecionCalle.

The sql function is 

ALTER FUNCTION [dbo].[SeleccionaCalle_NEW]
(
-- Add the parameters for the function here
@ano char(4),
@ArticleNr_unv int,
--@Padre sysname,
@Grade sysname,
@TipoColor sysname,
@Standard sysname,
@VolumenIBP float
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Calle int --@Calle=0 indica que no se ha encontrado calle

-- Declaración de resto de varaibles
DECLARE @MaxRegla int -- Total de reglas existentes
DECLARE @Orden int -- Cursor de regla evaluada (de 1 a @MaxRegla)
DECLARE @CalleRegla int -- Calle a la que aplica la regla evaluada

--- inicializa variables
SET @Calle=0; -- sólo si cumple alguna regla, se cambia el valor
SET @MaxRegla= (SELECT MAX(RG.Orden) FROM dbo.PT_T_SC_ReglasCalle AS RG WHERE [Enabled] = 1 and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ReglasCalle] where @ano <= Año)); -- Menor prioridad.
SET @Orden=1; -- Mayor prioridad
SET @CalleRegla = (SELECT RG.Calle FROM dbo.PT_T_SC_ReglasCalle AS RG WHERE Orden=@Orden and [Enabled] = 1 and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ReglasCalle] where @ano <= Año));
SET @TipoColor = LTRIM(@TipoColor)

-- iterar hasta que una calle cumpla o se acaben las reglas.

WHILE @Orden <= @MaxRegla and @Calle=0
BEGIN
-- Verificar si existe el padre
--IF (select COUNT(*) from dbo.PT_T_SC_ListaPadres where Calle=@CalleRegla and Padre=@Padre and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ListaPadres] where @ano <= Año))>0
IF (select COUNT(*) from dbo.PT_T_SC_ListaPadres where Calle=@CalleRegla and articleNr_unv=@ArticleNr_unv and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ListaPadres] where @ano <= Año))>0
BEGIN
-- Existe un Padre producido con anteriordad en esta calle
-- Es Standard?
-- 13/01/2014 DaniReseco: Saco los comentarios de la validacion de standard y volumen
-- email Rocio 13/01/2014
IF @Standard='Standard'
SET @Calle = @CalleRegla
ELSE
-- No es standard
-- Es superior al Volumen mínimo?
IF (SELECT RG.LotMin FROM dbo.PT_T_SC_ReglasCalle AS RG WHERE Orden=@Orden and [Enabled] = 1 and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ReglasCalle] where @ano <= Año))<=@VolumenIBP SET @Calle=@CalleRegla
END
ELSE
-- Este Padre no existe en esta regla de calle
BEGIN
-- Verificar si Existe Grade asignado a la calle
IF (select COUNT(*) from dbo.PT_T_SC_ListaGrades where Calle=@CalleRegla and Grade=@Grade and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ListaGrades] where @ano <= Año))>0
BEGIN
-- Existe grade disponible en esta calle
-- Validar si Cualquier TipoColor
IF (SELECT RG.AllTipoColor FROM dbo.PT_T_SC_ReglasCalle AS RG WHERE Orden=@Orden and [Enabled] = 1 and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ReglasCalle] where @ano <= Año))=-1
BEGIN
-- Cualquier tipo Color
-- Validar si es standard
IF @Standard='Standard' SET @Calle = @CalleRegla
ELSE
-- No es standard
-- Es superior al Volumen mínimo?
IF (SELECT RG.LotMin FROM dbo.PT_T_SC_ReglasCalle AS RG WHERE Orden=@Orden and [Enabled] = 1 and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ReglasCalle] where @ano <= Año))<=@VolumenIBP SET @Calle=@CalleRegla
END
ELSE
-- No está marcado cualquier TipoColor
-- Validar si existe TipoColor en esta Calle
IF (select COUNT(*) from dbo.PT_T_SC_ListaTipoColor where Calle=@CalleRegla and TipoColor=@TipoColor and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ListaTipoColor] where @ano <= Año))>0
BEGIN
-- Existe Este tipoColor en esta Calle
-- Validar si es standard
IF @Standard='Standard' SET @Calle = @CalleRegla
ELSE
-- No es standard
-- Es superior al Volumen mínimo?
IF (SELECT RG.LotMin FROM dbo.PT_T_SC_ReglasCalle AS RG WHERE Orden=@Orden and [Enabled] = 1 and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ReglasCalle] where @ano <= Año))<=@VolumenIBP SET @Calle=@CalleRegla
END
END
END
SET @Orden=@Orden+1
SET @CalleRegla = (SELECT RG.Calle FROM dbo.PT_T_SC_ReglasCalle AS RG WHERE Orden=@Orden and [Enabled] = 1 and Año = (SELECT min([Año]) FROM [PricingTool].[dbo].[PT_T_SC_ReglasCalle] where @ano <= Año))
END
-- Return the result of the function
RETURN @Calle;

END

[RW9 · Accepted solution]

I am sorry, I can't even ascertain what the question is from this mass of impossible to read text and attached files?  

I suppose one place to start is why you are creating compiled procedures in the first place?  Personally I find compiled procedures to be a bad thing, first your hiding code elsewhere, then compiling it so that its not available to the user and in a propretary file format which likely wont work in the future.

If your working with a database, then use some simple SQL steps to extract into SAS the data you want to work with.  Once it is in SAS then use Base SAS programming to work with the data.  Passing processing backwards and forwards, and using compiled SAS procedures to run SQL based queries is bound to take more time and just creates a complex web of relationships.  

[zetitas]

I´ll think about it and I´ll try to work with SAS data. Thank you!!!!

[ArtC]

I am not sure that i understand your question, but i can see in your DATA step that you call your FCMP function.  That function calls your macro and that macro (M_SeleccionaCalle), which in turn executes a number of data reads (potentially lots of I/O).  All of this I/O will happen for every observation in the original incoming data set (PT_V_NEW_SALES_LOTE_ORDER_TEMP1).  Without understanding your intent/objective/logic, my first thought is to try to minimize all of this I/O.  Can you restructure so that the macro (M_SeleccionaCalle) is called only once instead of N times?

[zetitas]

I´ll think about it and I´ll try to work with less I/O. Thank you very much!!!!

[Tom]

Can you explain what you are trying to do? 

Looking at the first SAS program it seems that you want to do something then calle=999.

Is that some type of missing value code?

Are you attempting to replace the 999 with some derived value?

If so what is the logic for deriving the replacement value?  Can you just calculate the replacement value in advance and then conditionally use it?

[zetitas]

It´s a some type of missing value code that I replace with 999 when is missing. And for each 999value I have to call the function to return a correct value. The problem is the function does a lot of consults to db sql. I took every tables and I passed them to work library and It´s take 5 minutes!! Already everything is alright and thank you for answer me!