question

LouisDavidson-9191 avatar image
0 Votes"
LouisDavidson-9191 asked ·

Error when executing query using SHORTEST_PATH

I have loaded the entire IMDB catalog onto a database in SQL Server 15.0.4102.2 Developer edition. It is a quad core i7, 32GB of RAM. This query/data I will describe is not maxing out memory (I don't have a max set, but in performance monitor, SQL Server is using 3.9 GB of ram, and I still have 16.9GB free. When I execute this query:

SELECT LAST_VALUE(Person2.Name) WITHIN GROUP (GRAPH PATH) AS ConnectedToAccountHandle
FROM Imdb.Person, Imdb.ContributedTo FOR PATH AS ContributedTo, Imdb.Title FOR PATH AS Title, Imdb.ContributedTo FOR PATH AS ContributedTo2, Imdb.Person FOR PATH AS Person2
WHERE MATCH(SHORTEST_PATH(Person(-(ContributedTo)->Title<-(ContributedTo2)-Person2)+)) AND Person.PrimaryName = 'Fred Astaire'

I get the following, VERY quickly: Msg 596, Level 21, State 1, Line 8 Cannot continue the execution because the session is in the kill state. Msg 0, Level 20, State 0, Line 8 A severe error occurred on the current command. The results, if any, should be discarded. The data is there, as this:

SELECT Person.PrimaryName, Person2.PrimaryName, Title.Name
ROM Imdb.Person, Imdb.ContributedTo AS ContributedTo, Imdb.Title AS Title, Imdb.ContributedTo AS ContributedTo2, Imdb.Person AS Person2
WHERE MATCH(Person-(ContributedTo)->Title<-(ContributedTo2)-Person2) AND Person.PrimaryName = 'Fred Astaire';

Returns 2681 connections. It is not really using a lot of memory or CPU to return this data, and the failure is immediate. The syntax is seemingly fine. I have done this on a smaller scale with the following query:

SELECT LAST_VALUE(Account2.AccountHandle) WITHIN GROUP (GRAPH PATH) AS ConnectedToAccountHandle FROM SocialGraph.Account AS Account1 ,SocialGraph.Account FOR PATH AS Account2 ,SocialGraph.Interest FOR PATH AS Interest ,SocialGraph.InterestedIn FOR PATH AS InterestedIn ,SocialGraph.InterestedIn FOR PATH AS InterestedIn2 --Account1 is interested in an interest, and Account2 is also
WHERE MATCH(SHORTEST_PATH(Account1(-(InterestedIn)->Interest<-(InterestedIn2)-Account2)+)) -- The interesting part
AND Account1.AccountHandle = '@Joe'

The structure to my imdb tables is:

CREATE TABLE [Imdb].[Title](
[TitleId] [varchar](10) NOT NULL,
[TitleType] [varchar](20) NOT NULL,
[Name] [nvarchar](500) NOT NULL,
[StartYear] [int] NULL,
CONSTRAINT [PKTitle] PRIMARY KEY CLUSTERED
(
[TitleId] 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]
)
AS NODE WITH (DATA_COMPRESSION = PAGE)
GO

CREATE TABLE [Imdb].[Person](
[PersonId] [varchar](10) NOT NULL,
[PrimaryName] [nvarchar](150) NOT NULL,
[Birthyear] [nvarchar](500) NULL,
CONSTRAINT [PKPerson] PRIMARY KEY CLUSTERED
(
[PersonId] 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]
)
AS NODE WITH (DATA_COMPRESSION = PAGE)
GO


CREATE TABLE [Imdb].[ContributedTo](
[ContributionType] [nvarchar](100) NULL,
CONSTRAINT EC_ContributedTo CONNECTION (imdb.Person TO Imdb.Title) ON DELETE NO ACTION,
CONSTRAINT [AKContributedTo] UNIQUE NONCLUSTERED
(
$from_id,
$to_id,
[ContributionType] 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]
)
AS EDGE WITH (DATA_COMPRESSION = PAGE)
GO

CREATE INDEX fromTo ON Imdb.ContributedTo ($from_id, $to_id)
CREATE INDEX ToFrom ON Imdb.ContributedTo ($to_id, $from_id)
GO


There is 10,756,900 people, and 43,312,922 contributed to rows, spread across 12 different types of contribution: director actor composer editor archive_footage actress writer self archive_sound cinematographer producer production_designer So it is a lot of data, and if it was churning and not finishing for a LONG time, I would probably understand. I added indexes on the from and to columns and fetching one level of connection is subsecond fast...

sql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

Hi @LouisDavidson-9191,

There are many reasons for this state.As far as the same problem I encountered before,this may be related to the kill command.You can find some information in the error log.

There is a similar question on the previous forum, which may be useful to you:
Cannot continue the execution because the session is in the kill state

I read the two links provided by lily, there are various possible reasons for this state, you can also refer to:
CHECKDB - Cannot continue the execution because the session is in the kill state.
Cannot continue the execution because the session is in the kill state. while building clustered index


If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LouisDavidson-9191 avatar image
0 Votes"
LouisDavidson-9191 answered ·

Turned out to be a bug. Adding the alias to the first table allows the statement to execute.


SELECT 1 --LAST_VALUE(Person2.PrimaryName) WITHIN GROUP (GRAPH PATH) AS ConnectedToPerson
FROM Imdb.Person AS Person,
Imdb.Person FOR PATH AS Person2,
Imdb.Title FOR PATH AS Title,
Imdb.ContributedTo FOR PATH AS ContributedTo,
Imdb.ContributedTo FOR PATH AS ContributedTo2
WHERE MATCH(SHORTEST_PATH(Person(-(ContributedTo)->Title<-(ContributedTo2)-Person2)+))
AND Person.PrimaryName = 'Frank Cardillo'

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am glad that your question has been resolved and you can accept your answer.

Echo

0 Votes 0 ·