I need help troubleshooting a performance issue. I have a stored procedure that is selecting from 3 joined tables, and is hanging on the first time this procedure is running.
Sometimes hanging so long that I get a timeout error. After the first run, it's faster, but still takes longer than it should.
On average it returns 3-4 rows, but it is selecting from hundreds of thousands. Any ideas on how to alleviate the first run timeout, and possible optimization tips?
What type of join are you using on the tables? Cross joins in particular can be very slow with so many records, are you able to change to inner or left/right outer joins? If not, you could try running three separate select statements and filtering down the result set each time.
If you aren't using a cross join, you might be able to try to step through your stored procedure to see where the hold up is. Open MSSQL Server Management Studio, and start a new EXEC query for your SP. Place a breakpoint and step into the procedure using F11. This might help to identify the section that is taking up the time.
Otherwise, can you post your procedure so we can see what is going on? We may be able to help by looking at it.
Unfortunately, because of privacy concerns, we can't post the SP. All the joins are inner joins. I'm running MSSQL 2008 r2, and for whatever reason, it won't let me step through the procedure. I'm familiar with the debug process your referring to, it's similar to what I use in VS for my ASP.NET and C# projects.
I'll try splitting it into separate queries before joining to see if that speeds things up.
I have witnessed that when SQL first starts, it's using 28MB of memory. After executing this procedure and one other procedure, it jumps to over 2GB of physical memory. Our app works beautifully after this, but if we try to run right after SQL starts, it hangs and lags and times out until after we get to the part that executes the SP.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.