Hi There,

1)
I need to search on multiple tables and the data from 20 TO 70GB

Since the data is pretty big I found out the best thing is to use a dynamic sql statement where I define on the fly the parameters to be used. I can NOT use a view with constant number of parameters since it will be too great of an impact on the execution plan.

I am trying to see how can I use a BO under this condition. For instant if the first sql gets 0 records, then in the same SP I will pursue with another SQL statement to grab from different set of tables.

How do you assign a BO to this kind of process.

2)
I need to use some audit trail. Like if the user enters FirstName, LastName and State for search purpose. I need to enter that as a record for an audit trail and then the results of the search as well. It could be up 100 records. Obviously that could be as a simple Insert.

How is the audit trail is coming along in the SF? Is that something that I can use?

Regards,

Doron

1. This is exactly where you would create a stored procedure to return a result set (or more) back to one or more BOs.  The issue here is the search.  If you want to traverse large data sets with high response, you are going to have to use a number of things in conjunction to make this happen.  But the issue is not on the BO side, as you will just call a stored procedure to fill it.  The issue is creating the query/queries necessary to accept the parameter(s) and then efficiently execute the search.

If you will recall, Ben gave a nice session during the training class on SQL Server Optimization and SPROCS.  This is exactly where that lesson comes in very nicely.

As for the BO, you can map it to a pre-existing table or view or even create custom properties.  In most cases that I have ever dealt with, you are generally searching data that represents more than one table in this type of scenario which may split into more than one table.  This is fine, you would just make your SPROC return more than one result set and then populate the BOs in question.  This is a great place to use the BusinessLayer.FillMultipleDataTables method.

2. You are going to want and do this server side.  The SF auditing is mostly in place, but the issue here if you are trying to do verbose auditing is that you are going to impede performance.  So in this case, you are probably beter off creating triggers on the SQL side to automatically handle the UPDATE/DELETEs to place the changes into your auditing table.  You will want to be careful here too and just keep an eye on optimization so that things don't slow down server side.

Hi Trent,

Thanks for your reply. 

I need to show the resulting data from the attached store procedure in a SF listview, I would like to use one single BO, but as you can see the SP is getting its data from 4 different tables, so I don't know how would I map the single BO to all those fields in the resulting SP cursor, it is this possible or is there any other approach I could use?

FYI, these are big tables with millions of records, so I must use the SP.

Regards,

Doron

Why do you need this to be a single BO?

Hi Trent,

Because Doron will be using a ListView to show the results, can we use multiple BO sources in a ListView and display fields from each BO in the ListView?

Also, would it be too much of overhead to have 4 BO to show the ListView result?

Edhy Rijo

Hi Trent,

If you look that SP that was attached before it covers 6 tables (for the 2 sql statements) and I need to add more sql statements from other tables as well so this way maybe 12 tables will get involved. If one search in the first data set gets 0 records then it goes to next SQL statement and so forth...

Hundreds of users will be logged into that server and perform search, and then I need constantly to reload too many BOs for one search at time not to mention the over head with all these BOs.

I know that you reduced the foot print of each BO but that also is a concern as well.
I would rather do that in one BO...

Regards,

Doron

 

Do you want to update back to the server with this same result set?

Hi Trent,

When it comes to search no need to update the server. I will need to create some SPs that will do as follows: If the user enters First, Last and State and the search yield 100 records which is the Max.

I will store the one record in table which is called: SearchValues which is the parent where I enter the values that the user entered initially, and then in the child will store only the pk of each found record and the table name. I haven't tried that yet, but that's the idea...

That's it when it comes to search.

I will have many types of search and this link may get you the idea: http://www.PeopleFinders.com

Regards,

Doron

Great!  So my logic is exactly what you want to do then as this is something that we do every day.  We have hundreds of places within our medical software that does this very thing.  It relates to a JOINed query.  At this point, it is just a matter of creating the query, and then within the list, enter the name of the field in the column that you want to show.  You don't even have to create custom properties in this case.  The column name just has to be within the result set to prevent an error when the ListView tries to reference that column.  Another option, if you so choose, is to create custom properties that will expose these columns.  But this is something that we do all of the time within our applications and is very common practice.

I am still a bit confused about how to build the listview to show the data from the query.  Do you mean that when building the listview instead of using {Index} to set the value we have to enter the Field Name of the returned query? or we have to do this in any of the listview event manually?

Edhy Rijo