|
View previous topic :: View next topic
|
| Author |
Message |
nikyojin
New User
Joined: 05 Oct 2005
Posts: 94
|
|
|
|
Please let me know which will be the most appropriate query to be used in program and why?
| Code: |
1) SELECT COUNT(*)
FROM TPAPUAG
WHERE CON_CODE = 'CON01'
AND PRAG_NO = 1201
AND PUAG_SEQ_NO = 1; |
| Code: |
2) SELECT PRAG_NO
FROM TPAPUAG
WHERE CON_CODE = 'CON01'
AND PRAG_NO = 1201
AND PUAG_SEQ_NO = 1; |
| Code: |
3) SELECT 1
FROM TPAPUAG
WHERE CON_CODE = 'CON01'
AND PRAG_NO = 1201
AND PUAG_SEQ_NO = 1; |
I feel the 3rd option is the best. |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
Since the three querys are going to return three different results the most appropriate 1 would be the 1 that returns what you need!
If there are 1,000 rows in the table that satisfy the where clause then option 1 will return 1,000 and option 2 will return 1201 1,000 times and option 3 will return 1 1,000 times! |
|
| Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008
Posts: 35
Location: bangalore
|
|
|
|
Hi nikyojin,
To add what to Carl said, if its only the existence of a particular record that you are interested in, I would think using the clause,
| Code: |
OPTIMIZE FOR 1 ROW ONLY FETCH FIRST 1 ROW ONLY
|
along with the SQL.
regards,
ASCI(I) |
|
| Back to top |
|
|
nikyojin
New User
Joined: 05 Oct 2005
Posts: 94
|
|
|
|
I'm sorry since all the fields making the primary key is included in the where condition.
So the no of rows returned will always be 1.
Sorry for the confusion once again. Please reply |
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
If WHERE Clause has only Primary Keys, then 3rd query will take less time when compared to other two queries. Since it needs to check only the existence of the record.
1)
Primary Keys being used, will go via Index path and if value is exist then it will return 1.
2)
Then 2nd Query. It needs to find out the existence and select the column value.
3)
Then 1st Query. It needs to find out the matching records (In this case it is 1) and apply the scalar function COUNT on that. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
I think there is no difference in the 3 queries as the index is always the same and in the same order ... EXPLAIN on these 3 queries will give the same o/p in PLAN_TABLE and DSN_STATEMNT_TABLE ...
Correct me if im wrong !!! |
|
| Back to top |
|
|
|
|
