temp table question

Posts   
 
    
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 12-Jan-2012 17:34:02   

I know LLBLGen doesn't support real temp table for database.

Can you guys have a look if it is possible to be implemented with llblgen functions?

declare @p1_param TinyInt declare @p2_param NVarChar(31)

set @p1_param = 1; set @p2_param = '%File For Grants Autoresponder%';

SELECT [LPA_V3].[ID] AS [ClientId] INTO #temp_cte_exclusion FROM [Autoresponders] [LPA_V2] INNER JOIN [Autosub] [LPA_V3] ON [LPA_V2].[AutoresponderID]=[LPA_V3].[AutoresponderID] WHERE ([LPA_V2].[AutoresponderName] LIKE @p2_param)

SELECT * FROM [Clients] [LPA_V1] LEFT JOIN #temp_cte_exclusion [cte] ON [LPA_V1].ClientId = [cte].ClientId WHERE [LPA_V1].[mailingList] IN (@p1_param) AND [cte].ClientId IS NULL

drop table #temp_cte_exclusion;

This is performance issue for us. if we are using above query, we can get the result less than a second,

but we use the following, it will take 6 minutes to get the result.

set @p1_param = 1; set @p2_param = '%File For Grants Autoresponder%';

SELECT * FROM [Clients] [LPA_V1] WHERE ( ( ( ( [LPA_V1].[mailingList] IN (@p1_param))) AND ( ( NOT EXISTS (SELECT [LPA_V3].[ClientID] AS [ClientId] FROM ( [Autoresponders] [LPA_V2] INNER JOIN [Autosub] [LPA_V3] ON [LPA_V2].[AutoresponderID]=[LPA_V3].[AutoresponderID]) WHERE ( [LPA_V3].[ClientID] = [LPA_V1].[ClientId] AND [LPA_V2].[AutoresponderName] LIKE @p2_param))))))

Thanks

Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 12-Jan-2012 22:17:06   

By the way, the query is generated dynamically by LLBLGen functions. The stored procedure is not an option to me to wrap the temp table inside.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 13-Jan-2012 00:15:22   

Mm. No, you can't use #temp tables in entity fetches queries. The option (which you said is not an option) is write that StoreProcedure and then project the result to an entity collection.

One question: how fast would be this query?:

set @p1_param = 1; 
set @p2_param = '%File For Grants Autoresponder%'; 

SELECT *
FROM [Clients] [LPA_V1]
LEFT JOIN 
     ( SELECT [LPA_V3].[ID] AS [ClientId] 
       FROM [Autoresponders] [LPA_V2] 
       INNER JOIN [Autosub] [LPA_V3] 
          ON [LPA_V2].[AutoresponderID]=[LPA_V3].[AutoresponderID]
       WHERE ([LPA_V2].[AutoresponderName] LIKE @p2_param)
     ) [cte]  ON [LPA_V1].ClientId = [cte].ClientId
WHERE [LPA_V1].[mailingList] IN (@p1_param)
AND [cte].ClientId IS NULL

... which seems to be the same you are doing, with the difference that you this approach doesn't insert results in a temporary table. You can acomplish this kind of queries with Derived Tables.

David Elizondo | LLBLGen Support Team
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 13-Jan-2012 15:28:07   

Hi Daelmo,

I have tried your query which runs almost same as using "not exists". It doesn't work.

Do you have any other suggestion? can we mix the stored procedure with dynamic queries to return entity collection?

Between stored procedures and dynamic queries, I was convinced at some point that there is no much different between them. As long as we have right index and fairly good server, we should be OK. So our entire search engine has been built with dynamic queries using LLBLGen functions. It is very flexible and easy to dynamically build and generate the queries with filter predicates, sorting and so on. I am very impressed and pleased with the flexible part. For performance, most of the queries are fine, but some special case like this(not exists) is a problem.

Will LLBLGen support temp table soon?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 13-Jan-2012 19:33:49   

Chaoyster wrote:

I have tried your query which runs almost same as using "not exists". It doesn't work.

Mmm, to me it looks like it does what you intended to do: A Join with a Temp table, it just that the temp table is not physical, but it's represented by the subquery. That is why LLBLGen added this (DerivedTables and DynamicRelations). Please test that as I think it should return the same results almost at the same cost. As a matter of fact, shouldn't this DerivedTables way be faster? In the #temp approach there is much more I/O to the database.

David Elizondo | LLBLGen Support Team
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 13-Jan-2012 19:46:54   

I thought that as well, I have run all three queries in management studio console, the temp table is way faster than any sub-queries(less than second), we have about 6000 somewhat records in that sub-query. Our DBA explains to me that the query for the temp table only run once. and it is static, then doing the joining only once. but if it is subquery, it is like nested loop. It dynamically runs the sub-query all the time during the join. I think he is right, since the result is quite different.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-Jan-2012 10:00:38   

For such rare edge cases, we support projecting StoredProcedures' resultSets into EntityCollections.

Please check the following documentation link: Projecting Stored Procedure resultset onto entity collection

Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 16-Jan-2012 15:23:14   

