February 8, 2009 at 10:43 pm
Hi,
I have a one time stored procedure to excute that inserts data on some of the newly added columns in several tables depending on join from different tables. This stored procedure uses cursor to perform this data migration.
This cursor contains multiple select and print statements. (giving idea what data have been moved)
If I connect to the test database server using sql server management studio installed on my machine (sql server authentication), it generally takes 12 hours to execute.
Will it make any difference if I directly execute the same stored procedure directly on the test server (using sql serevr management studio on test server) instead of executing the same from my machine?
Here what i am assuming is there may be delay in the execution due to print and select statements that transfer data from server to my machine. If i directly execute it on server, it may avoid this delay (saving network bandwidth) causing less execution time.
I am using SQL Server 2005 enterprise edition (SP 2).
February 9, 2009 at 6:57 am
You may see some performance gain by running on the server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 9, 2009 at 7:14 am
You might see a slight improvement running directly on the server. Won't be much, but it might be a little.
Would it be worthwhile to post the proc and table definitions? We might be able to make it take significantly less than 12 hours.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 1:10 pm
Usually network overhead is very little (negligible) unless the amount of data is very large.
* Noel
February 9, 2009 at 1:28 pm
viduvs (2/8/2009)
This stored procedure uses cursor to perform this data migration.
That's possibly one of the reasons for the slowness. Why a cursor?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2009 at 6:29 am
Thanks for all the replies.
Actually this is one time activity. So we don't want to pay much attention towards optimization of this stored procedure.
But in case if it is possible to reduce excution time significantly by executing it directly on the server, so we were looking for this solution.
February 10, 2009 at 10:54 pm
viduvs (2/10/2009)
Thanks for all the replies.Actually this is one time activity. So we don't want to pay much attention towards optimization of this stored procedure.
Like Gail said, that's probably why it takes 12 hours...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2009 at 11:18 pm
viduvs (2/10/2009)
But in case if it is possible to reduce excution time significantly by executing it directly on the server, so we were looking for this solution.
Tricks like that might save you a second, probably less. If you want it faster, you're going to have to optimise your code.
SQL Queries run on the server anyway. It's not like Access where all the data is pulled client-side for processing. Management studio's just an interface, the work is all done by the SQL engine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply