As a newbie, I am trying to look at the behaviour of LLblgen with MS-SQL.

I have a small sample code in a Windows Forms application that retrieves 10 records from the Customers table from the AdventureWorks database (not Northwind this time for a change). I also use a prefetch for the Salesterritory object:

bindingSource1.DataSource = entityCollection1;
dataGridView1.DataSource = bindingSource1;
bindingNavigator1.BindingSource = bindingSource1;
DataAccessAdapter ds = new DataAccessAdapter();
PrefetchPath2 path = new PrefetchPath2((int)EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathSalesTerritory );
ds.FetchEntityCollection(entityCollection1, null, 0, null, path,3, 10);
CustomerEntity cust =  (CustomerEntity)entityCollection1[0];
        
SalesTerritoryEntity s = new SalesTerritoryEntity();
s = cust.SalesTerritory;

To my surprise the generated SQL does not use a inner join for the two tables but makes two separate select calls, which is neat I guess. However, beside about 10 useful commands I see about 90 commands that seems to be unnecessary. The following three statements are executed about 30 times:

SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;
RETURN (@ReturnValue);
SET @ReturnValue = CONVERT(varchar(8), @Value);
    

Do I need to configure something different or is this unavoidable?

Hi Dotprof,

1. What LLBLGen version and runtime library version are you using? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717)

2. Where are you seeing the generated sql (sql prfiler, llblgen tracing)?

3. Please attach a .txt file with the complete sql trace.

Sorry to jump in here, but I've seen this exact thing before tinkering with Adventureworks.

On the Customer table, the AccountNumber column is a computed column, which is causing that extra query to run multiple times (I think once per row returned?). The column is computed by a UDF if I'm not mistaken.

HTH,

Phil

Thanks for the replies. I am using the (demo) version 2.6 of LLBLGen Pro. The last message, from Phil is correct. The Customer table has a UFD for adding leading zero's to the CustomerID. E.g. accountnumber = 'AW00000001' for record with CustomerId=1

ALTER FUNCTION [dbo].[ufnLeadingZeros](
    @Value int
) 
RETURNS varchar(8)
WITH SCHEMABINDING 
AS 
BEGIN
    DECLARE @ReturnValue varchar(8);

    SET @ReturnValue = CONVERT(varchar(8), @Value);
    SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;

    RETURN (@ReturnValue);
END;

This is causing the SQL statements in the profiler.

If you collect page 20 of the customer table with 10 records/page (so a fetch of records 191-200), the profiler shows 610 statements of which 603 statements are caused by the UDF of the computed column Accountnumber.

Maybe I better stick to Northwind

So this is unavoidable for the computed column, whether you are using LLBLGen or not.