Subject Re: How can I skip an Insert ?
Author Stefan Renzewitz
--- In, Svein Erling Tysvær <svein.
erling.tysvaer@k...> wrote:
> Hi Stefan!
> I haven't got a clue how you store things, but as far as I know any
> field can consist of one out of 13 values (either it is empty or it
> populated by one out of the six kinds of black and white officers
> whatever it is called in English, I've never played chess with an
> Englishspeaking person). 13 isn't a very selective number, so I'd
> rather multiply things and store them line A..H separately. 13 to
> power of 8 should be 815730721 and then the inserts would be simply:
> INSERT INTO ChessMove(OldA, OldB ... OldH, NewA ... NewH)
> VALUES ...
> WHERE NOT EXISTS(Select * FROM ChessMove
> WHERE OldA = ... AND NewH = ...)
> With 16 indexes this should be a pretty quick operation,
> Set

Thanks for your reply! The way I store the positions is not an issue I
believe. I'm right now storing the FEN representation of a position as
char() (FEN is international standard for representing chess
positions). I'm going to move to Huffman encoding though which will
reduce the required char size. Still quite big for an index I guess
with about 40 or so characters.

Anyway, to use a stored procedure and let it do both steps in one is
an excellent idea which I should have got myself too.

One concern though: Right now my strategy was to first check for each
postion whether its new or not and add those new ones to an arraylist
and not right to the database. After I'm done with that I'm inserting
them all in one run. I thought this would give me some performance
benefits. I'm even first checking this arraylist whether its a
position I already checked or not before I execute any database query.
Because if I checked it already it can't be unique anymore thus no
database query necessary. This saves me a lot of time. I did several
performance tests. In my scenario I have about 14.000 queries for my
arraylist which need 3 seconds and about 5000 database queries which
need 25 seconds.