Kjell,

> Ann W. Harrison skriver:
> > Before going into a full investigation, does the problem happen
> > whenever you start the database or just with an insert to that
> > particular table?
>
> Insert in a different table was fast. Selects, including from "Uppgift"
> are fast, as was noted using select from mon$statements when my
> application was running. Insert into "Uppgift" takes... 39 seconds.
> Deleting the record again was instantaneous. So why does it take forever
> (20-60 minutes) when doing it from my application?
>
> > What happens
> > with a SELECT FIRST 1 on that table, with no order by?
>
> Fast even with rows 1000.
>
> > What happens with an indexed select?
>
> Instantaneous.

Is the select based on one of the columns which have the index with the deadly poor selectivity (148721942 dups out of 148721943 entries)!!!

> Database header page information:

> Variable header data:
> Database backup GUID: {D0FF9040-0002-47C9-B487-FC68B87751DF}
> Sweep interval: 20000
> *END*

Is it possible you are running into a sweep?

> Analyzing database pages ...

> Index IX_Uppgift_Länkobjekt (3)
> Depth: 3, leaf buckets: 91591, nodes: 148721943
> Average data length: 0.00, total dup: 148721942, max dup: 148721942
> Fill distribution:

You have to get rid of indexes like this!!! They are much more overhead than they are worth.


I suspect that the source problem is that the insert is having to navigate the large dup lists in order to update the indexes. This is causing a large number of pages to be loaded into cache. Once loaded, however, they are easy/quickly accessed by subsequent operations.


Sean