Results 1 to 8 of 8

Thread: Speedproblems with SQL-View with 3 tables

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Feb 2009
    Location
    Germany, Mannheim
    Posts
    427

    Default Speedproblems with SQL-View with 3 tables

    Hello,

    The described speed problems refer ONLY to DataflexViews/-SelectionLists.
    There are no problems in the SQL-Managemant-Studio.


    I have a SQL-View, where 3 tables are linked together and i want to sort by fields of each table

    Code:
    Table 1: AccountNumber -------> Table2: Account.AccountNumber 
             ID            -------> Table3: Customer.ID
    
    The following indexes exist:
    Table 1: Account Number
    Table 1: ID
    
    Table 2: Account number
    Table 2: Type
    Table 2: Beginning
    
    Table 3: ID
    Table 3: Name
    Now I built a Dataflexview and a Selectionlist for this SQL-View.
    Everything is displayed correctly, scrolling in the fields AccountNumber and ID (Based on table 1) works without problems. But if I want to browse in the name (table 3) or type (table 2), it takes an eternity until the next record is found (at least half an hour). The same happens when I jump to the column Names, type in the selection list.

    Conclusion: Scrolling in Table 1 is easy and fast, scrolling in Table 3 or 2 takes too long.

    If I now remove table 2 from the SQL-View, there are no speed problems.

    Same result if I want to scroll in fields of table 2. As long as table 3 is in the SQL-View, it takes forever. Without Table 3 the result is there quickly.

    SQL-Script for the SQL-View:
    Code:
    SELECT dbo.Table1.Accountnumber AS T1Accountnumber, dbo.Table1.ID, 
           dbo.Table2.Accountnumber AS T2Accountnumber, dbo.Table2.Type, dbo.Table2.Beginning, 
           dbo.Table3.Name
    FROM dbo.Table1 INNER JOIN dbo.Table2 ON dbo.Table1.Accountnumber = dbo.Table2.Accountnumber 
                    INNER JOIN dbo.Table3 ON dbo.Table1.ID = dbo.Table3.ID
    Last edited by Roman Köhler; 17-Sep-2020 at 03:56 AM.
    Regards / Viele Grüße

    Roman Köhler
    Gartenstadt-Genossenschaft Mannheim eG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •