multi-table BO question

Keith Chisarik — Mon, 01 Mar 2010 08:54:18 GMT

Since I need to have a view to map my business objects to when pulling data from many tables, what (if any) is the advantage of populating via a SPROC versus using the view I already have defined for mapping?

I like (in theory) not having to keep the SPROC and views in sync. In admittedly limited testing performance seems equal.

Thanks in advance for your opinions, as always.

Method A (mapped to view, populate via SPROC, passing in parameters):

[quote]

Public Sub FillBySprocGetRosterValues(ByVal p_rosterPK As Integer)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "dbo.sdsi_GetRosterValues"

'-- Create the parms

cmd.Parameters.AddWithValue("@rosterPK", p_rosterPK).SqlDbType = SqlDbType.Int

'-- Return the results

Me.FillDataTable(cmd)

End Sub

[/quote]

Method 2 (Map to view, populate BO using the view):

[quote]

Public Sub FillBySessionPK(ByVal p_sessionPK As Integer)

'-- Establish locals

Dim loCommand As New SqlCommand()

'-- Build the query

loCommand.CommandText = "SELECT * FROM vwAllAcademiesBySession WHERE sxa_session_fk = @param1"

'-- Add the parameter

loCommand.Parameters.Add("@param1", SqlDbType.Int)

loCommand.Parameters("@param1").Value = p_sessionPK

'-- Execute the command to fill the business object

Me.FillDataTable(loCommand)

End Sub

[/quote]

RE: multi-table BO question

Trent L. Taylor — Mon, 01 Mar 2010 08:54:18 GMT

Sorry I missed this post, Keith.

Well, in short, speed. There can be other advantages and disadvantages of either as well depending on your circumstances. Recently, like all last week, we spent a lot of time tweaking queries on one of our online servers that calculates insurance formulary results for a patient when retrieving drug results in our prescription writer. This database is so massive and changes daily that we actually keep this on central online servers and query through web services. Not only that, we may query millions of records, across dozens of tables, etc. Through this process, we had some views that were part of some of these embedded queries that were killing us (keep in mind that 500 ms [half a second] would be an extremely long query time here). We tried indexed views, but still, no joy. So be back off, made direct table queries with properly placed indexes after living in the Execution Plans for a while.

The Execution Plan plays a big part here as well in regards to how well a view may perform (not to mention the number of records). When all was said an done, the query went from 400 ms down to 12 ms. This is a MAJOR improvement and when you are talking about recursive queries and a large number of transactions, this makes a big difference.

Don't get me wrong, we still use views and they are super handy. But if you are in a situation where performance is a must and you are dealing with large numbers of records, then a view [u]may[/u] slow things down....not always...but it is always something I keep an eye on.

In short, there is nothing wrong with either approach if you are getting the performance and results that you are looking for. :)

StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » multi-table BO question

multi-table BO question

RE: multi-table BO question