I have a question regarding the logic behind the generated SQL. Specifically, I have a query that is timing out because it seems to be using strange logic to build the query.
Basically, I have two tables which are joined via a composite key. I have two filters on the query, one on one table, and one on the other. But when LLBL creates the SQL from the query, it is using strange logic to join the tables and filter the results.
Here is the code for the data source:
<llblgenpro:LLBLGenProDataSource2 ID="ContractDS" runat="server" EnablePaging="true" LivePersistence="false"
DataContainerType="EntityCollection"
AdapterTypeName="Components.DataAccessLayers.MyDatabase.DatabaseSpecific.DataAccessAdapter, Components.DataAccessLayers.MyDatabaseDBSpecific"
EntityFactoryTypeName="Components.DataAccessLayers.MyDatabase.FactoryClasses.ContractMasterEntityFactory, Components.DataAccessLayers.Servicer"
OnPerformSelect="ContractDS_PerformSelect" OnPerformGetDbCount="ContractDS_PerformGetDbCount"
>
</llblgenpro:LLBLGenProDataSource2>
Here is the code for the data source's PerformSelect:
protected void ContractDS_PerformSelect(object sender, SD.LLBLGen.Pro.ORMSupportClasses.PerformSelectEventArgs2 e)
{
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(ContractMasterEntity.Relations.CustMasterEntityUsingCustomerIdDataSource, JoinHint.Left);
filter.PredicateExpression.Add(ContractMasterFields.DealerId == "0015392");
filter.PredicateExpression.Add(CustMasterFields.LastName == LastName.Text);
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
PrefetchPath2 path = new PrefetchPath2((int)EntityType.ContractMasterEntity);
path.Add(ContractMasterEntity.PrefetchPathCustMaster);
adapter.FetchEntityCollection(e.ContainedCollection, filter,
e.MaxNumberOfItemsToReturn, e.Sorter, path, e.PageNumber, e.PageSize);
}
}
Here is the resulting SQL query for the customer data (I replaced column names with "*" fro simplicity):
SELECT *
FROM [MyDatabase].[dbo].[Cust_Master]
WHERE
(
[MyDatabase].[dbo].[Cust_Master].[Customer_ID] IN
(
SELECT [MyDatabase].[dbo].[Contract_Master].[Customer_ID] AS [CustomerId]
FROM (
[MyDatabase].[dbo].[Cust_Master]
RIGHT JOIN [MyDatabase].[dbo].[Contract_Master]
ON [MyDatabase].[dbo].[Cust_Master].[Customer_ID]=[MyDatabase].[dbo].[Contract_Master].[Customer_ID]
AND [MyDatabase].[dbo].[Cust_Master].[Data_Source]=[MyDatabase].[dbo].[Contract_Master].[Data_Source]
)
WHERE (
(
(
[MyDatabase].[dbo].[Contract_Master].[Dealer_ID] = @DealerId1
AND [MyDatabase].[dbo].[Cust_Master].[Last_Name] = @LastName2
)
)
)
)
AND [MyDatabase].[dbo].[Cust_Master].[Data_Source] IN
(
SELECT [MyDatabase].[dbo].[Contract_Master].[Data_Source] AS [DataSource]
FROM (
[MyDatabase].[dbo].[Cust_Master]
RIGHT JOIN [MyDatabase].[dbo].[Contract_Master]
ON [MyDatabase].[dbo].[Cust_Master].[Customer_ID]=[MyDatabase].[dbo].[Contract_Master].[Customer_ID]
AND [MyDatabase].[dbo].[Cust_Master].[Data_Source]=[MyDatabase].[dbo].[Contract_Master].[Data_Source]
)
WHERE (
(
(
[MyDatabase].[dbo].[Contract_Master].[Dealer_ID] = @DealerId3
AND [MyDatabase].[dbo].[Cust_Master].[Last_Name] = @LastName4
)
)
)
)
)
The above query takes two minutes to execute. However, the following query is much simpler, but is logically the same query, and takes a couple seconds to execute (uses indexes properly). Keep in mind, there are indexes on both [Contract_Master].[Dealer_ID] and [Cust_Master].[Last_Name].
SELECT *
FROM [MyDatabase].[dbo].[Cust_Master]
JOIN [MyDatabase].[dbo].[Contract_Master]
ON [MyDatabase].[dbo].[Cust_Master].[Customer_ID] = [MyDatabase].[dbo].[Contract_Master].[Customer_ID]
AND [MyDatabase].[dbo].[Cust_Master].[Data_Source] = [MyDatabase].[dbo].[Contract_Master].[Data_Source]
WHERE [MyDatabase].[dbo].[Contract_Master].[Dealer_ID] = @DealerId1
AND [MyDatabase].[dbo].[Cust_Master].[Last_Name] = @LastName2
So my question is, what is causing LLBL to use this overly complicated and much less performant query? Does it have something to do with how the data is related (composite key), or is there something else going on? Is there anything I can do to help it use a better query?
Thanks in advance.
Jerad
