At 11:05 AM 15/06/2011, Ann Harrison wrote:

>>Jens,
>>
>>>> Example: SELECT id From PLAYER WHERE name='thename'
>>>>
>>>> If no record exists and the id returned is 0 i'm inserting the data.
>>>>
>>>> After 5000 inserts the inserts getting slow. I start with 2000 records
>>>> in 10 seconds, after 5000 inserts it slows down to 100 records in 10
>>>> seconds.
>>
>>
>>You need an index on PLAYERNAME, or you need to make PLAYERNAME
>>the first item in the unique constraint UNQ1_PLAYER. Firebird cannot use
>>the second or subsequent columns in an index unless it has values for all
>>the preceding columns.
>
>As Ann suggests....also consider the UPDATE OR INSERT syntax if you are using
>Firebird 2.1 or higher (but *still* improve that index!)

Another thing (unless you use UPDATE OR INSERT) is that using parameters and preparing once is far superior to issuing a new statement each time. So, rather than

SELECT id From PLAYER WHERE name='thename';
...
SELECT id From PLAYER WHERE name='anothername';
...
SELECT id From PLAYER WHERE name='yetanothername';
...

it is better to do

SELECT id From PLAYER WHERE name=:name;
Prepare;
Params[0].AsString:='thename';
First;
...
Close;
Params[0].AsString:='anothername';
...
Params[0].AsString:='yetanothername';
...

(of course, you'd typically do this in a 'while not eof' loop in most programming languages).

By the way, I hope you either simplified your problem quite a bit or that it is a small system - it would be strange to be denied participating in any game due to another player having the same name as me (well, my name is unique, so I don't have that problem, but many names are shared by several persons on this globe).

HTH,
Set