SQL Procedure - insert record repeating, slowing procedure

[rzirpolo]

Without going into to much detail I am wondering if anybody has come across any issues running Stored Procedures compiled in SQL Server 7 via another PC which has SQL Server 2000 tools. The database being used is a SQL Server 7 database by the way.

I have a problem that when running a procedure from Query Analyzer on my PC (SQL Server 2000 tools) the procedure takes an abnormal time. It keeps processing an insert to a table on the same row of data. The procedure then takes a total of approx 4 hours (it should only take 20-25 mins max). But it does eventually complete which is also strange.

Now.. if I log onto the PC remotely using PC Anywhere, then run the procedure from Query Analyzer (SQL Server 7 tools) it takes approx 20 mins. I can't understand it.

All my initial investigation has been revolved around the data but there is nothing suspicious here at all. I have been thinking about what other factors could contribute to this issue but I can confirm the login details, rights, privileges are exactly the same. The only differences are the platforms as I have detailed above.

If anybody has had similar issues or has any other ideas I would be very grateful.

N.B. SQL Profiler\Trace didn't help at all.

 

[SQLSister]

What on earth are you doing that even the normal insert takes 20-25 minutes? If you have a cursor in there, get rid of it.

Without knowing what tasks are being done by the SP it's a little hard to say what could be causing the problem.


Questions about posting. See faq183-874

 

[rzirpolo]

Before you have a go at me... I inherited this procedure from my predecessor. I agree it needs re-compiling big time but with the amount that it does this is by no means a small process.

My general question is if anybody has come across issues running a SQL Server 7 compiled procedure from another PC using SQL Server 2000.

I'm looking more along problems environmentally than functions executed in the Stored Procedure.

 

[leveyp]

When you use PC Anywhere you are executing the SP on the remote machine. When you exec the SP on your PC you will be pulling/pushing data around your network. Bound to be slower. I had the same problem working on a clients site when their machine was in an outsourcing facility.