Re: Build Expression Query very slow

Wed, 19 Nov 2008 07:30:05 -0800 

Just keep in mind that all generated requests are cached, so the generation
time is for the first call only.
On Wed, Nov 19, 2008 at 16:10, Jimbo1982 <[EMAIL PROTECTED]> wrote:

>
> Hi,
>
> I am running an left join on 2 tables and the
> QueryBuilder.BuildExpressionQuery is taking about 7 seconds each time.
> I am running the select over 150 times on different inputs. Any ideas
> why it is taking so long. Currently it is far to slow and I will be
> forced to use NHibernate :-( or just string sql :-((
>
> Any help appreciated,
>
> James
>
> I have included the output from one of the selects.
>
> Call (MethodCallExpression)
> ..Object: (null)
> ..Method: Where
> ..#00000 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Table`1[PickSlot]
> ..#00001 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call Equal (BinaryExpression)
> ........Left  MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: ps
> ..........Member: StationID
> ........Right MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object MemberAccess (MemberExpression)
> ..............Object Constant (ConstantExpression)
> ................Value: ProductSlottingTool.Database.P2008-11-19
> 14:58:26Z
>  Call (MethodCallExpression)
> ..Object: (null)
> ..Method: Where
> ..#00000 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Table`1[PickSlot]
> ..#00001 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call Equal (BinaryExpression)
> ........Left  MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: ps
> ..........Member: StationID
> ........Right MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object MemberAccess (MemberExpression)
> ..............Object Constant (ConstantExpression)
> ................Value:
> ProductSlottingTool.Database.Procedures.GetWarehouseSlotDetailsDao
> +<>c__DisplayClass3
> ..............Member: warehouseStation
> ............Member: Id
> ..........Member: Value
> ......#000 Parameter (ParameterExpression)
> ........Parameter: ps
> rocedures.GetWarehouseSlotDetailsDao+<>c__DisplayClass3
> ..............Member: warehouseStation
> ............Member: Id
> ..........Member: Value
> ......#000 Parameter (ParameterExpression)
> ........Parameter: ps
>
>  Call (MethodCallExpression)
> ..Object: (null)
> ..Method: GroupJoin
> ..#00000 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Implementation.QueryProvider`1[PickSlot]
> ..#00001 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Table`1[SkU]
> ..#00002 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call MemberAccess (MemberExpression)
> ........Object Parameter (ParameterExpression)
> ..........Parameter: ps
> ........Member: SkUID
> ......#000 Parameter (ParameterExpression)
> ........Parameter: ps
> ..#00003 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call MemberAccess (MemberExpression)
> ........Object Parameter (ParameterExpression)
> ..........Parameter: s
> ........Member: SkUID
> ......#000 Parameter (ParameterE2008-11-19 14:58:26Z
>  Call (MethodCallExpression)
> ..Object: (null)
> ..Method: GroupJoin
> ..#00000 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Implementation.QueryProvider`1[PickSlot]
> ..#00001 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Table`1[SkU]
> ..#00002 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call MemberAccess (MemberExpression)
> ........Object Parameter (ParameterExpression)
> ..........Parameter: ps
> ........Member: SkUID
> ......#000 Parameter (ParameterExpression)
> ........Parameter: ps
> ..#00003 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call MemberAccess (MemberExpression)
> ........Object Parameter (ParameterExpression)
> ..........Parameter: s
> ........Member: SkUID
> ......#000 Parameter (ParameterExpression)
> ........Parameter: s
> ..#00004 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call New (NewExpression)
> ........Ctor: .ctor
> ........#000 Parameter (ParameterExpression)
> ..........Parameter: ps
> ........#001 Parameter (ParameterExpression)
> ..........Parameter: tmpProductSlot
> ........M000: get_ps
> ........M001: get_tmpProductSlot
> ......#000 Parameter (ParameterExpression)
> ........Parameter: ps
> ......#001 Parameter (ParameterExpression)
> ........Parameter: tmpProductSlot
> xpression)
> ........Parameter: s
> ..#00004 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call New (NewExpression)
> ........Ctor: .ctor
> ........#000 Parameter (ParameterExpression)
> ..........Parameter: ps
> ........#001 Parameter (ParameterExpression)
> ..........Parameter: tmpProductSlot
> ........M000: get_ps
> ........M001: get_tmpProductSlot
> ......#000 Parameter (ParameterExpression)
> ........Parameter: ps
> ......#001 Parameter (ParameterExpression)
> ........Parameter: tmpProductSlot
>
>  Call (MethodCallExpression)
> ..Object: (null)
> ..Method: SelectMany
> ..#00000 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Implementation.QueryProvider`1
> [<>f__AnonymousType0`2
> [PickSlot,System.Collections.Generic.IEnumerable`1[SkU]]]
> ..#00001 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call Call (MethodCallExpression)
> ........Object: (null)
> ........Method: DefaultIfEmpty
> ........#00000 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: <>h__TransparentIdentifier0
> ..........Member: tmpProductSlot
> ......#000 Parameter (ParameterExpression)
> ........Parameter: <>h__TransparentIdentifier0
> ..#00002 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call New (NewExpression)
> ........Ctor: .ctor
> ........#000 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: PickSlotID
> ........#001 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: XCooRd
> ........#002 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: YCooRd
> ........#003 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: ZCooRd
> ........#004 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: MaXWeight
> ........#005 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: TUType
> ........#006 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: MachineID
> ........#007 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: RepLenLocID
> ........#008 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: Old
> ........#009 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: SkUID
> ........#010 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachVolume
> ........#011 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachWeight
> ........#012 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: BagSegregationDamage
> ........#013 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: BagSpace
> ........#014 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: Commonality
> ........#015 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: WineHolder
> ........#016 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: Description
> ........#017 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachHeight
> ........#018 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachWidth
> ........#019 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachLength
> ........#020 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: Sample
> ........M000: get_PickSlotID
> ........M001: get_XCooRd
> ........M002: get_YCooRd
> ........M003: get_ZCooRd
> ........M004: get_MaXWeight
> ........M005: get_TUType
> ........M006: get_MachineID
> ........M007: get_RepLenLocID
> ........M008: get_Old
> ........M009: get_SkUID
> ........M010: get_EachVolume
> ........M011: get_EachWeight
> ........M012: get_BagSegregationDamage
> ........M013: get_BagSpace
> ........M014: get_Commonality
> ........M015: get_WineHolder
> ........M016: get_Description
> .......2008-11-19 14:58:27Z
>  Call (MethodCallExpression)
> ..Object: (null)
> ..Method: SelectMany
> ..#00000 Constant (ConstantExpression)
> ....Value: DbLinq.Data.Linq.Implementation.QueryProvider`1
> [<>f__AnonymousType0`2
> [PickSlot,System.Collections.Generic.IEnumerable`1[SkU]]]
> ..#00001 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call Call (MethodCallExpression)
> ........Object: (null)
> ........Method: DefaultIfEmpty
> ........#00000 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: <>h__TransparentIdentifier0
> ..........Member: tmpProductSlot
> ......#000 Parameter (ParameterExpression)
> ........Parameter: <>h__TransparentIdentifier0
> ..#00002 Quote (UnaryExpression)
> ....Method :
> ....Operand Lambda (Expression`1)
> ......Call New (NewExpression)
> ........Ctor: .ctor
> ........#000 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: PickSlotID
> ........#001 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: XCooRd
> ........#002 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: YCooRd
> ........#003 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: ZCooRd
> ........#004 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: MaXWeight
> ........#005 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: TUType
> ........#006 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: MachineID
> ........#007 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: RepLenLocID
> ........#008 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: Old
> ........#009 MemberAccess (MemberExpression)
> ..........Object MemberAccess (MemberExpression)
> ............Object Parameter (ParameterExpression)
> ..............Parameter: <>h__TransparentIdentifier0
> ............Member: ps
> ..........Member: SkUID
> ........#010 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachVolume
> ........#011 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachWeight
> ........#012 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: BagSegregationDamage
> ........#013 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: BagSpace
> ........#014 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: Commonality
> ........#015 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: WineHolder
> ........#016 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: Description
> ........#017 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachHeight
> ........#018 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachWidth
> ........#019 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: EachLength
> ........#020 MemberAccess (MemberExpression)
> ..........Object Parameter (ParameterExpression)
> ............Parameter: subsku
> ..........Member: Sample
> ........M000: get_PickSlotID
> ........M001: get_XCooRd
> ........M002: get_YCooRd
> ........M003: get_ZCooRd
> ........M004: get_MaXWeight
> ........M005: get_TUType
> ........M006: get_MachineID
> ........M007: get_RepLenLocID
> ........M008: get_Old
> ........M009: get_SkUID
> ........M010: get_EachVolume
> ........M011: get_EachWeight
> ........M012: get_BagSegregationDamage
> ........M013: get_BagSpace
> ........M014: get_Commonality
> ........M015: get_WineHolder
> ........M016: get_Description
> ........M017: get_EachHeight
> ........M018: get_EachWidth
> ........M019: get_EachLength
> ........M020: get_Sample
> ......#000 Parameter (ParameterExpression)
> ........Parameter: <>h__TransparentIdentifier0
> ......#001 Parameter (ParameterExpression)
> ........Parameter: subsku
> .M017: get_EachHeight
> ........M018: get_EachWidth
> ........M019: get_EachLength
> ........M020: get_Sample
> ......#000 Parameter (ParameterExpression)
> ........Parameter: <>h__TransparentIdentifier0
> ......#001 Parameter (ParameterExpression)
> ........Parameter: subsku
> Select Expression build: 8725ms
> Select SQL build:        1ms
> Select SQL: SELECT ps$.PICK_SLOT_ID, ps$.X_COORD, ps$.Y_COORD, ps
> $.Z_COORD, ps$.MAX_WEIGHT, ps$.TU_TYPE, ps$.MACHINE_ID, ps
> $.REPLEN_LOC_ID, ps$.OLD, ps$.SKU_ID, s$.EACH_VOLUME, s$.EACH_WEIGHT, s
> $.BAG_SEGREGATION_DAMAGE, s$.BAG_SPACE, s$.COMMONALITY, s
> $.WINE_HOLDER, s$.DESCRIPTION, s$.EACH_HEIGHT, s$.EACH_WIDTH, s
> $.EACH_LENGTH, s$.SAMPLE
> FROM WMSADH.PICK_SLOT ps$
>  LEFT JOIN WMSADH.SKU s$ ON ps$.SKU_ID = s$.SKU_ID
> WHERE ps$.STATION_ID = :Value
> >
>


-- 
Pascal.

jabber/gtalk: [EMAIL PROTECTED]
msn: [EMAIL PROTECTED]

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups 
"DbLinq" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/dblinq?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply via email to