I don't understand if the projecting stored procedure will help me on this. that's say I have a store procedure for creating client ID similar like the temp table. and I am able to get the all the client id into the entity collection or list. How can I dynamically combine the result with the second dynamic query to fetch the data from database again? do you have an example like that? cause the example of the link only shows that how to get the result from a store procedure.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Jan-2012 05:31:38   

You would write all the sql code into the stored procedure: fill the #temp, make the result joining with #temp, delete #temp and return the result. Then THAT final result could be mapped into an entity collection.

David Elizondo | LLBLGen Support Team
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 17-Jan-2012 15:41:07   

That's been said, the stored procedure is not an option for me, since all the sql code generated on the fly by the LLBLGen filter predicates functions. each time the sql code could be different, I can not hard code all the sql code in the stored procedure, that's almost infinite. all the predicates in where clause and sorting are generated dynamically. Otherwise I have to get all the where clause and sorting as string and pass them into the hell of stored procedures. and do the sql code build inside of stored procedure. I know this will work, we have another reporting system build like that which we didn't like it, and it needs a lot of work and also I don't want to embed all the business rule inside of stored procedure either.

I still believe that the temp table should be supported/implemented as part of LLBLGen functions. As LLBLGen is a wrapper/mapper of all relational database schema and functions.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 18-Jan-2012 03:35:18   

The thing with temp-tables is: you don't know they exist until you create them. So there are no entity/fields/persistenceInfo for them. Untimely I think you could create a custom IEntityRelation or a custom IPredicate to mimic what you want, but still it could be not easy and error prone. Also, even if you have some kind of trick to make it possible, you have to insert records into it, then delete it.

I don't see how it would be supported in the framework. If it were, How would you expect to use such feature?

Another option: Did you consider Indexed Views (SQLServer) / Materialized Views (Oracle). It seems very close to your performance needs.

David Elizondo | LLBLGen Support Team
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 19-Jan-2012 18:18:49   

declare @p1_param TinyInt declare @p2_param NVarChar(31)

set @p1_param = 1; set @p2_param = '%File For Grants Autoresponder%'; SELECT * FROM [Clients] [LPA_V1] JOIN { Select ClientId from Clients where mailingList IN (@p1_param) EXCEPT SELECT [LPA_V3].[ID] AS [ClientId] INTO #temp_cte_exclusion FROM [Autoresponders] [LPA_V2] INNER JOIN [Autosub] [LPA_V3] ON [LPA_V2].[AutoresponderID]=[LPA_V3].[AutoresponderID] WHERE ([LPA_V2].[AutoresponderName] LIKE @p2_param) ) [cte] on [cte].[ClientId] = [LPA_V1].[ClientId] OPTION (FAST 50)

DBA comes with the above query, it seems working well with performance. Does LLBLGen support EXCEPT and OPTION?

Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 19-Jan-2012 18:25:43   

My bad, copy and paste mistake. Actually it is the following query.

declare @p1_param TinyInt declare @p2_param NVarChar(31)

set @p1_param = 1; set @p2_param = '%File For Grants Autoresponder%'; SELECT * FROM [Clients] [LPA_V1] JOIN { Select ClientId from Clients where mailingList IN (@p1_param) EXCEPT SELECT [LPA_V3].[ID] AS [ClientId] FROM [Autoresponders] [LPA_V2] INNER JOIN [Autosub] [LPA_V3] ON [LPA_V2].[AutoresponderID]=[LPA_V3].[AutoresponderID] WHERE ([LPA_V2].[AutoresponderName] LIKE @p2_param) ) [cte] on [cte].[ClientId] = [LPA_V1].[ClientId] OPTION (FAST 50)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Jan-2012 18:54:20   

There is no built-in support for semi-join EXCEPT. Is there any performance difference between EXCEPT and NOT IN ? (they seems to have the same execution plan). If that is a must, I think you could cook a variation of a FieldCompareSetPredicate that include Except as Set Operand.

Also there is no built-in support for "OPTiON (FAST n)", however you can write code to support it. Here is an example of (nolock) hint: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16508&StartAtMessage=0&#92356

So give it a try. Let me know if you need help with those extensions.

David Elizondo | LLBLGen Support Team
Chaoyster
User
Posts: 40
Joined: 23-Mar-2011
# Posted on: 19-Jan-2012 19:28:07   

I do like to know how to cook a variation of a FieldCompareSetPredicate that include EXCEPT as Set Operand. do you have an example? We are using FieldCompareSetPredicate to build the exists or not exists sql statement. I have tested the query without the Options( Fast n), The performance of the query seems no different. and Also let me know if we can add INTERSECT in as well.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 20-Jan-2012 06:38:37   

I think I talked to early. Except, Intersect, Union, etc, are a special kind of query which are applied to sets on both sides (i.e. set1 EXCEPT set2). So it's no that simple as FieldCompareSetPredicate (i.e. field IN set1). This is not supported and it's not easy to implement it in an special query api element.

Is there any performance difference to you between EXCEPT and NOT IN? If you really need EXCEPT, for the moment you can put the query in a stored procedure, and fetch the results from there.

David Elizondo | LLBLGen Support Team