Page 1 of 1

Performance improvement by adding a larger scope clause

PostPosted: Wed Jul 03, 2019 3:08 pm
by RalphEagle
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.

Re: Performance improvement by adding a larger scope clause

PostPosted: Wed Jul 03, 2019 5:23 pm
by NicC
Is there an index on employee-number?
Surely employee-number is unique so why try and match on name as well?

Re: Performance improvement by adding a larger scope clause

PostPosted: Wed Jul 03, 2019 7:03 pm
by RalphEagle
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.

Re: Performance improvement by adding a larger scope clause

PostPosted: Tue Jul 09, 2019 2:03 pm
by RalphEagle
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 ?