Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raniel
Starting Member
3 Posts |
 Posted - 2005-12-27 : 22:14:08
|
| Good Day... To start things off, I wrote a program using visual basic 6... and my backend was ms access 2000, one procedure in my program takes 19 mins to complete... then we migrate it to sql server 2000 msde (operating system -WindowsXP pro)... without changing any parts of the code except for the connection of course... then, it is now almost an hour to complete!!... after optimizing the code and changing it all to stored procedures nothing have changed (1 hour still).. i dont know what to do, im in trouble... if you find my explanation unclear. i can email to you my source code and my databaseany help guys?? thanks in advance. <sorry for my incorrect grammar> |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2005-12-27 : 22:49:26
|
| Hi,post your stored procedure |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-28 : 02:09:47
|
| What does your sp do?Post the code used at spYou need to make use of index to speed up the processMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-28 : 10:56:14
|
| If there is a lot of "chatter" between your application and the database then perhaps consider checking if the database is set to AUTOCLOSE - and if so un-setting that.Kristen |
|
|
|
raniel
Starting Member
3 Posts |
Posted - 2005-12-30 : 23:46:06
|
| hi,actually my stored proc is just simple, using the sql wizard. is there any way aside from indexing to speed up the process? my code goes like this..do while myrecordset.eof recordset2.open "select from mytable where id=5" if recordset2.eof recordset2.addnew recordset2!field1=a recordset2!field2=a endif recordset2!field3=a recordset2!field4=a recordset2.update . . . .loopnow, ive changed the way i add to a recordset by using stored procedures. this must work faster than the previous one.gcnAccess.Execute "exec aSP_rsBMAccounts '" & rsLoadBM.Fields("BranchCode") & _ "' , '" & rsLoadBM.Fields("AcctCodeBM") & "'"access=19minssql=60mins, sometimes 10 hrs to complete. omgthanks. happy holidays |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-31 : 10:55:45
|
| Can you do ALL the processing Server Side, rather than having a loop Client Side? That's typically the "key" to dramatically improving performance - i.e. by using Set Based logic, rather than the sort of "procedural logic" that you tend to get in client-side applicationsKristen |
|
|
|
|
|
|
|
|
sql -any help???