Dev6
asked on
Interbase optimizing SQL Query
I use Interbase 6.01 and Firebird
and have following sql code
What indexes do i need for otimizing this code ?
I have over a half million of rows in this table ?
Is it better to use one index with many columns or generate for every column an individual index ? Whats better for speed and size ?
'SELECT A.ID, A.AUFTRAGID, A.TEILID, A.DATUM, A.PARENTID, A.DAUER, '+
' M.PARENTID AS MASCHINE '+
' FROM AUFTRAG_KOSTEN A '+
' LEFT OUTER JOIN AUFTRAG_KOSTEN M ON M.ID = A.OTHER_ID '+
' WHERE A.ART = 0 AND A.DATUM >= :VON AND A.DATUM < :BIS ORDER BY A.PARENTID, A.DATUM'
and have following sql code
What indexes do i need for otimizing this code ?
I have over a half million of rows in this table ?
Is it better to use one index with many columns or generate for every column an individual index ? Whats better for speed and size ?
'SELECT A.ID, A.AUFTRAGID, A.TEILID, A.DATUM, A.PARENTID, A.DAUER, '+
' M.PARENTID AS MASCHINE '+
' FROM AUFTRAG_KOSTEN A '+
' LEFT OUTER JOIN AUFTRAG_KOSTEN M ON M.ID = A.OTHER_ID '+
' WHERE A.ART = 0 AND A.DATUM >= :VON AND A.DATUM < :BIS ORDER BY A.PARENTID, A.DATUM'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In this case, whch coloumns should i use ?
ART (in Where)
DATUM (in Where and Order)
OTHER_ID (in JOIN) ?????
PARETID (in Order) ?????
ART (in Where)
DATUM (in Where and Order)
OTHER_ID (in JOIN) ?????
PARETID (in Order) ?????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:
to split points as follows:
66 points for thegroup
66 points for grolschisgood
67 points for YodaMage
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
kacor
EE Cleanup Volunteer
I will leave a recommendation in the Cleanup topic area for this question:
to split points as follows:
66 points for thegroup
66 points for grolschisgood
67 points for YodaMage
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
kacor
EE Cleanup Volunteer
case 2 : Create Index Index1 On table1 (field1)
Create Index Index2 On table1 (field2)
Create Index Index3 On table1 (field3)
Case 1 will be more efficient *if* you only
ever do queries which reference all three columns in the WHERE clause. Otherwise choose case 2.