locked
"Attach to external database" extremely slow with Oracle

    Question

  • I am using Oracle provider from DevArt.

    After selecting connection, "Attach Data Source Wizard" needs 19 minutes to show the tables.

    If I do the same in normal programm "Entity Data Model Wizard" needs 10 seconds to show all objects.

    The problem is in the select statement produced from "Attach Data Source Wizard".

    There are 3 inner order by without any meaning. If I delete them, the statement is executed for 1:50 minutes.

    There is another order by at the end of the statement. I doubt , it is needed too.


    Regards


    Thursday, September 16, 2010 12:27 PM

Answers

  • This problem isnt specific to oracle-lightswitch but can happen with other EF providers and LighSwitch too. Unfortunately I don't think there is any way to workaround this for now and you will have to take the initial hit of waiting to load the data tables while importing.

    As I mentioned we are investigating ways of improving this scenario but those changes will only be available in the next updated release of LightSwitch.

    Thanks

    Saurabh

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 22, 2010 8:26 PM
  • To improve the overall experience we will be looking at ways to avoid generating the full model so the select itself might become irrelevant.

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 23, 2010 12:19 AM

All replies

  • SELECT   "Project6".c2 AS c1,
             "Project6"."CatalogName" AS "CatalogName",
             "Project6"."SchemaName" AS "SchemaName",
             "Project6"."Name" AS "Name",
             "Project6".c1 AS c2,
             "Project6".c3 AS c3,
             "Project6".c4 AS c4,
             "Project6".c5 AS c5,
             "Project6".c6 AS c6,
             "Project6".c7 AS c7,
             "Project6".c8 AS c8,
             "Project6".c9 AS c9,
             "Project6".c10 AS c10,
             "Project6".c11 AS c11
        FROM (SELECT "Extent1"."CatalogName" AS "CatalogName",
                     "Extent1"."SchemaName" AS "SchemaName",
                     "Extent1"."Name" AS "Name",
                     "UnionAll1"."Name" AS c1,
                     "UnionAll1"."Ordinal" AS c2,
                     "UnionAll1"."IsNullable" AS c3,
                     "UnionAll1"."TypeName" AS c4,
                     "UnionAll1"."MaxLength" AS c5,
                     "UnionAll1"."Precision" AS c6,
                     "UnionAll1"."DateTimePrecision" AS c7,
                     "UnionAll1"."Scale" AS c8,
                     "UnionAll1"."IsIdentity" AS c9,
                     "UnionAll1"."IsStoreGenerated" AS c10,
                     CASE
                        WHEN ("Project5".c2 IS NULL) THEN 0
                        ELSE "Project5".c2
                     END AS c11
                FROM (
                      /*SViews {0B22944E-0F7B-48be-89B5-743B50E1FC06} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                      SELECT   v.owner || '.' || v.view_name AS "Id",
                               'Catalog1' AS "CatalogName",
                               v.owner AS "SchemaName",
                               v.view_name AS "Name",
                               v.text AS "ViewDefinition",
                               0 AS "IsUpdatable"
                          FROM SYS.all_views v LEFT OUTER JOIN user_tab_privs p
                               ON (    v.owner = p.owner
                                   AND p.grantee = USER
                                   AND p.table_name = v.view_name
                                   AND (p."PRIVILEGE" = 'SELECT' OR p."PRIVILEGE" = 'EXECUTE'))
                         WHERE v.owner = USER OR p.grantee = USER
                      ORDER BY 3, 1) "Extent1"
                     INNER JOIN
                     (SELECT "Extent2"."Id" AS "Id",
                             "Extent2"."Name" AS "Name",
                             "Extent2"."Ordinal" AS "Ordinal",
                             "Extent2"."IsNullable" AS "IsNullable",
                             "Extent2"."TypeName" AS "TypeName",
                             "Extent2"."MaxLength" AS "MaxLength",
                             "Extent2"."Precision" AS "Precision",
                             "Extent2"."DateTimePrecision" AS "DateTimePrecision",
                             "Extent2"."Scale" AS "Scale",
                             "Extent2"."IsIdentity" AS "IsIdentity",
                             "Extent2"."IsStoreGenerated" AS "IsStoreGenerated",
                             4 AS c1,
                             "Extent2"."ParentId" AS "ParentId"
                        FROM (
                              /*STableColumns {4D35D67E-B0EE-45e6-BA55-3238B93342FB} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                              SELECT   t.owner || '.' || t.table_name || '.' || t.column_name AS "Id",
                                       t.owner || '.' || t.table_name AS "ParentId",
                                       column_name AS "Name",
                                       column_id AS "Ordinal",
                                       ...,
                                       ...,
                                       NULL AS "Default"
                                  FROM SYS.all_tab_columns t LEFT OUTER JOIN user_tab_privs p
                                       ON (    t.owner = p.owner
                                           AND p.grantee = USER
                                           AND p.table_name = t.table_name
                                           AND (p."PRIVILEGE" = 'SELECT' OR p."PRIVILEGE" = 'EXECUTE'))
                                 WHERE t.owner = USER OR p.grantee = USER
                              ORDER BY 2, 4) "Extent2"
                      UNION ALL
                      SELECT "Extent3"."Id" AS "Id",
                             "Extent3"."Name" AS "Name",
                             "Extent3"."Ordinal" AS "Ordinal",
                             "Extent3"."IsNullable" AS "IsNullable",
                             "Extent3"."TypeName" AS "TypeName",
                             "Extent3"."MaxLength" AS "MaxLength",
                             "Extent3"."Precision" AS "Precision",
                             "Extent3"."DateTimePrecision" AS "DateTimePrecision",
                             "Extent3"."Scale" AS "Scale",
                             "Extent3"."IsIdentity" AS "IsIdentity",
                             "Extent3"."IsStoreGenerated" AS "IsStoreGenerated",
                             0 AS c1,
                             "Extent3"."ParentId" AS "ParentId"
                        FROM (
                              /*SViewColumns {02C89419-DB97-4abb-9F1A-6B313FAE7866} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                              SELECT t.owner || '.' || t.table_name || '.' || t.column_name AS "Id",
                                     t.owner || '.' || t.table_name AS "ParentId",
                                     column_name AS "Name",
                                     column_id AS "Ordinal",
                                     ...,
                                     NULL AS "Default"
                                FROM SYS.all_tab_columns t LEFT OUTER JOIN user_tab_privs p
                                     ON (    t.owner = p.owner
                                         AND p.grantee = USER
                                         AND p.table_name = t.table_name
                                         AND (p."PRIVILEGE" = 'SELECT' OR p."PRIVILEGE" = 'EXECUTE'))
                               WHERE t.owner = USER OR p.grantee = USER) "Extent3") "UnionAll1"
                     ON (0 = "UnionAll1".c1) AND ("Extent1"."Id" = "UnionAll1"."ParentId")
                     LEFT OUTER JOIN
                     (SELECT "UnionAll2"."Id" AS c1,
                             1 AS c2
                        FROM (
                              /*SViewConstraints {0C61BE7A-8D9D-4bf1-97B0-449F70A9470D} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                              SELECT CAST (NULL AS VARCHAR2 (1)) AS "Id",
                                     CAST (NULL AS VARCHAR2 (1)) AS "ParentId",
                                     CAST (NULL AS VARCHAR2 (1)) AS "Name",
                                     CAST (NULL AS VARCHAR2 (1)) AS "ConstraintType",
                                     0 AS "IsDeferrable",
                                     0 AS "IsInitiallyDeferred",
                                     CAST (NULL AS VARCHAR2 (1)) AS "Expression",
                                     CAST (NULL AS VARCHAR2 (1)) AS "UpdateRule",
                                     CAST (NULL AS VARCHAR2 (1)) AS "DeleteRule"
                                FROM DUAL
                               WHERE 0 = 1) "Extent4"
                             INNER JOIN
                             (SELECT 10 AS c1,
                                     "Extent5"."ConstraintId" AS "ConstraintId",
                                     "Extent6"."Id" AS "Id"
                                FROM (
                                      /*SConstraintColumns {F2DCFD5C-8B38-4e6c-92A5-34493F5A9237} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                                      SELECT cons.owner || '.' || cons.constraint_name AS "ConstraintId",
                                             cols.owner || '.' || cols.table_name || '.' || cols.column_name AS "ColumnId"
                                        FROM SYS.all_cons_columns cols INNER JOIN SYS.all_constraints cons
                                             ON (cols.constraint_name = cons.constraint_name AND cols.owner = cons.owner)
                                             LEFT OUTER JOIN user_tab_privs p
                                             ON (    cols.owner = p.owner
                                                 AND p.grantee = USER
                                                 AND p.table_name = cols.table_name
                                                 AND (p."PRIVILEGE" = 'SELECT' OR p."PRIVILEGE" = 'EXECUTE'))
                                       WHERE cons.owner = USER OR p.grantee = USER) "Extent5"
                                     INNER JOIN
                                     (
                                      /*STableColumns {4D35D67E-B0EE-45e6-BA55-3238B93342FB} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                                      SELECT   t.owner || '.' || t.table_name || '.' || t.column_name AS "Id",
                                               t.owner || '.' || t.table_name AS "ParentId",
                                               column_name AS "Name",
                                               column_id AS "Ordinal",
                                               ...,
                                               NULL AS "Default"
                                          FROM SYS.all_tab_columns t LEFT OUTER JOIN user_tab_privs p
                                               ON (    t.owner = p.owner
                                                   AND p.grantee = USER
                                                   AND p.table_name = t.table_name
                                                   AND (p."PRIVILEGE" = 'SELECT' OR p."PRIVILEGE" = 'EXECUTE'))
                                         WHERE t.owner = USER OR p.grantee = USER
                                      ORDER BY 2, 4) "Extent6" ON "Extent6"."Id" = "Extent5"."ColumnId"
                              UNION ALL
                              SELECT 7 AS c1,
                                     "Extent7"."ConstraintId" AS "ConstraintId",
                                     "Extent8"."Id" AS "Id"
                                FROM (
                                      /*SViewConstraintColumns {5AA2F6A5-AA81-4724-8593-55D34067FFEC} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                                      SELECT CAST (NULL AS VARCHAR2 (1)) AS "ConstraintId",
                                             CAST (NULL AS VARCHAR2 (1)) AS "ColumnId"
                                        FROM DUAL
                                       WHERE 0 = 1) "Extent7"
                                     INNER JOIN
                                     (
                                      /*SViewColumns {02C89419-DB97-4abb-9F1A-6B313FAE7866} {BFD32F32-EEE4-4826-9725-C489B0EE1ACC}*/
                                      SELECT t.owner || '.' || t.table_name || '.' || t.column_name AS "Id",
                                             t.owner || '.' || t.table_name AS "ParentId",
                                             column_name AS "Name",
                                             column_id AS "Ordinal",
                                             CASE nullable
                                                WHEN 'Y' THEN 1
                                                ELSE 0
                                             END AS "IsNullable",
                                             ...,
                                             NULL AS "Default"
                                        FROM SYS.all_tab_columns t LEFT OUTER JOIN user_tab_privs p
                                             ON (    t.owner = p.owner
                                                 AND p.grantee = USER
                                                 AND p.table_name = t.table_name
                                                 AND (p."PRIVILEGE" = 'SELECT' OR p."PRIVILEGE" = 'EXECUTE'))
                                       WHERE t.owner = USER OR p.grantee = USER) "Extent8" ON "Extent8"."Id" = "Extent7"."ColumnId"
                                     ) "UnionAll2" ON (7 = "UnionAll2".c1) AND ("Extent4"."Id" = "UnionAll2"."ConstraintId")
                       WHERE "Extent4"."ConstraintType" = 'PRIMARY KEY') "Project5" ON "UnionAll1"."Id" = "Project5".c1
                     ) "Project6"
    ORDER BY "Project6"."SchemaName" ASC, "Project6"."Name" ASC, "Project6".c2 ASC

    Friday, September 17, 2010 11:06 AM
  • Thanks for reporting this issue.

    Could you confirm which version of the data provider you are seeing this issue with?

    Thanks

    Saurabh


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, September 20, 2010 10:59 PM
  • dotConnect for Oracle 5.70 (DevArt)  (The same is with version 6 beta)

    And this is the question in DevArt forum:

    http://www.devart.com/forums/viewtopic.php?t=19010&sid=9c2f027a1a444b6a5a4fec6b5038e867

    "The problem you are encountering is probably an integration one, whereas integration with VS LightSwitch is not supported yet.

    We will investigate it and inform you about the results, but please notice that dotConnect for Oracle is not supposed to be used with the LightSwitch VS edition, nor do we guarantee they are compatible."

    That is not an answer for me.

    And in this video :

    http://channel9.msdn.com/shows/Going+Deep/Steve-Anonsen-and-John-Rivard-Inside-LightSwitch/

    " We support SQL ... SQL is any EDM provider..."  Minute 60:20

    You have abandoned Oracle provider and Oracle does not care about an EDM provider.

    Neither company has an interest to work together.

    And what I have from DevArt are things like this above or something like "EF is not good done", "use LINQ" or "use our  Entity Developer".

    Yes, I know that this is a beta version, but when I ask a simple question, I am expecting somebody to see for 5 minutes what happen and to explain it. I am not expecting immediately repair.

    Thank you.

    Regards

     

    Tuesday, September 21, 2010 8:10 AM
  • Thanks for your patience.

     

    This looks like it might be due to how LightSwitch populates the list of tables. LightSwitch generates a full Entity Framework model prior to showing that list in the data attach wizard. In the Entity Data Model Wizard only a list of tables is pulled from the database. The provider is generating the select statement but only because LightSwitch requested a full EF Model.

     

    We are investigating how we can improve the performance of this scenario in LightSwitch.

    Thanks again for reporting the problem.

    Saurabh

     

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by AndreAlvesLima Wednesday, September 22, 2010 5:08 PM
    Wednesday, September 22, 2010 5:03 PM
  • I've also faced the same problem as Mottor, so can you provide any workaround for this oracle-lightswitch problem?
    Wednesday, September 22, 2010 5:12 PM
  • This problem isnt specific to oracle-lightswitch but can happen with other EF providers and LighSwitch too. Unfortunately I don't think there is any way to workaround this for now and you will have to take the initial hit of waiting to load the data tables while importing.

    As I mentioned we are investigating ways of improving this scenario but those changes will only be available in the next updated release of LightSwitch.

    Thanks

    Saurabh

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, September 22, 2010 8:26 PM
  • Thank you,

    This was an answer, which I have expected.

    Only one question:

    Is not possible to delete the order by clause when you (or EF provider) combine select statemenst?

    Regards

    Wednesday, September 22, 2010 8:33 PM
  • To improve the overall experience we will be looking at ways to avoid generating the full model so the select itself might become irrelevant.

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 23, 2010 12:19 AM
  • Yes, this is the best solution.

    The normal way is first to select the schema and than the table.

    And columns are selected pro table, not for the whole datebase. (I have 150000 rows in all_tab_columns)

    Regards

    Thursday, September 23, 2010 9:35 PM
  • Hi ,

    Any news on this problem ? We are pritty much stuck and would realy much like to test the technology which is a problem when you are stuck at the first wizard screen

     

    Sunday, November 14, 2010 11:25 AM