Hi, is there a way to stop Excluded fields being SELECTed as NULL in the generated SQL?

For example i have 2 queries here, 1st one generated by LLBLGen, and 2nd one edited by me. The 1st one takes significantly longer. I'd like to know if there is a way to ensure the SQL generated is like the 2nd query.

Query 1 (Generated by LLBLGen)

SELECT NULL AS [CmpId], NULL AS [MunId], NULL AS [ParId], NULL AS [PltId], NULL AS [Rowguid], NULL AS [VesId], NULL AS [VivAmos], NULL AS [VivAmosDeptNo], NULL AS [VivComment], NULL AS [VivCritical], NULL AS [VivDeleted], NULL AS [VivDrawingPos], [ProjectDb].[dbo].[VesInventory].[VIV_ID] AS [VivId], NULL AS [VivMakersRef], NULL AS [VivMaxStock], NULL AS [VivMinStock], [ProjectDb].[dbo].[VesInventory].[VIV_Name] AS [VivName], NULL AS [VivRob], NULL AS [VivUpdatedBy], NULL AS [VivUpdatedOn] FROM [ProjectDb].[dbo].[VesInventory] WHERE ([ProjectDb].[dbo].[VesInventory].[VIV_ID] IN ('GLAS08279553' /* @p1 /, 'GLAS08279556' / @p2 /, 'GLAS08279555' / @p3 /, 'GLAS08279554' / @p4 /, 'GLAS08279557' / @p5 /, 'GLAS08279558' / @p6 */))

Query 2 (Hand edited to remove the SELECT NULL as [Field] statements)

SELECT [ProjectDb].[dbo].[VesInventory].[VIV_ID] AS [VivId], [ProjectDb].[dbo].[VesInventory].[VIV_Name] AS [VivName] FROM [ProjectDb].[dbo].[VesInventory] WHERE ([ProjectDb].[dbo].[VesInventory].[VIV_ID] IN ('GLAS08279553' /* @p1 /, 'GLAS08279556' / @p2 /, 'GLAS08279555' / @p3 /, 'GLAS08279554' / @p4 /, 'GLAS08279557' / @p5 /, 'GLAS08279558' / @p6 */))

Thanks, Iain

Why do you need this? just to minimize the SQL code?

Please check the following scenario:

var excludedFields = new ExcludeIncludeFieldsList();
excludedFields.Add(CustomerFields.Country);
CustomerEntity customer = new CustomerEntity("CHOPS");

adapter.FetchEntity(customer);
// customer.Country = "Switzerland"; // say

customer.Country = "UK";
adapter.FetchEntity(customer, null, null, excludedFields);

adapter.FetchEntityCollection(customersCol, excludedFields, null);

After editing the Country, and then perform a fetch excluding the company. What would you like the Country value to be after the second fetch, and what would be the state of the entity? (fetched or modified?

Hi,

Thanks for the quick response.

I should have explained a little bit about my scenario. In fact, i am doing this to support a scenario where i know up front that the fields that i exclude are not going to be used.

Basically, i have Entity A which could be potentially modified by the view, however the same view also needs to show 'specific' properties from related Entity B. In this case, I'd like to exclude the properties i don't need from Entity B.

The reason i ask is that for whatever reason, the SQL statement which does "SELECT NULL as [Field]" for all the excluded fields, runs slower than a SQL fetch which just misses the excluded fields out of the statement completely. (As in Query 2 in my initial example)

Would you expect the query with all the "NULL as [Field]" statements to be slower?

To be honest, i was surprised to see the difference in speed and that's what initially sparked me into asking here.

Cheers, Iain

What did you use to profile the queries? Do you have figures, how slow it is compared to the slim one? Which database type and version are you using?

Just performed a quick test on SQL 2012 SP1 It seems to not have any effect, might be I'm missing something here.

TopDog74 wrote:

Hi,

Thanks for the quick response.

I should have explained a little bit about my scenario. In fact, i am doing this to support a scenario where i know up front that the fields that i exclude are not going to be used.

Basically, i have Entity A which could be potentially modified by the view, however the same view also needs to show 'specific' properties from related Entity B. In this case, I'd like to exclude the properties i don't need from Entity B.

The reason i ask is that for whatever reason, the SQL statement which does "SELECT NULL as [Field]" for all the excluded fields, runs slower than a SQL fetch which just misses the excluded fields out of the statement completely. (As in Query 2 in my initial example)

Would you expect the query with all the "NULL as [Field]" statements to be slower?

To be honest, i was surprised to see the difference in speed and that's what initially sparked me into asking here.

Cheers, Iain

how did you measure the performance difference, if I may ask? The difference should be 0 to almost 0 if you count the extra null bytes for the columns transported in the resultset, but as these are small (8 bits) I doubt it will be noticable.

to be honest, i just ran both queries in SQL Management Studio. db is SQL 2008 R2

The version of the query with excluding fields was noticeably slower just by watching and waiting for the response.

Could you please rerun them again (restart the DB service) in between runs? (order of execution might have an effect), and then provide some numbers?

Thanks,