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