Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> table variable
 

Pages: 1
LLBLGen Pro Runtime Framework
table variable
Page:1/1 

  Print all messages in this thread  
Poster Message
mohamed
User



Location:
Salmia, kuwait
Joined on:
10-Mar-2008 19:55:21
Posted:
136 posts
# Posted on: 07-Jan-2009 00:43:12.  
Greeting,

I'm use llbl version 2.6 ,SQL 2008,VS 2008,C#

I had task that rewrite a PROCEDURE from T-sql to ORM by C#, this PROCEDURE contains a table variable or to be more accurate many of table variables
this is sample script

Code:


DECLARE @NAV TABLE(
        cntID int Identity(1,1) ,
        PortfolioNo int,
        AccountNo int,
        TotBalance Numeric(30,12),
        TotBalance_Bid Numeric(30,12),
        Type int,
        Primary Key(cntID,PortfolioNo ,AccountNo ))


INSERT INTO @NAV
        SELECT a.PortfolioNo, isnull(a.AccountNo,0),
            a.TotBalance * dbo.GetExchRate(a.Currency,@PFCurrency,@AsOfDate),
            a.TotBalance * dbo.GetExchRate(a.Currency,@PFCurrency,@AsOfDate), 1 AS Type
        FROM @TrialBalance a
            JOIN dbo.AccChart b ON (a.AccountNo = b.[Id])
        WHERE (b.SubClass = 2)
            
INSERT INTO @NAV
            SELECT    TrialBalance.PortfolioNo,
                         TrialBalance.AccountNo,
                         TrialBalance.TotBalance ,
                         TrialBalance.TotBalance ,
                         1 AS Type
            FROM @TrialBalance TrialBalance
            INNER JOIN dbo.AccChart ON TrialBalance.AccountNo = dbo.AccChart.Id
            WHERE (dbo.AccChart.Parent = (SELECT value FROM tblconfig WHERE TrxNo = 53)) AND TrialBalance.Currency = @PFCurrency

INSERT INTO @NAV
        SELECT pffwd.PortfolioNo, isnull(@tblaccounts24,0),
             SUM(pffwd.QuantityBalance * pffwd.ClosePrice) AS MarketValue,
             SUM(pffwd.QuantityBalance * pffwd.BidPrice) AS MarketValue_Bid, 1 AS Type
        FROM @FwdPosition pffwd     
        WHERE pffwd.PortfolioNo = @PFNo
        GROUP BY pffwd.PortfolioNo





what is the best practises to
achieve this table variables and their inserts !!!

  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8098 posts
# Posted on: 07-Jan-2009 03:16:31.  
Hi mohamed,

May I ask you, Why do you need table variables for?

(Edit)
Also, read this: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=15032


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
mohamed
User



Location:
Salmia, kuwait
Joined on:
10-Mar-2008 19:55:21
Posted:
136 posts
# Posted on: 07-Jan-2009 09:53:34.  
Hi daelmo,

I use table variable for collecting data from several tables (these tables have different sructure) after collecting data i make some aggregates on few column and update this table variable finally use it as driven table to select from it any idea for applying this table variable ???

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 07-Jan-2009 10:14:06.  
The thread Daelmo suggested is a help desk thread (private), so you can't access it.
But here is the core of it:

Please check this out: Derived tables and dynamic relations were added in v.2.6


  Top
mohamed
User



Location:
Salmia, kuwait
Joined on:
10-Mar-2008 19:55:21
Posted:
136 posts
# Posted on: 07-Jan-2009 10:42:48.  
I know Derived tables but let put this way

if i use ResultsetFields it doesn't apply table variable that source of each column in table variable comes from multiple source

my dig deal is how to cerate table variable and insert and update on this table variable
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 07-Jan-2009 11:11:34.  
The following is quoted from that helpdesk thread:
MTrinder wrote:
LLBLGen's virtual/derived tables are only really meant to be used in a query. If you need to hold data in memory for processing reasons there are a multitude of data structures available to you - ADO.NET data tables, arrays/lists/collections/dictionaries etc of objects.

