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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-31 : 09:55:15
|
| VIJAY writes "hi I am Vijay, I have a task where I have to move a recordset/a series of rows from front end (visual basic) to the database, sql server 7.0, is there a way that I can pass a recordset as an input parameter to the stored procedure. the problem is if I move record by record to the stored procedure say to update or insert a reocord to a table on a database, I have to connect so many times to the database, I am looking for an option where I can move all the data(bunch of records) at one go to the database, and do all the looping on the database side." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-31 : 10:03:13
|
| Can you post your code Vijay? We'll need some more background on EXACTLY what you're doing with the rows you're processing.I don't think you'll need to pass an entire recordset back to the server. You can probably get by with passing row identifiers only. If you are going to pass multiple rows back to the server for processing, DO NOT USE a server-side cursor.You could pass a recordset as a comma-separated value (CSV) string value to a stored procedure, and then have the SP split the rows out. I don't think that's the best way to go, but these articles will help if you decide to do it that way:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-31 : 10:11:46
|
| You only need to connect to the database once then all the updates can be made on that connection.Look at disconnected recordsets.You can create a recordset, populate it then connect to the database and all the updates will then be made on the table. I have tried this and it works but I didn't look at what the impact on the server was. Try using the profiler to see what is actually going on.You can do a similar thing with XML.The simplest solution is usually the best - which would be repeated calls t oan insert SP.How quick do you really need this to be.Note that whatever you do you will have to transfer the data to the server.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
vijbasrao
Starting Member
1 Post |
Posted - 2002-02-04 : 11:47:14
|
| Thanks for your response,hi robvolk, about posting my code, its not possible so I can tell you the algorithm, for each header record (one to 5 headers) for each detail record (detail recrds can be 100 to 500 and up to 3 types) each reacord has to be either save/updated to the database, currentlly depending on the no of records and data, some times it takes up to one minute, since these are online screens in visual basic, I wanted to speed them up no to take more than 20 to 25 seconds, I believe your suggestion of "could pass a recordset as a comma-separated value (CSV) string value to a stored procedure, and then have the SP split the rows out." best suits the problem, because I can send the data ("of a full record") at one go to the server instead of several parameters (fields in each record), but my no of hits to the database remains the same.hi nr, Thanks for your idea of using disconnected recordset, due to the processing involved and other business logics, I have to go for a stored procedure only. And your suggestion of "- which would be repeated calls t oan insert SP.", is what in production currently, I am planning to improve on that, that is the reason for changing it.Thanks againRegards,Vijay.A.Vijaya Bhaskar Rao |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-04 : 12:54:30
|
Here's a variation on the CSV idea that I used for a project. It uses sequence tables, so check these articles for more background on them:http://www.sqlteam.com/item.asp?ItemID=5857http://www.sqlteam.com/item.asp?ItemID=2652Instead of a CSV, I'll use fixed-length data in a large varchar parameter. The reason is that I'm going to pass several fixed-length parameters, one for each column being inserted. Each column will have a maximum data length, and the data needs to be padded to ensure each value is the same length. If you passed multiple CSV values like this, you'll have a major headache splitting them in one query and making sure each row was inserted properly.Here's a sample table:CREATE TABLE CartoonCharacters (firstName varchar(15),lastName varchar(20),gender varchar(6),age tinyint)Here's the data I want to INSERT:firstName lastName gender ageFred Flintstone male 40Wilma Flintstone female 35Barney Rubble male 35Betty Rubble female 30Pebbles Flinstone female 1 I'll combine each value for firstName into a single string, with each value padded to 15 characters:DECLARE @firstnames varchar(8000)@firstnames='Fred Wilma Barney Betty Pebbles ' The padded spaces at the very end of the string are not entirely necessary, but you should include them anyway. I'll do the same thing for each column:DECLARE @firstnames varchar(8000), @lastnames varchar(8000), @genders varchar(8000), @ages varchar(8000)SELECT @firstnames='Fred Wilma Barney Betty Pebbles ',@lastnames='Flintstone Flintstone Rubble Rubble Flintstone ', @genders='male femalemale femalefemale',@ages='40 35 35 30 1 ' I'm using a 3 digit string for each age. These values might need a conversion to make them a string value. Once the strings are formatted, a stored procedure like this will split them out and INSERT them:CREATE PROCEDURE InsertCartoonCharacters @firstNames varchar(8000), @lastNames varchar(8000), @genders varchar(8000), @ages varchar(8000) ASINSERT INTO CartoonCharacters (firstName, lastName, gender, age)SELECT Rtrim(SubString(@firstNames, (seq-1)*15+1, 15)),Rtrim(SubString(@lastNames, (seq-1)*20+1, 20)),Rtrim(SubString(@genders, (seq-1)*6+1, 6)),CONVERT(tinyint, SubString(@ages, (seq-1)*3+1, 3))FROM SequenceWHERE seq Between 1 AND 300 --change the last number if you need to INSERT more than 300 rows at a time And then you run it like this (once for each header record, not each detail):EXECUTE InsertCartoonCharacters@firstnames='Fred Wilma Barney Betty Pebbles ',@lastnames='Flintstone Flintstone Rubble Rubble Flintstone ', @genders='male femalemale femalefemale',@ages='40 35 35 30 1 ' The nice thing about using fixed-length values is that it's easy to calculate how many you can pass at one time (8000/length = # of rows inserted). VB allows declaring fixed-length string variables, so it's easy to convert a variable-length string to a fixed length. You could even have your VB program call this SP multiple times if there are more detail rows than can be accommodated with one call.Unfortunately I can't say that this is a great way to do it because it will require a lot of modification to your VB code. I was able to use this without much hassle because I was using JavaScript and had the values in an array (they were already fixed-length) and simply used the join method to convert them to a string. You will probably have to use some kind of loop with string concatenation in VB (not very efficient). If you are navigating an ADO recordset to get values then it will be even slower. You could use GetRows to convert a recordset to an array, and then process the array (yes, this WILL be faster than using MoveNext).Hopefully this will give you some ideas to pursue and could turn out to be a viable solution. Good luck! |
|
|
|
|
|
|
|
|
moving a recordset/cursor from front end to the database