January 14, 2004 at 8:22 am
Hi everyone,
Thanks to you all for your suggestions for making my VB / SQL Server application faster - I've implemented some of the suggestions, and it's definitely made a difference, but it's still slow. I have 2 questions today:
1) I've started created stored procedures instead of creating my recordsets through ADO code - I created a SP that selected my fields (and ordered by ID), then called it with the follwing code:
Set mydb = New ADODB.Connection
mydb.Mode = adModeReadWrite
mydb.Open sConn
cmd.ActiveConnection = mydb
cmd.CommandText = "select_orderID_tblaudit_1"
cmd.CommandType = adCmdStoredProc
Set datAudit = New ADODB.Recordset
Set datAudit = cmd.Execute
But whenever I try to movelast in the recordset, I get an error "Rowset does not support fetching backward." - any ideas why this is happening?
2) I'm thinking now that some of the slowness may be caused my my server hardware - our server is only for testing and development, and it is a Pentium III, 1133 MHz, 256 megs of ram - not a lot for a server. Just running the OS and SQL Server takes up most of the RAM! DO you think this could be a cause of the slowness?
Thanks for all your help, folks - having this forum really is a Godsend for me!!!
Christy 🙂
January 14, 2004 at 2:59 pm
You need to decide how you intend to read/update the data. If you are doing this is VB.net see a tool like LLBLGEN (http://www.sd.nl/software/) If you are using VB 6 see http://users.quista.net/thealey/download.htm (CodeGEN) for some samples on how to do this. I'm pretty sure your problem #1 has to do with with cursor type when you open the recordset (The default is "adOpenStatic" which is a disconnected recordset. Records added/modified/deleted to the DB at the same time by another user/process are not accessible in this cursor type.) Both the tools I mentions will generate both Stored procs and VB code to call these procs which is great even if only used for sample code.
As for question 2 256 M RAM is really borderline I would definitely ask for a bigger box or least a substantial memory upgrade (3-6 times what you have). Use the Memory counters in Performance Monitor to check for bottlenecks but I can almost guarantee that more memory will get you better performance.
Francis
January 19, 2004 at 6:24 am
SQL Server is more perfect when you Add more RAMS ... and notice that SQL Server uses ALL the Rams that you give to the server... Also you have to add (with (NoLock)) in Stored procedures when you read a value .. so you don't make a lock on the table or the row ... I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply