Post reply

Multple If Statements with slow performance

  • October 23, 2006 at 6:10 am

    #667325

    Ken,

     The datatypes for the table as well as the view are varchar(10).

     

     

    Andrew and Ian I give both your suggestions a try and post the results when I'm done.

    Thanks.

  • October 23, 2006 at 7:28 am

    #667343

    In QA, if

    select * from v_ORder where ShipmentStatus = 'Live'

    takes 2 secs to run, how long does:

    DECLARE @test-2 varchar(10)

    SET @test-2 = 'Live'

    SELECT *

    FROM v_ORder

    WHERE ShipmentStatus = @test-2

    take to run?

     

  • October 23, 2006 at 8:39 am

    #667380

    Ken,

    They both take the same amount of time.

  • October 23, 2006 at 8:58 am

    #667384

    OK. Does running the following SP run like

    EXEC dbo.Test @pShipmentStatus = 'Live'

    still take 22 secs?

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE dbo.Test

     @pOrderStatus VARCHAR(10) = ''

     ,@pShipmentStatus VARCHAR(10) = ''

    AS

    SET NOCOUNT ON

    DECLARE @OrderStatus VARCHAR(10)

     ,@ShipmentStatus VARCHAR(10)

    SELECT @OrderStatus = @pOrderStatus

     ,@ShipmentStatus = @pShipmentStatus

    IF @OrderStatus = '' AND @ShipmentStatus <> ''

     SELECT *

     FROM v_Order

     WHERE ShipmentStatus = @ShipmentStatus

    IF @ShipmentStatus = '' AND @OrderStatus <>''

     SELECT *

     FROM v_Order

     WHERE OrderStatus = @OrderStatus

    GO

  • October 23, 2006 at 10:27 am

    #667414

    Ken,

    Your onto something. I just created that SP and tested it against "select * from v_order where ShipmentStatus= 'Live' "  and the SP blew away that select statement. There is about a 10 second difference between them.

     

  • October 23, 2006 at 10:32 am

    #667415

    How long did the select statement take and how long did the SP take?

  • October 23, 2006 at 10:37 am

    #667418

    For this paticular status the SP took 2 secs the SELECT Statement took 12.

  • October 23, 2006 at 10:41 am

    #667419

    That sounds like caching. Run them both a couple of times and then compare performance.

  • October 23, 2006 at 12:16 pm

    #667436

    Yeah if I keep doing it they both run about the same.

  • October 23, 2006 at 4:38 pm

    #667471

    Did you manage to compare execution plans?

    _____________
    Code for TallyGenerator

Viewing 10 posts - 16 through 24 (of 24 total)

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