Performance improvement by adding a larger scope clause



IBM's flagship relational database management system

Performance improvement by adding a larger scope clause

Postby RalphEagle » Wed Jul 03, 2019 3:08 pm

Hello,

I have some SQL queries which already look for very specific data in tables. I can add a larger scope clause to the filtering, but I was wondering if this was going to result in any performance improvement ?
EDIT: this is for a program which potentially makes these queries quite a lot.

For example, let's assume a classical employee table. In the WHERE clause I already have

--SQL
WHERE employee-name = :employee-name
      AND
      employee-number = :employee-number


Would it have any incidence if I add a clause

--SQL
AND employee-class = 'E'


which has obviously a much larger scope ? Let me precise that the employee number is actually stored on CHAR, not INTEGER nor VARCHAR.
RalphEagle
 
Posts: 24
Joined: Mon Apr 23, 2018 2:51 pm
Has thanked: 17 times
Been thanked: 0 time

Re: Performance improvement by adding a larger scope clause

Postby NicC » Wed Jul 03, 2019 5:23 pm

Is there an index on employee-number?
Surely employee-number is unique so why try and match on name as well?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Performance improvement by adding a larger scope clause

Postby RalphEagle » Wed Jul 03, 2019 7:03 pm

There is one index for both employee-name and employee-number. The 'employee-number' is not unique in my case ; I understand it does not make much sense for the example... We could imagine having employee-country instead of employee-name, and employees could have the same employee number but be in a different country, for the sake of the example coherence.
RalphEagle
 
Posts: 24
Joined: Mon Apr 23, 2018 2:51 pm
Has thanked: 17 times
Been thanked: 0 time

Re: Performance improvement by adding a larger scope clause

Postby RalphEagle » Tue Jul 09, 2019 2:03 pm

As far as I could research, a composed indice used appropriately (as is the case here) seems to be extremely efficient. So, we can suppose that adding a larger scope clause does not help tremendously ?
RalphEagle
 
Posts: 24
Joined: Mon Apr 23, 2018 2:51 pm
Has thanked: 17 times
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post