Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Stored Proc returns in 0 sec, but app gives time out error

    I am having this issue where the stored procedure looks good, it returns data in 0 seconds. When I call the proc from Visual Studio, it takes a long time or times out completely. Here's what I've noticed so far:

    If I recompile the stored procedure, VS will return the data as expected. Update statistics did not help.
    I have narrowed the issue down to a few tables because the same problem exists with another stored procedure that joins these same tables.
    Tables are joined on primary key = foreign key. (nonclustered index on pk)
    Joins result in table scan even though the tables are joined on keys.

    Here's the query, any help would be greatly appreciated:

    SELECT DISTINCT TCBF.Flightnumber
    FROM tblCBillFlight TCBF
    INNER JOIN tblCBillFlightInfo TCBFI
    ON TCBF.CBillFlight_PK = TCBFI.FK_Cbillflight
    JOIN tblCBillClass TCBC
    ON TCBFI.CBillFlightInfo_PK= TCBC.FK_CBillFlightInfo
    WHERE TCBF.FK_KitchenID = @FK_KitchenID
    AND 'y' =
    CASE
    WHEN TCBF.FK_CustomerID = @FK_CustomerID THEN 'y'
    WHEN @FK_CustomerID = -1 THEN 'y'
    ELSE 'n'
    END
    AND TCBFI.FlightDate BETWEEN @FromDate and @ToDate
    AND ISCALCULATED = 'TRUE'
    ORDER BY TCBF.Flightnumber

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have you checked to see if something else in the application is blocking the select query?

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    Do you mean like another query running at the same time that would cause blocking?
    I have not tried looking for blocking as the app is in development right now, so there would be no other queries running by another user.

  4. #4
    Join Date
    Sep 2009
    Posts
    4

    Fixed

    I added an index to the foreign keys and the application is performing better.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're using optional parameters, right?
    Code:
    AND    'Y' = CASE WHEN TCBF.FK_CustomerID = @FK_CustomerID THEN
                        'Y'
                      WHEN @FK_CustomerID = -1 THEN
                        'Y'
                      ELSE
                        'N'
                      END
    This will cause a scan everytime, regardless of the value passed to the function.
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2009
    Posts
    4
    Even when I take the case statement out and just run a select from table where someColWithIndex = @Something I get a table scan.
    If I take the variable away and hard code it, it hits the index. But that is too narrow. I need stuff like date between @Start and @End and a few other filters in my query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •