SQL Server Performance

ASP Session Timeout / Stored procedure performance question

Discussion in 'Getting Started' started by inwoodpcpro, Oct 20, 2009.

  1. inwoodpcpro New Member

    Hello everyone. This is my first time posting so I'm posting to new section. If an admin wants to move this to a more approriate section that would be great. So here is my problem. I got put on project and was told to find out why a certain page is timing out. So i checked out the ASP code and it seemed the problem reffered to a stored procedure that was used to fill in the grid on the page. It uses stored procedure to populate record set.
    So i dig into the database to look at stored procedure. The database is filled with tons and tons of similar sounding table and procedures. Here is the procedure that seems to be causing trouble. I simplified it quite a bit just to explain my real question. My problem is the two innerjoins in middle. Does it make sense to inner join two tables of 500,000+ entries each in a stored procedure?
    SELECT
    q.name,
    qc.starttime,
    qc.elapsetime,
    cdrd.status,
    qc.id



    FROM qc (nolock)
    inner join cdrd (nolock) on cdrd.id = qc.id
    inner join q (nolock) on q.id = qc.idanswer
    WHERE qc.call_status = 1

    So I basically believe I understand what is happening here. But this too me seems very inefficient. I believe it is something like this. QC table is calls in queue. CDRD is call details. Each of these tables has a huge amount of entries 500,000+ each. The Q table is just used to pull the description name has only 30+ entries. It seems to me that sometimes joining two large tables causes the page to timeout.
    I dont have much experience with large databases at all but there are hundreds of similar stored procedures. Is this the right way to go about doing things?
    Any thoughts on the performance of stored procedures as it relates to the above example would be great also.

  2. Adriaan New Member

    The problem isn't so much in the inner join, that's something you definitely need and cannot avoid (otherwise you'd get totally unrelated details for calls).
    What is surprising is that the developer(s) never thought about paging the data. There is filtering on the call_status, but your current data may contain more rows with call_status = 1 than before, resulting in a larger recordsource for your grid.

Share This Page