Hi,

Ive got a class in my code which contains data from several tables. A user profile has a single record in a user_profile tables, for each of these records there is several records in a another table, which itself has several records in a tags table.

So far the save method on the class saves the record to the user profile table, then loops through the next two tables info to insert each record. I want to decrease the number of trips to the database and am wondering if anyone can advise on the best way to do this.

I am considering using a dataset to retrieve and store the records, instead of a datareader Im using currently. Then when Im done altering the data in the dataset (all three tables) I can commit all the changes in one database transaction?? I have been avoiding datasets due to the fact datareaders are apparently faster.

My other option is to try make three calls to the database, one per table, updating my stored procedures to accept arrays of data using XML??

Can anyone tell me what is the best option for me?


Thanks,

C 

Using a dataset won't inheritly decrease the amount of trips to the database.

Another approach would be to create a sql string that executes all the commands you want.  For example:

dim conn as new sqlconnection(ConfigurationManager.ConnectionStrings("ConnectString").ConnectionString)

dim cmd as new sqlcommand(MyClass.SaveSQL)

conn.open

cmd.executenonquery

conn.close

Assuming MyClass has two string properties col1 and col2, and a collection of tags called TagCollection:

Public Function SaveSQL() as string

dim sql as string

sql="INSERT INTO user_profile(col1,col2) VALUES ('" & col1 & "','" & col2 & "') " -- Note the trailing space in the quotes

for each tag in TagCollection

sql=sql & tag.SaveSQL

next

return sql

end function

And assuming your tag class has a tagname and tagvalue property, then add this function to your tag class:

Public Function SaveSQL() as string

dim sql as string

sql="INSERT INTO TagsTable(TagName,TagValue) VALUES ('" & tagname & "','" & tagvalue & "') " -- Note trailing space in the quotes

-- If there is a child collection, iterate and append their SQL values here

return sql

end function

If the tags table needs the id of the user_profile, then change MyClass SaveSQL like:

Public Function SaveSQL() as string

dim sql as string

sql="DECLARE @MyClassID int INSERT INTO user_profile(col1,col2) VALUES ('" & col1 & "','" & col2 & "') SET @MyClassID=SCOPE_IDENTITY() " -- Note the trailing space in the quotes

for each tag in TagCollection

sql=sql & tag.SaveSQL

next

return sql

end function

Public Function SaveSQL() as string

dim sql as string

sql="INSERT INTO TagsTable(TagName,TagValue,MyClass) VALUES ('" & tagname & "','" & tagvalue & "',@MyClassID) " -- Note trailing space in the quotes

-- If there is a child collection, iterate and append their SQL values here

return sql

end function

Of course, you should make sure all the parameters are properly cleaned before appending them in the SQL String by double single quoting any embedded single quotes, etc.

Of course, you could similiarly batch up commands to be sent to the database with a dataset as well if there are no identity columns involved.  There is a method on a dataset to return a collection of rows that meet your criteria like this:

Dim sql As String

sql = ""

sql = sql & "INSERT INTO Table1(col1,col2) VALUES ('" & r("col1") & "','" & r("col2") & "') " -- Note trailing space

Next

Thanks for the comprehesive reply. You say that a dataset wont reduce the number of trips to the database. Any chance you can explain this or point me in the direction of somewhere that does.

While on the point, is using a stored procedure that returns 3 seperate recordsets into a dataset, quicker than calling a reader 3 times with 3 different stored procedures?

Im gonna build a string like you advised to commit all the changes to the database, is there any difference in using a long sql string, or XML to pass it to the database?

Yes, when you commit the changes to the database via a dataset (using the built in dataadapters, etc).  What will happen the background is that it will do this (psuedo code):

For each t as datatable in dataset

  for each r as datarow in t.GetChanges(Added)

    Execute the sqldataadapter's insert command using r as the parameters

  next

  for each r as datarow in t.GetChanges(Updated)

    Execute the sqldataadapter's update command using r as the parameters

  next

  for each r as datarow in t.GetChanges(Deleted)

    Execute the sqldataadapter's delete command using r as the parameters

  next

next

So if you've added 10 rows, updated 5, and deleted 4, then you'll see 10 inserts, 5 updates, and 4 delete commands going to the database and being executed one by one (Each one being a round-trip).  By building up the SQL string, you tell sql server to execute that single command (batch) in a single round-trip instead of the 10+5+4.  If you have a high latency between your application and your database server, this can make a HUGE difference.

 -- Disclaimer: It might not be that exact order (Adds, then updates, then deletes), but it does do them one at a time.

As for 3 separate recordsets into a dataset, possibly.  Depends on the load on the client and the latency between the client and server.  If the performance is that important to you, I would recommend setting up a test environment that mimicks the different loads you want to tune for.

As to whether there is a difference between a long sql string or XML... I guess that depends on how you prepare and parse the XML.  XML isn't necessarily a bad choice either, especially considering how easy it should be to create (I do believe its a call on a dataset, so you should be able to generate your insert XML via ds.GetChanges(Added).GetXML, or even possibly just passing the whole dataset to the SQL Server)  I use a lot of different database engines other than SQL Server, and the way they handle XML (if at all) is vastly different.  Creating simple Insert/update/deletes are pretty much the same for most database engines, so I tend to go that route even if the XML way might have been easier/faster.

I'm afraid the answer I gave above isn't completely accurate.  The pseudo code is more like:

For each r  as datarow in dataset.Table(0).GetChanges()

  if r.rowstate=added then

    execute the insert

  else if r.rowstate=modified then

    execute the update

  else if r.rowstate=deleted then

    execute the delete

  end if

next

And that only holds completely true if the batch size is set to 1.  If the batch size is >1, then it does appear to go into some logic that attempts to batch up the commands and I assume will execute them in sets of the batch size, but I haven't verified it.