Can I build queries with predefined functions in LLBLGen?

here is the query I would like to build,

exec sp_executesql N' declare @MerchantId1_param Int
declare @MerchantId2_param Int
declare @MerchantId3_param Int
declare @Valid4_param TinyInt
declare @AutoresponderName5_param NVarChar (200) set @MerchantId1_param = @MerchantId1;
set @MerchantId2_param = @MerchantId2;
set @MerchantId3_param = @MerchantId3;
set @Valid4_param = @Valid4;
set @AutoresponderName5_param = @AutoresponderName5; SELECT DISTINCT TOP 10 c.[ClientID] AS [ClientId], c.[MerchantID] AS [MerchantId], c.[Name], c.[Email], c.[City], c.[State], c.[Zip], c.[Country], c.[HomePhone], c.[WorkPhone], c.[DateEntered], c.[adid] AS [Adid], c.[afid] AS [Afid] FROM [mc].[dbo].tf_crm_clients_all c LEFT JOIN [mc].[dbo].tf_crm_Autosub a ON c.[ClientID]= a.[ClientID] LEFT JOIN [mc].[dbo].tf_crm_Autoresponders ar ON ar.[AutoresponderID]= a.[AutoresponderID] WHERE ar.[AutoresponderName] IS NOT NULL AND ar.[AutoresponderName] <> @AutoresponderName5_param ORDER BY c.[DateEntered] DESC,c.[Name] ASC',N'@MerchantId1 int, @MerchantId2 int,@MerchantId3 int,@Valid4 tinyint,@AutoresponderName5 nvarchar(200)', @MerchantId1=111,@MerchantId2=111,@MerchantId3=111,@Valid4=1,@AutoresponderName5=N''

Thanks,

Hi there,

So, you are talking about function-based queries...

FROM [mc].[dbo].[tf_crm_clients_all](@MerchantId2_param) c
LEFT JOIN [mc].[dbo].[tf_crm_Autosub](@MerchantId2_param) a ON c.[ClientID]= a.[ClientID]
LEFT JOIN [mc].[dbo].[tf_crm_Autoresponders](@MerchantId2_param)

.. sorry, that is not supported. You may consider using a stored procedure, then you can create a projector that translates the results into LLBLGen entities.

If you can migrate those functions into typed queries (LLBLGen constructs based on entities, fields, predicate expression, etc.) you could use DerivedTables, otherwise, you cant. Please consider the SProc+Projection approach.

Thanks, could you give me a code example on how to replace the function with stored procedures? like how to create a projector that translates the results into LLBLGen entities. I'd like to use SProc+Projection approach.

I have looked at the sample about how to use sp_procedure + projection. I think my situation is more complicated, actually I need to create stored procedures to replace the functions in the query which I have posted in my original message. Is it possible? Rather than wrapping up the whole query with one stored procedure and projection on it.

Note: the queries are dynamically generated in my case, it is not feasible to create 100+ stored procedure for each cases.

FROM [mc].[dbo].[tf_crm_clients_all](@MerchantId2_param) c
LEFT JOIN [mc].[dbo].[tf_crm_Autosub](@MerchantId2_param) a ON c.[ClientID]= a.[ClientID]
LEFT JOIN [mc].[dbo].[tf_crm_Autoresponders](@MerchantId2_param) ar ON ar.[AutoresponderID]= a.[AutoresponderID]

Translated to the following

FROM [mc].[dbo].[sp_crm_clients_all] @MerchantId2_param c
LEFT JOIN [mc].[dbo].[sp_crm_Autosub] @MerchantId2_param a ON c.[ClientID]= a.[ClientID]
LEFT JOIN [mc].[dbo].[sp_crm_Autoresponders] @MerchantId2_param ar ON ar.[AutoresponderID]= a.[AutoresponderID]

I know the above query even not a runnable Sql statement per say. just wondering how LLBLGen will handle this case with stored procedures + projection instead of functions.

Again, the whole issue I am facing is the performance. we have large amount data in all the tables. it is timed out crazy even with proper index applied with load. so we think to create some basic data sets using function limited by merchant Id and then join them together. The only thing we can do is adjust the sql statement and try to use one of execution plans good for all the situations.

The last resource I have is to create views or even indexed views to replace the functions. Do you think this would be a better approach?

Thanks.

Out of interest, what sort of volumes of data are we talking about ? LLBLGen's generated queries are usually very efficient, and are rarely the cause of performance issues.

Have you tried building an indexed view to see if this resolves the issue ?

Matt

I didn't mean that the LLBLGen generated query is not efficient. if we are using tables instead of function, let me make it clear, not for small account, it is ok for average accounts, but some larger accounts is over 1 minute during the heavy load. that's why to to use function to replace table to improve the performance which is suggested by our DBA.

Thanks

So how many rows of data are we talking about for a large account ?

Matt

I haven't try the indexed view yet, just try to get as much as options, as soon as the query generated as DBA expects, I am done. The DBA can play with them until the cows come home.

around 1.5 million records for the larger accounts

To answer your original question (I think ) LLBLGen should cope fine if the functions are wrapped up in stored procedures - the only output LLBLGen is expecting is one or more resultsets, it doesn't care about the internal implentation.

Matt

Could you come up with a code example on how to join the multiple stored procedures together?

Thanks,

I'm pretty sure that's not possible. I envisaged all of your functionality being wrapped up in one SP?

If you need to join multiple function based "things" together then I think Indexed views may be your only way to go.

Matt