Tips for optimizing performance?? - VB/SQL Server
-
I think you're struggling thru the pains of "connected" or "disconnected".
6-7 years ago, most apps were "connected" over the local network. So when you were editing a record, you had a connection over the network that was always "on".
Because of the internet, which is a disconnected state.. meaning. i load a web page (connected), once hte page is up, i'm disconnected. if I make data changes, I then reconnect and reestablish connnection to do updates.
Because of this. You would make one procedure for reading the data from the db. Then .. you would make edits to (most times) one record, and then send your data back (as args to a) stored procedure, and that procedure would update the db. If you were worried about 2 people editing a record at the same time, you had to add a little magic to check for this.
This is why .NET created an object called a DataSet. Which is ... the golden nugget to alot of developers since it was released.
You're kinda at a weird place. Your not at dot net. So you're using a little older technology, trying to do things alot of people were doing 4-5 years ago, and you might get conflicting advice at times. and software development is a maturing process.
Are you saying your need is to be able to put multiple records on a page,and edit them at the same time? Like a grid layout? That's a slightly different gameplan.
And I don't remember,is your backend db() sql server 2000? I have a "new" way to update multiple records at the same time using a stored procedure, and passing in the information as xml. Mine is dataset.getXML() (for those reading this post), but it could be altered slightly. This was taught to me by another developer, and its kinda my bread and butter now. I created a generic sample, I might post it.
Someone else will have to comment on doing multiple record updates in VB6. I never did that.
-
This example works against the pubs db().
This will allow you to update 1:N (1 or many many) records.
This is sql server 2000 ONLY (or later naturally)
Procedure 1:
if exists (select * from sysobjects
where id = object_id('dbo.usp_insert_pubs_authors') and sysstat & 0xf = 4)
drop procedure dbo.usp_insert_pubs_authors
GO
CREATE PROCEDURE usp_insert_pubs_authors (
@xml_doc TEXT )
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @storesinsert TABLE (
stor_id varchar(16) ,
stor_name varchar(16) ,
stor_address varchar(16) ,
city varchar(16) ,
state varchar(16) ,
zip varchar(16)
)
INSERT @storesinsert
SELECT
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
OPENXML (@hdoc, '/StoresDS/store', 2) WITH (
stor_id varchar(16) ,
stor_name varchar(16) ,
stor_address varchar(16) ,
city varchar(16) ,
state varchar(16) ,
zip varchar(16)
)
-- temp select just to show all the data in the @variabletable
select * from @storesinsert
BEGIN TRANSACTION
--insert into the stores table ( a "real" table) .. (from) the @variabletable (which now contains all the dataset data)
Insert into stores
(
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
)
SELECT
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
FROM @storesinsert
IF @@ERROR <> 0
BEGIN
RAISERROR('error updating database', 16, 1)
ROLLBACK Transaction
END
COMMIT TRAN
GO
Procedure 2
(just paste into query analyser, it only calls the above procedure)
--Here is the call. The input for the t-sql proc is
--text, but tsql doesn't allow localized text parameters
--so varchar is used here (for @xml_doc_temp).
--The xml string (below) follows what would be a typed dataset xml
--While here, it is manually created, the procedure would
--probably be called using the myTypeDataset.getXML() as the input parameter
--Naturally,the xml can contain 0, 1 or N number of "stores" for
--insertion (2 stores are used here)
declare @xml_doc_temp varchar(8000)
select @xml_doc_temp =
'
<StoresDS>
<store>
<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>
<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>
<stor_address>456 Hickory</stor_address>
<city>Charlotte</city>
<state>NC</state>
<zip>44444</zip>
</store>
<store>
<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>
<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>
<stor_address>456 Main</stor_address>
<city>Charlotte</city>
<state>NC</state>
<zip>33333</zip>
</store>
</StoresDS>
'
print @xml_doc_temp
EXEC usp_insert_pubs_authors @xml_doc_temp
GO
select * from stores order by stor_name
-
THanks, Steve - no, I only display one record on my form at a time, so I only need to be able to edit one at a time.
But I need to be able to use movefirst and movelast because my users have buttons on their forms that allow them to scroll between records. If they click the "Last Record" button, my code has to be able to say recordset.movelast.
But if I'm using a stored procedure to populate my recordset, which I want to do to speed up my application, the default cursor type won't allow me to use the movelast method.
Understand? I'm so confused by all of this haha!
Thanks!
Christy
-
Ok. (btw, my name is Sloan)
What I did for this was have a form with a "Search" button.
This would populate (for ease, lets say a list box), with the Nameof (the time), and would naturally have its id as the index property.
Then I would have 3 buttons to the side "Add" Edit "Delete".
Youre kinda in a "Access forms" frame of mind, with those buttons
<< < > >> (at the bottom).
My suggest is to move away from that line of thinking.
The search page, will also allow you to setup filters. So.. if you have emps in departs, you could put a dept combbox on that form, and then restrict the results.
If its a VB forms app, then when you click "Edit", the form will show modal(ly), and you make edits, hit "ok", and save the record using the stored procedure we talked about.
The listbox in the search page, doesn't have to be refreshed. the user just picks another records and can go and edit it.
Here is the flow:
Main Screen will have a menu item for "Manage Employees".
Manage Employee screen will have a:
listbox (empty at first)
search button
dept combo box
add, edit, and delete buttons.
with or without picking a dept, the user will pick "search", and it will populate the listbox. the stored procedure for this will be "select empid, lastname + ', ' + firstname as fullname from emps"
(and a where deptid = x) if the use picks a dept.
this will populate the listbox. user picks a listbox item.. and then hits "edit".
make data changes. hit "ok". this calls the BUSINESS OBJECT which will call the stored procedure.
..
Make sense?
Others may disagree. But the "Access paging" thing is ..not the way to go in my mind. Because it forces a "always connected" mindset. And.. 2 years from now , if you go web development, web development is a "disconntected" mindset.
..
-
Thanks, Sloan! (got your name right this time - sorry about that!)
The difference with my application is that when my main form loads, I need to move to the end of the recordset (calling recordset.movelast), so that the user can see the last record added. For editing, a search feature won't really work because they often don't know something needs to be edited until they are scrolling through. I have another form where the user can filter the records, so your logic would probably work there.
But unfortunately, I don't think it will work on my main form.
I do understand the problem with connected recordsets though - I just don't know the best way to deal with the problem as it stands in my application.
Christy
-
Christy,
What Sloan has said is good advice. In your case what you might do is take his approach and modify it slightly. Take the data that he suggests you put in the combo box and put it in an array. Once you have it in the array you can then use it to go from record to record. To go to the last record you simply move to the last entry in your array then use the ID stored in the Array to call the SP to look up the data for the record.
I would also place the combo box on your form so that the user can use it to move from record to record instead of the buttons. In my experience once the users get used to using it they will almost never use the record buttons.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply