I am using VS 2010, EF 4.0 with Firebird Dot Net Provider 2.6.5.0
When I use ObjectSet.ToList, the provider creates an select statement for
each and every record in the list and it takes forever to load an list of
10000 records.
But instead if I use like
"From item in Objectset select item.id, item.." It only creates one single
statement and in blink of an eye it loads all the records. I am also
attaching the SQL statements that is sent by the provider to the database.
_____
ObjectSet.ToList creates one select statement for each record as follows and
takes forever
SELECT
"Extent1"."ID" AS "ID",
"Extent1"."CATEGORYID" AS "CATEGORYID",
"Extent1"."VENDORID" AS "VENDORID",
"Extent1"."ITEMNAME" AS "ITEMNAME",
"Extent1"."TAXABLE" AS "TAXABLE",
"Extent1"."ITEMTYPE" AS "ITEMTYPE"
FROM "ITEM" AS "Extent1"
WHERE "Extent1"."ID" = @EntityKeyValue1
FirebirdClient: Parameters:
FirebirdClient: Name:EntityKeyValue1 Type:Integer
Used Value:5
FirebirdClient: Command:
SELECT
"Extent1"."ID" AS "ID",
"Extent1"."CATEGORYID" AS "CATEGORYID",
"Extent1"."VENDORID" AS "VENDORID",
"Extent1"."ITEMNAME" AS "ITEMNAME",
"Extent1"."TAXABLE" AS "TAXABLE",
"Extent1"."ITEMTYPE" AS "ITEMTYPE"
FROM "ITEM" AS "Extent1"
WHERE "Extent1"."ID" = @EntityKeyValue1
FirebirdClient: Parameters:
FirebirdClient: Name:EntityKeyValue1 Type:Integer
Used Value:6
FirebirdClient: Command:
SELECT
"Extent1"."ID" AS "ID",
"Extent1"."CATEGORYID" AS "CATEGORYID",
"Extent1"."VENDORID" AS "VENDORID",
"Extent1"."ITEMNAME" AS "ITEMNAME",
"Extent1"."TAXABLE" AS "TAXABLE",
"Extent1"."ITEMTYPE" AS "ITEMTYPE"
FROM "ITEM" AS "Extent1"
WHERE "Extent1"."ID" = @EntityKeyValue1
FirebirdClient: Parameters:
FirebirdClient: Name:EntityKeyValue1 Type:Integer
Used Value:9
FirebirdClient: Command:
SELECT
"Extent1"."ID" AS "ID",
"Extent1"."CATEGORYID" AS "CATEGORYID",
"Extent1"."VENDORID" AS "VENDORID",
"Extent1"."ITEMNAME" AS "ITEMNAME",
"Extent1"."TAXABLE" AS "TAXABLE",
"Extent1"."ITEMTYPE" AS "ITEMTYPE"
FROM "ITEM" AS "Extent1"
WHERE "Extent1"."ID" = @EntityKeyValue1
FirebirdClient: Parameters:
FirebirdClient: Name:EntityKeyValue1 Type:Integer
Used Value:8
FirebirdClient: Command:
And goes on and on...
_____
>From item in ObjectSet creates just one sql statement as follows and loads
instantly
SELECT
"Extent1"."VENDORNAME" AS "VENDORNAME"
FROM "VENDOR" AS "Extent1"
ORDER BY "Extent1"."VENDORNAME" ASC
FirebirdClient: Parameters:
FirebirdClient: Command:
SELECT
"Extent1"."DEPARTMENTNAME" AS "DEPARTMENTNAME"
FROM "DEPARTMENT" AS "Extent1"
ORDER BY "Extent1"."DEPARTMENTNAME" ASC
FirebirdClient: Parameters:
FirebirdClient: Command:
SELECT
"Extent1"."CATEGORYNAME" AS "CATEGORYNAME"
FROM "CATEGORY" AS "Extent1"
ORDER BY "Extent1"."CATEGORYNAME" ASC
FirebirdClient: Parameters:
FirebirdClient: Command:
SELECT
"Extent1"."ID" AS "ID",
"Extent3"."VENDORNAME" AS "VENDORNAME",
"Extent6"."DEPARTMENTNAME" AS "DEPARTMENTNAME",
"Extent7"."CATEGORYNAME" AS "CATEGORYNAME",
"Extent4"."ITEMNAME" AS "ITEMNAME",
"Extent1"."STYLENAME" AS "STYLENAME",
"Extent1"."BARCODE" AS "BARCODE",
"Extent1"."LOOKUP" AS "LOOKUP",
"Extent1"."LOCATION" AS "LOCATION",
"Extent1"."VENDORITEMNUMBER" AS "VENDORITEMNUMBER",
"Extent1"."COST" AS "COST",
"Extent1"."PRICE" AS "PRICE",
"Extent1"."STOCK" AS "STOCK",
"Extent1"."CASEPACK" AS "CASEPACK",
"Extent1"."REORDERLEVEL" AS "REORDERLEVEL",
"Extent1"."REORDERQUANTITY" AS "REORDERQUANTITY",
"Extent1"."AUTOREORDER" AS "AUTOREORDER",
"Extent1"."SEASONTYPE" AS "SEASONTYPE",
"Extent1"."DISCOUNTALLOWED" AS "DISCOUNTALLOWED",
"Extent4"."TAXABLE" AS "TAXABLE",
"Extent1"."ISACTIVE" AS "ISACTIVE"
FROM "ITEMSTYLE" AS "Extent1"
INNER JOIN "ITEM" AS "Extent2" ON "Extent1"."ITEMID" = "Extent2"."ID"
LEFT OUTER JOIN "VENDOR" AS "Extent3" ON "Extent2"."VENDORID" =
"Extent3"."ID"
LEFT OUTER JOIN "ITEM" AS "Extent4" ON "Extent1"."ITEMID" = "Extent4"."ID"
LEFT OUTER JOIN "CATEGORY" AS "Extent5" ON "Extent4"."CATEGORYID" =
"Extent5"."ID"
LEFT OUTER JOIN "DEPARTMENT" AS "Extent6" ON "Extent5"."DEPARTMENTID" =
"Extent6"."ID"
LEFT OUTER JOIN "CATEGORY" AS "Extent7" ON "Extent4"."CATEGORYID" =
"Extent7"."ID"
FirebirdClient: Parameters:
The thread '<No Name>' (0x176c) has exited with code 0 (0x0).
The thread '<No Name>' (0x15e8) has exited with code 0 (0x0).
The thread '<No Name>' (0x1610) has exited with code 0 (0x0).
The thread '<No Name>' (0x1568) has exited with code 0 (0x0).
The program '[5048] PearlPOS.vshost.exe: Managed (v4.0.30319)' has exited
with code 0 (0x0).
_____
Any help is highly appreciated
Thanks!
Muthu Annamalai
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2dcopy1
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
