Home » Other » Training & Certification » Which query is more efficient regarding db performance??
Which query is more efficient regarding db performance?? [message #287815] Thu, 13 December 2007 11:39 Go to next message
ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal
Junior Member
I have a table which contains huge data. And now
which query will perform better, SQL1 or SQL2 ?

Quote:

SQL1> select * from mytable where sal != 1000


or

Quote:

SQL2> select * from mytable where sal < 1000 or sal > 1000


Re: Which query is more efficient regarding db performance?? [message #287819 is a reply to message #287815] Thu, 13 December 2007 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did this give you when you tried?

Regards
Michel
Re: Which query is more efficient regarding db performance?? [message #287824 is a reply to message #287815] Thu, 13 December 2007 12:38 Go to previous messageGo to next message
ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal
Junior Member
Its a query about performance issue which i just imagine. I have not face it practically.
In addition, if the salary field has an index and if it has not then if there any difference between sql1 and sql2 exists regarding faster retrieval?
Thanks
Re: Which query is more efficient regarding db performance?? [message #287825 is a reply to message #287815] Thu, 13 December 2007 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
It depends.
What did you own benchmarks show you?
Re: Which query is more efficient regarding db performance?? [message #287827 is a reply to message #287824] Thu, 13 December 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No difference as long as < and > are defined.

Regards
Michel

[Updated on: Thu, 13 December 2007 12:49]

Report message to a moderator

Re: Which query is more efficient regarding db performance?? [message #287835 is a reply to message #287815] Thu, 13 December 2007 13:28 Go to previous messageGo to next message
ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal
Junior Member
Ok am I right
If salary has an index then there is no difference.
If not then sql1 will perform faster than sql2 because in sql2 and additional and checking is performed. And both are using FTS.
Re: Which query is more efficient regarding db performance?? [message #287836 is a reply to message #287835] Thu, 13 December 2007 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 13 December 2007 19:49

No difference as long as < and > are defined.

Regards
Michel


Re: Which query is more efficient regarding db performance?? [message #287837 is a reply to message #287815] Thu, 13 December 2007 13:47 Go to previous messageGo to next message
ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal
Junior Member
I need more explanation why my assumption
Quote:

If salary has an index then there is no difference.
If not then sql1 will perform faster than sql2 because in sql2 and additional and checking is performed. And both are using FTS.
is wrong??

Thanks
Re: Which query is more efficient regarding db performance?? [message #287838 is a reply to message #287837] Thu, 13 December 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Benchmark it.

Regards
Michel
Re: Which query is more efficient regarding db performance?? [message #287898 is a reply to message #287815] Fri, 14 December 2007 00:33 Go to previous messageGo to next message
ora_dilemma
Messages: 10
Registered: December 2007
Location: Universal
Junior Member
in sql1 just one checking is performed
In sql2 both checking < and > are performed. So it should take time longer.
Thats my view.
Re: Which query is more efficient regarding db performance?? [message #287905 is a reply to message #287898] Fri, 14 December 2007 00:42 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is good to have an assumption to start a research.
Now you have to prove your assumption otherwise it is useless.
So build a test case and time.

Regards
Michel
Previous Topic: Need Research topic about Data Warehousing
Next Topic: Upgradation
Goto Forum:
  


Current Time: Fri Dec 02 19:03:24 CST 2016

Total time taken to generate the page: 0.08145 seconds