On Tue, May 13, 2014 at 4:33 PM, 'Leyne, Sean' Sean@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

Questions: 

1- What indexes do you have defined on a.User_ID?

 CREATE INDEX IDX_ADVOCATE1 ON ADVOCATE (USER_ID);

2- What is the selectivity (aka uniqueness) of User_ID and Advocate_Code?

I'm not sure I understand the question.  I mentioned in a post I just did a few minutes ago that SupProg has 929329 records and 2792 where Advocate_Code is not null.  Advocate has 2448 records and currently 0 records where User_ID is not null, although that will increase to may 200 as it goes into production.  The ratio of non-null record will increase after this goes into production.

 

 3- Is searching by User_ID and Advocate_Code a common query?

 The hope is that it will become so, but it the badly optimized query run in about 1.5s which is too high for production.