Re: Procedure Inconsistent Performance

[Mel G]

Hi Peter,

When I run the select statements directly, the big ones take around 8 seconds each on a consistent basis. Considering that they're querying tables that are 125000 and 25000 records respectively, I find that an acceptable performance. To get one row of output, it takes me around 20 seconds manually.

However, the problem begins when I put these selects/inserts into a procedure. The performance suffers a bit. I still find it tolerable because I'm doing this for personal research and not implementing for an organization. However, when the procedure takes 20 minutes or more and still doesn't complete, that's when performance is an issue to me.

Thank you for the notes on the queries. I had clearly overlooked the group by and I much appreciate the feedback there. Also, on the join syntax, I will consider separating this into 2 queries.

However, I'm still quite puzzled - why is the performance inconsistent?

Do you have any feedback on the structuring of the procedure itself - leaving the queries aside?

Thank you.