what is the best interface for chess analysis of games

[Werewolf]

ChessAssistant is also nice, and Aquarium is not bad.
I use Arena a lot just because of familiarity and what I use it for (Epd analysis and game contests)
Scid has a compact game format, but even though they keep extending the capability it runs out of steam if you throw hundreds of millions of games at it.
Too bad Mattias' GUI is no longer developed. It had some nice features.
There are some others that are good for high speed contest games and other things.
I don't feel like any of them really meet my needs, so I put my data into SQL Server and use SQL queries to learn what I want to know.

Aquarium is the best of the best for analysis, but not for complete games.

[glav]

Hi, Dan. You are really an expert in this field and it is always nice to get your point of view. Could you give some example of the things you address with SQL? May be some of us could be motivated by them ans start thinking out of the box like you do.

[Dann Corbit]

I have the EPD records atomized into their EPD fields. The records look like this:
USE [Chess]
GO

/****** Object: Table [dbo].[Epd] Script Date: 5/12/2024 6:11:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Epd](
[Epd] [varchar](4096) NULL,
[acd] [smallint] NULL,
[acn] [int] NULL,
[ce] [smallint] NULL,
[am] [varchar](255) NULL,
[dm] [smallint] NULL,
[id] [varchar](255) NULL,
[CheckoutCount] [smallint] NULL,
[c0] [varchar](255) NULL,
[c1] [varchar](255) NULL,
[c2] [varchar](255) NULL,
[c3] [varchar](255) NULL,
[c4] [varchar](255) NULL,
[acs] [int] NULL,
[EpdID] [uniqueidentifier] NOT NULL,
[tag] [char](10) NULL,
[white_wins] [int] NULL,
[black_wins] [int] NULL,
[draws] [int] NULL,
[coef] [float] NULL,
[bm] [varchar](4096) NULL,
[pm] [varchar](4096) NULL,
[Opening] [varchar](400) NULL,
[games] AS (([white_wins]+[black_wins])+[draws]),
[pv] [varchar](1000) NULL,
[c5] [varchar](1000) NULL,
[c6] [varchar](max) NULL,
[nid] [varchar](64) NULL,
[varadic] [varchar](200) NULL,
[c7] [varchar](6000) NULL,
[c8] [varchar](1000) NULL,
[c9] [varchar](1000) NULL,
[hash] [numeric](23, 0) NULL,
CONSTRAINT [PK_Epd_1] PRIMARY KEY CLUSTERED
(
[EpdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Epd] ADD CONSTRAINT [DF_Epd_EpdID] DEFAULT (newid()) FOR [EpdID]
GO

ALTER TABLE [dbo].[Epd] ADD CONSTRAINT [DF_Epd_white_wins] DEFAULT ((0)) FOR [white_wins]
GO

ALTER TABLE [dbo].[Epd] ADD CONSTRAINT [DF_Epd_black_wins] DEFAULT ((0)) FOR [black_wins]
GO

ALTER TABLE [dbo].[Epd] ADD CONSTRAINT [DF_Epd_draws] DEFAULT ((0)) FOR [draws]
GO

The field coef is calculated as:
USE [Chess]
GO

/****** Object: StoredProcedure [dbo].[UpdateCoef] Script Date: 5/12/2024 6:13:41 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[UpdateCoef] as
BEGIN
UPDATE Epd SET coef = (white_wins - black_wins)* 1.0/(1e-20+white_wins+black_wins+draws)
UPDATE Epd SET coef = -coef WHERE Epd like '% b %'
END
GO

Using coef, I can easily compute an "observed" ce as: round(coef * 444.0,0) as oce, which gives me an estimate of how well a position scores in real games that is closely related to centipawns, but has a limit of +/- 444 centipawns.

I calculate the distance to mate with this procedure:
USE [Chess]
GO

/****** Object: StoredProcedure [dbo].[UpdateDM2] Script Date: 5/12/2024 6:17:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[UpdateDM2] as
BEGIN
update Epd SET dm = d.dm from Epd e, dmtab d where e.ce = d.ce and e.ce > 30000 and (e.dm IS NULL or e.dm > d.dm);
update Epd SET dm = d.dm from Epd e, dmtab d where e.ce = d.ce and e.ce < -30000 and (e.dm IS NULL or abs(e.dm) > abs( d.dm) );
END

GO

I have trillions of positions in my database. I have most every position from engine contests like CCRL, CEGT, TCEC, etc.
I also have every position from databases like the ChessAssistant, ChessBase, TWIC, LiChess, etc stored in a view called ContestGames that looks like this:
USE [Chess]
GO

/****** Object: View [dbo].[ContestGames] Script Date: 5/12/2024 6:21:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ContestGames] as
SELECT * FROM dbo.Catchall UNION ALL
SELECT * FROM dbo.Ccc3 UNION ALL
SELECT * FROM dbo.Graham UNION ALL
SELECT * FROM dbo.Grob UNION ALL
SELECT * FROM dbo.ICCF UNION ALL
SELECT * FROM dbo.Playchess UNION ALL
SELECT * FROM dbo.SSDF UNION ALL
SELECT * FROM dbo.Lichess UNION ALL
SELECT * FROM dbo.Scct UNION ALL
SELECT * FROM dbo.Tcec UNION ALL
SELECT * FROM dbo.Junkbase UNION ALL
SELECT * FROM dbo.Ylcet UNION ALL
SELECT * FROM dbo.ccrl4040 UNION ALL
SELECT * FROM dbo.cegt40120 UNION ALL
SELECT * FROM dbo.cegt4020 UNION ALL
SELECT * FROM dbo.cegt4020p UNION ALL
SELECT * FROM dbo.cegt5p3 UNION ALL
SELECT * FROM dbo.twic UNION ALL
select * from dbo.Ccls union all
select * from dbo.mig2004 union all
select * from dbo.Sedat union all
select * from dbo.OmCorr
GO

I have atomized these games into structures like this:
USE [Chess]
GO

/****** Object: Table [dbo].[ccrl4040] Script Date: 5/12/2024 6:23:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ccrl4040](
[Epd] [varchar](255) NOT NULL,
[acd] [smallint] NULL,
[ce] [smallint] NULL,
[Engine] [varchar](255) NOT NULL,
[acs] [int] NULL,
[bm] [varchar](15) NULL,
[result] [char](3) NULL,
CONSTRAINT [PK_CCRL4040] PRIMARY KEY CLUSTERED
(
[Epd] ASC,
[Engine] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

This has the unfortunate side effect that human players are called Engines, but I suppose that they are in some sense.
I can find win/loss/draw statistics for just about any position. I can see how different engines scored it. I can see positions that some engines struggle with and others don't. I can see positions that engines evaluate as great, but that lose in practice. I can see positions that win in practice, but engines found a way to win. Because the positions are atomized into elements, I can ask any sort of statistical question that interests me.
I have thousands of queries that I have saved. I can turn my EPD records into Formal EPD strings that are recognized by chess engines.

My friend Les Fernandez wrote me a nice utility that does all sorts of marvelous things. It will take PGN games from any contest and turn them into fully decorated EPD records. If there is no data associated with the EPD record, I still collect the won/loss/drawn value so I can calculate oce better.
It also creates heat maps and many other things. I can create a heat map of the 7th move by piece type and filter by won/loss/drawn outcome. In this case, we can make a very elaborate PSQT based on actual game outcomes.

[Dann Corbit]

This is what CUT looks like:

[Dann Corbit]

This is a query that shows previous analysis of a position in a recent thread where the depth was at least 36:

[glav]

Thanks, Dann. Very interesting and original stuff.

[towforce]

This is a query that shows previous analysis of a position in a recent thread where the depth was at least 36:

Yes - you will need your own database for this kind of analysis. This probably isn't what most chess players have in mind regarding "analysis of chess games", though.

[Dann Corbit]

I can ask anything. What players have played tis position?
What moves were tried?
How did each of those moves fare statistically?
What did computers think?
Are there any successful novelties?

[BrendanJNorman]

As someone stuck on an M1 Macbook, I have recently started using chessify.me.

A 9 million game, weekly updated database/opening tree, very fast cloud engines, 6 man tablebases without taking up ANY of my CPU (or HDD for that matter) is a pretty good deal imo.

Look forward to the future of cloud computing with chess, especially once AI is mainstream in chess apps as well.

[Dave Gomboc]

I use Arena a lot just because of familiarity and what I use it for (Epd analysis and game contests)

Hmm. www.playwitharena.de has a download for Arena 3.5.1 for Windows (released on 2015-12-20) and Arena 3.10beta for Linux (released 2020-01-19). However, I don't see any source code at that site. It seems like the author of the program, apparently named Martin Blume, has been inactive for quite some time. Is there any chance that the author might consider open-sourcing the code so that it can be updated further by others?