Post reply

Procedure vs Scalar function

  • February 25, 2016 at 2:58 am

    #308813

    Hi,

    I have 10 procedures that use dynamic SQL and have the same where clause, they return different columns and join with different tables.

    I was thinking of creating a procedure to return, as output, the where clause since it's the same for all 10 (every time I have to add a new restriction I have to add the where clause to all the 10 procedures)....

    Is it best to use a procedure or a scalar function for the porpoise?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • February 25, 2016 at 4:41 am

    #1860998

    What does the WHERE clause currently look like?

    LinkedIn

  • February 25, 2016 at 5:12 am

    #1861001

    It's irrelevant for the question...

    The where clause is a dynamic string cause the filter has 20 fields that can all have values or be null.


    If you need to work better, try working less...

  • February 25, 2016 at 5:32 am

    #1861003

    Made some tests with server tracing and the procedure is the fastest one, specially the first time it runs.

    Also less it has less reads, and the procedure has a big advantage I can't use inside the function: TRY CATCH block... I need to convert some variable to GEOGRAPHY type.

    Thanks


    If you need to work better, try working less...

  • February 25, 2016 at 5:41 am

    #1861005

    It's not really irrelevant if it helps someone make the most sensible suggestion. I would usually use a function, but there may be instances where a procedure is better. Your question doesn't tell me if this is one of those instances 🙂

    LinkedIn

  • February 25, 2016 at 5:47 am

    #1861007

    It's irrelevant since the only thing it does is build a where clause by concatenating...

    IF @idType IS NOT NULL AND LEN(@idType) > 0 SET @Query = @Query + ' AND idType = @idType'

    .....

    That's all it does

    Regards,

    Pedro


    If you need to work better, try working less...

  • February 25, 2016 at 5:56 am

    #1861011

    If you can get away with using a stored procedure then that's the way to go. Scalar UDFs are almost always poor performers and should be avoided at all costs.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • February 25, 2016 at 6:30 am

    #1861026

    I can only get away with the procedure due to the try catch i need 🙂


    If you need to work better, try working less...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply