select sp.STUDENTSEQ, a.User_ID
from supprog sp
join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code
where a.USER_ID=37
PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_)) is slow.
Firebird simply thinks this is the best plan. As you know, it isn't and the
simplest way to tell the optimizer that it
This thread generated a lot of responses and I'd like to thank everyone for
helping me. Several came in after I left work yesterday and I want to
answer those, but it seems the consensus is to use the tweak || ''.
Thomas, thanks for the brief explanation of index statistics. That's helps
me a
and then
join with other tables)
De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Enviada em: terça-feira, 13 de maio de 2014 16:29
Para: firebird-support@yahoogroups.com
Assunto: [firebird-support] Query optimization mystery
I've got a query optimization mystery I
-support@yahoogroups.com
Assunto: Re: [firebird-support] Query optimization mystery
Try this:
select * from
(
select a.User_ID
from Advocate
where a.USER_ID=37
) as FILTER1, supprog sp
Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code
This doesn't quite execute. I cleaned it up
Em 13/5/2014 17:14, Kevin Donn kd...@msedd.com [firebird-support] escreveu:
Try this:
select * from
(
select a.User_ID
from Advocate
where a.USER_ID=37
) as FILTER1, supprog sp
Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code
This doesn't quite
This doesn't quite execute. I cleaned it up to this:
select * from (
select a.User_ID, a.ADVOCATE_CODE
from Advocate a
where a.USER_ID=37
) as FILTER1,supprog sp
Where sp.ADVOCATE_CODE=FILTER1.Advocate_Code
It gets the same plan: PLAN JOIN (SP NATURAL, FILTER1 A INDEX
Are the statistcs up to date ?
Statistics are not something I understand well. I have run SET STATISTICS
INDEX USV_SUPPROG_OWNER_USER_ID on all indexes of the two tables. I'm
guessing that brings them up to date, correct? No change in the query.
The two tables you mentioned has the same
On Tue, May 13, 2014 at 4:33 PM, 'Leyne, Sean'
s...@broadviewsoftware.com[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)
Hi Kevin,
let me first answer one of your particular questions:
I don't really
understand statistics but USV_SUPPROG_ADVOCATE_CODE
has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE
has 0.000422.
with the first index, you've about 3% of records with the same value in
index (these are going to be
s...@broadviewsoftware.com [firebird-support] firebird-
supp...@yahoogroups.com wrote:
Questions:
What about the SQL I recommended??
Sean
Oops:
If you know, that the stored procedure returns view records, connect ...
I meant: few records
--
Mit freundlichen Grüßen,
Thomas Beckmann
Diplom-Informatiker
Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckm...@assfinet.de
11 matches
Mail list logo