Using LLBLGen can sometimes (particularly if you have come from a heavy database background) need a fairly large shift in mentality to come round to its way of working - but it will be worth it

I second what's been said, you need to shift from processing data inside the database to processing them in your application. Use the database for fetching data and persisting them at the end, and whatever you do in between do it in your .NET application/code.


  Top
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 07-Jan-2009 11:42:28.  
Duly noted Walaa but to second what Mohammad is trying to explain I will explain the following scenario.

A huge SPROC (1600+ lines) that heavily uses table variables. Now, a developer tasked with transferring this SPROC to the BL using LLBL; he can surly use something like a DataTable for in memory caching of Selected data from the database.
LLBL offers a big barrage of SELECT tools including DerviedTables and ResultSetFields and each one of them can be used to FETCH data from the DB and then insert into the in-memory cache (like a DataTable)

The catch comes if you need to JOIN the collected data in the in-memory DataTable with some table in the DB to do your selection.

The only way out of this catch-22 position I can see is to build a huge FilterBucket with OR'ed conditions to simulate the join between the in-memory table and the DB table.

Is there a better way of doing this or at least know workarounds to do the same??
We don't know who we are until we see what we can do  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 07-Jan-2009 11:49:52.  
I see what you mean.

Are these derived tables very dynamic or they can be replaced by database views?
Needless to say that views can be mapped into entities which then can have relations customly defined with other entities.


  Top
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 07-Jan-2009 12:51:42.  
But if I am to replace them with table views and then map them to Entities and then use an EntityCollection as my in-memory collection of data, can I then use DynamicRelations to join my in-memory data in the EntityCollection with a DB table???
We don't know who we are until we see what we can do  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37796 posts
# Posted on: 07-Jan-2009 14:15:06.  
If you need a lot of temporary sets to produce the final set, it's obvious these temporary sets have to be available when the final set is produced. If you produce these temporary sets in memory, to produce the final set, based on these temporary sets, you have to transport these temporary sets to the database to produce the final set.

With derived tables, you can effectively produce these temporary sets as a derived table, and use them to filter the final set. You can join between derived tables, though it will require some code. The main thing is: try to write the query in such a way that all temporary sets are written as derived tables inside the main query so everything stays inside the database at query execution. Otherwise you have to transport data back/forth and that's not efficient.

It depends on what these temporary sets are of course. It also depends on how huge this query becomes.

I'm not sure what the final filter looks like: if the in-memory data contains customerid's for example of which you want the data of, you could do this by using fieldcomparerange predicates where you fetch the data in batches (if the # of entities to fetch is huge) by simply fetching each batch data into the same entity collection.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 07-Jan-2009 18:07:59.  
So to summarize your recommendation:

1- Try to build the SPROC's table variable as DerivedTables instead of DataTables because DerivedTables can be joined (DynamicRelation) with DB tables

2- Try to combine all these DerivedTables in a single query so to guarantee execution at the Database

3- If point 2 is not possible and a DerivedTable has the in-memory data to join with a DB table, then build fieldCompareRange predicates for each of the in-memory records to simulate the join to the DB table
We don't know who we are until we see what we can do  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37796 posts
# Posted on: 07-Jan-2009 19:00:43.  
omar wrote:
So to summarize your recommendation:

1- Try to build the SPROC's table variable as DerivedTables instead of DataTables because DerivedTables can be joined (DynamicRelation) with DB tables

2- Try to combine all these DerivedTables in a single query so to guarantee execution at the Database

3- If point 2 is not possible and a DerivedTable has the in-memory data to join with a DB table, then build fieldCompareRange predicates for each of the in-memory records to simulate the join to the DB table


Yes, and examine what the data collected in the proc is really used for for the final set. Say you want a list of customers who match a complex set of criteria. In the end, you can try to produce the set of customerid's which match the criteria and use that to fetch the customers (for example, I'm sure your setup is more complex), or use the outcome of parts of the criteria queries to produce a set which can be used in another query (the next step of the criteria production) for example.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.