Re: Query Taking a Long Time
Last night I wrote:
>=7F
>_mm}?Rsn ;dIa7$X,/uHJz-HF>04$8WJlw6$fx.}x(#F"KJtlWe7W=3D-Qh1KQ5&_QUCNd()3cx
Etc.
And I swear it made perfect sense at the time... :)
Sorry folks, I've got a new mailer that I'm obviously getting wrong. Anyway,
what I tried to say was:
> I have a query that is taking an inordinate amount of time. I have=20
>allowed it to run for 8 hours without getting a result. Yes it involves=20
>a join of two tables . One with 5.6 million records , the other table has
>2.8 million records. Working with an HP9000 T5500 box with 2 cpus.
>
>Below is the query and the results of set explain. Any ideas? Thanks.
>
>set explain on;
>set pdqpriority high;
I think you "SET PDQPRIORITY n", where 0 <=3D n <=3D 100. I suspect that the
date you're excluding is not going to account for a large number of rows, if
so, an index on booked_date probably will not help. I would, however, index
b.disb_id.
Have you done an UPDATE STATISTICS HIGH recently?
Also, if MAXPDQPRIORITY =3D (say) 30, then even SET PDQPRIORITY 100 will not
set PDQ above 30.
>select loan_type_code, count(*)
>from disbursements a,disburse_activity b
>where booked_date !=3D '1858/11/17'
>and a.disb_id =3D b.disb_id
>group by loan_type_code>
>1) pwages01.b: SEQUENTIAL SCAN
> Filters: pwages01.b.booked_date !=3D 1858/11/17
>
>2) pwages01.a: INDEX PATH
> (1) Index Keys: disb_id
> Lower Index Filter: pwages01.a.disb_id =3D pwages01.b.disb_id
HTH.
--
Cheers,
Billy.
----------------------------------------------------------------------------=
-
billy.wheeler@pixie.co.za +27 11 803=
2151
p.o. box 3463, rivonia, rsa, 2128 +27 83 250=
2324
"Have you got it yet?" (c) Kitsch 'n' Sync Productions 1996
----------------------------------------------------------------------------=
-