(Thirsty work)
In part 1 of this series, I wanted to do a sanity check on running local LLMs by asking them to carry out a simple task. The task was to inline a function into the body of a stored procedure. This was met with varying levels of success. In this post, I want to look at the performance of the LLMs and the impact on my rig, which is as follows:
- AMD Ryzen 9 9950x3d
- 96GB of RAM
- NVIDIA 4070 Super
In this post, we will again be using SQL Server as our target RDBMS for our sample data. Whilst the actual sample data itself is not massively important, I have made it more interesting by basing it around Star Trek. We will briefly examine the output produced by the LLMs.
Due to the insanely fast-paced nature of AI and the local models, what’s good, etc, this post will likely age badly.
The Local LLMs Tested
These are the same list from Part 1.
How the LLMs were tested
Three simple questions were asked one after the other:
Question 1
I want to generate some sample data in SQL Server with a catch. The catch is I want the tables and data to be
themed around Star Trek. Not only do we need tables but we also need queries or stored procedures we can use to
populate millions of rows.
Question 2
Great, can you add in a table with ships and battles? You can make the names of the ships up if need be.
Question 3
Finally, please can you generate some interesting reporting queries for our Star Trek data?
I then used a combination of task manager (watching for VmmemWSL) and watch nvidia-smi to eyeball the impact on system resources, along with tracking roughly how long the LLMs were taking to return results, along with logging tokens per sec. Nothing done here was particularly scientific.
Overhead and performance
Here are the results:
As we can see from the above image, my rig is hopeless when running the bigger models with tokens per sec in the single digits. These were essentially unusable if one is used to the frontier LLMs like ChatGPT, Claude, Grok and so forth. There isn’t much more to add other than that you need some serious firepower to run the models with more parameters.
For the models that could utilise my GPU properly, I got highish temps with the fans spooling up. For the bigger models that could not squeeze into VRAM, GPU usage was minimal as the workload was offloaded to my CPU and RAM, with the downside that they were painfully slow.
LLM output
The LLMs all gave me the following objects (I will paste the output of a working example in the APPENDIX):
Question 1 – Initial objects
- Tables
- Stored Procedures (to generate data)
Question 2 – Battles
- Tables
- Stored Procedures (to generate data)
Question 3 – Queries
- Sample queries
Here are the results:
I was brutal in my assessments. The slightest syntax error would mean part of the deployment failed. Most LLMs managed to generate TSQL create table syntax correctly, but the smaller models usually made a mess of the actual stored procedures. Some LLMs also started blending in Postgres syntax into the queries task set in question 3. For the queries, they just have to execute without error, not pull back any actual data. This is because some models could not write a usable data population stored procedure.
Frustratingly, only the bigger models tended to get things correct enough to consider a deployment successful for my tasks. I appreciate that in reality, you would keep asking for corrections, but for the interest of this experiment, I only gave each LLM one attempt.
Lore Factor 7
Whilst the LLMs struggled with producing usable TSQL output, most fared better with their Star Trek lore.
Here is a quick breakdown of what they produced:
Iconic Phrases
- “Live Long and Prosper!” – Mentioned by llama3.1:70b and gemma2:9b
Famous Battles and Scenarios
- The Kobayashi Maru – Found in qwen2.5:14b
- Battle of Wolf 359 – Found in qwen2.5:32b and mixtral:8x7b
- First Battle of Chin’toka – Found in mixtral:8x7b
- Battle at Axanar – Found in qwen2.5:32b
Iconic Starships
- USS Enterprise (NCC-1701) – Found in nearly all models
- USS Defiant (NX-74205) – Found in qwen2.5:32b and llama3.1:70b
- Klingon Bird-of-Prey – Found in mixtral:8x7b and qwen2.5:14b
Ship Classes
- Constitution-class – Found in multiple models
- Galaxy-class – Found in deepseek-r1:14b and gemma2:9b
- Nebula-class – Found in deepseek-r1:14b
- Intrepid-class – Found in qwen2.5:14b
Hero Crew Members
- James T. Kirk – Found in qwen2.5:32b, qwen2.5:14b, mixtral:8x7b and deepseek-r1:14b
- Spock – Found in qwen2.5:32b, qwen2.5:14b and deepseek-r1:14b
- Leonard McCoy – Found in qwen2.5:32b and deepseek-r1:14b
- Montgomery Scott – Found in qwen2.5:14b
- Hikaru Sulu – Found in mixtral:8x7b
- Pavel Chekov – Found in mixtral:8x7b
- Uhura – Found in deepseek-r1:14b
- Jean-Luc Picard – Found in llama3.1:70b and mixtral:8x7b
- Worf – Found in mixtral:8x7b
Species & Civilizations
- Federation – Found in all models
- Vulcan – Found in qwen2.5:32b, qwen2.5:14b and mixtral:8x7b
- Klingon Empire – Found in mixtral:8x7b and gemma2:9b
- Romulan Star Empire – Found in gemma2:9b and llama3.1:70b
- Borg Collective – Found in mixtral:8x7b
- Cardassian Union – Found in mixtral:8x7b
- Dominion – Found in mixtral:8x7b
Key Worlds
- Vulcan – Found in multiple models
- Earth – Found in multiple models
- Qo’noS – Found in mixtral:8x7b
- Risa – Found in qwen2.5:32b
- Betazed – Found in gemma2:9b
Space Regions
- Alpha Quadrant – Found in deepseek-r1:14b and gemma2:9b
- Beta Quadrant – Found in gemma2:9b
- Delta Quadrant – Found in qwen2.5:14b and deepseek-r1:14b
Conclusion
The performance of the smaller models was acceptable, but the actual content they delivered in my testing was noticeably worse.
A large professional-grade GPU is required for serious local LLM work, or at least multiple gaming GPUs, as a mid-tier GPU like mine will be slow otherwise. In today’s market, prices are only going up and up, with NVIDIA rumoured to be raising prices further in 2026.
Some of the latest Mac’s with unified memory are apparently also decent for LLM usage, but I have no experience with these.
With regards to Star Trek knowledge, they all had a pretty good attempt at producing lore-based sample data, even though the actual TSQL to deploy the data was mostly undeployable in a lot of cases.
Resources
APPENDIX
Good example of working TSQL output (Qwen 2.5 72B).
--Question 1
Creating a Star Trek-themed database with tables, sample data, and scripts to populate it
with millions of rows is an exciting project! Below, I'll guide you through setting up the
schema, creating the necessary tables, and writing scripts to generate large amounts of data.
### Step 1: Create the Database
First, let's create a new database called `StarTrekDB`.
```sql
CREATE DATABASE StarTrekDB;
GO
USE StarTrekDB;
GO
```
### Step 2: Define the Tables
We'll create several tables to represent different aspects of the Star Trek universe. For
example, we can have tables for starships, crew members, missions, and planets.
#### 1. **Starships Table**
```sql
CREATE TABLE Starships (
ShipID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(50) NOT NULL,
Class NVARCHAR(50) NOT NULL,
Registry NVARCHAR(20) UNIQUE NOT NULL,
CrewCapacity INT NOT NULL
);
```
#### 2. **Crew Members Table**
```sql
CREATE TABLE CrewMembers (
CrewID INT PRIMARY KEY IDENTITY(1,1),
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Species NVARCHAR(50) NOT NULL,
Rank NVARCHAR(50) NOT NULL,
Department NVARCHAR(50) NOT NULL
);
```
#### 3. **Missions Table**
```sql
CREATE TABLE Missions (
MissionID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(100) NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
Objective NVARCHAR(255) NOT NULL,
ShipID INT FOREIGN KEY REFERENCES Starships(ShipID)
);
```
#### 4. **Planets Table**
```sql
CREATE TABLE Planets (
PlanetID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(50) NOT NULL,
System NVARCHAR(50) NOT NULL,
Classification NVARCHAR(50) NOT NULL
);
```
### Step 3: Create Stored Procedures to Populate the Tables
We'll create stored procedures to generate a large amount of data for each table. These
procedures will use random values and loops to insert millions of rows.
#### 1. **Populate Starships Table**
```sql
CREATE PROCEDURE PopulateStarships
@RowCount INT
AS
BEGIN
DECLARE @i INT = 1;
WHILE @i <= @RowCount
BEGIN
INSERT INTO Starships (Name, Class, Registry, CrewCapacity)
VALUES (
CONCAT('USS ', CHAR(65 + FLOOR(RAND() * 26)), 'Enterprise-', CAST(@i AS
NVARCHAR)),
CASE FLOOR(RAND() * 5)
WHEN 0 THEN 'Constitution'
WHEN 1 THEN 'Galaxy'
WHEN 2 THEN 'Intrepid'
WHEN 3 THEN 'Defiant'
ELSE 'Prometheus'
END,
CONCAT('NCC-', CAST(1000 + @i AS NVARCHAR)),
FLOOR(RAND() * 1000) + 500
);
SET @i = @i + 1;
END
END
GO
-- Execute the procedure to insert 1 million rows
EXEC PopulateStarships @RowCount = 1000000;
```
#### 2. **Populate Crew Members Table**
```sql
CREATE PROCEDURE PopulateCrewMembers
@RowCount INT
AS
BEGIN
DECLARE @i INT = 1;
WHILE @i <= @RowCount
BEGIN
INSERT INTO CrewMembers (FirstName, LastName, Species, Rank, Department)
VALUES (
CASE FLOOR(RAND() * 5)
WHEN 0 THEN 'James'
WHEN 1 THEN 'Spock'
WHEN 2 THEN 'Leonard'
WHEN 3 THEN 'Montgomery'
ELSE 'Nyota'
END,
CASE FLOOR(RAND() * 5)
WHEN 0 THEN 'Kirk'
WHEN 1 THEN 'McCoy'
WHEN 2 THEN 'Scott'
WHEN 3 THEN 'Uhura'
ELSE 'Sulu'
END,
CASE FLOOR(RAND() * 4)
WHEN 0 THEN 'Human'
WHEN 1 THEN 'Vulcan'
WHEN 2 THEN 'Klingon'
ELSE 'Borg'
END,
CASE FLOOR(RAND() * 5)
WHEN 0 THEN 'Captain'
WHEN 1 THEN 'Commander'
WHEN 2 THEN 'Lieutenant'
WHEN 3 THEN 'Ensign'
ELSE 'Cadet'
END,
CASE FLOOR(RAND() * 5)
WHEN 0 THEN 'Command'
WHEN 1 THEN 'Science'
WHEN 2 THEN 'Operations'
WHEN 3 THEN 'Medical'
ELSE 'Security'
END
);
SET @i = @i + 1;
END
END
GO
-- Execute the procedure to insert 1 million rows
EXEC PopulateCrewMembers @RowCount = 1000000;
```
#### 3. **Populate Missions Table**
```sql
CREATE PROCEDURE PopulateMissions
@RowCount INT
AS
BEGIN
DECLARE @i INT = 1;
WHILE @i <= @RowCount
BEGIN
INSERT INTO Missions (Name, StartDate, EndDate, Objective, ShipID)
VALUES (
CONCAT('Mission ', CAST(@i AS NVARCHAR)),
DATEADD(DAY, FLOOR(RAND() * 365), '2023-01-01'),
DATEADD(DAY, FLOOR(RAND() * 365) + 7, '2023-01-01'),
CASE FLOOR(RAND() * 4)
WHEN 0 THEN 'Explore new worlds'
WHEN 1 THEN 'Establish diplomatic relations'
WHEN 2 THEN 'Defend against threats'
ELSE 'Conduct scientific research'
END,
FLOOR(RAND() * (SELECT MAX(ShipID) FROM Starships)) + 1
);
SET @i = @i + 1;
END
END
GO
-- Execute the procedure to insert 1 million rows
EXEC PopulateMissions @RowCount = 1000000;
```
#### 4. **Populate Planets Table**
```sql
CREATE PROCEDURE PopulatePlanets
@RowCount INT
AS
BEGIN
DECLARE @i INT = 1;
WHILE @i <= @RowCount
BEGIN
INSERT INTO Planets (Name, System, Classification)
VALUES (
CONCAT('Planet ', CAST(@i AS NVARCHAR)),
CASE FLOOR(RAND() * 5)
WHEN 0 THEN 'Alpha Centauri'
WHEN 1 THEN 'Vulcan System'
WHEN 2 THEN 'Klingon Empire'
WHEN 3 THEN 'Ferengi Alliance'
ELSE 'Romulan Star Empire'
END,
CASE FLOOR(RAND() * 4)
WHEN 0 THEN 'Class M'
WHEN 1 THEN 'Class K'
WHEN 2 THEN 'Class L'
WHEN 3 THEN 'Class N'
ELSE 'Class P'
END
);
SET @i = @i + 1;
END
END
GO
-- Execute the procedure to insert 1 million rows
EXEC PopulatePlanets @RowCount = 1000000;
```
### Step 4: Verify the Data
After running the stored procedures, you can verify that the data has been inserted correctly
by querying the tables.
```sql
SELECT COUNT(*) FROM Starships; -- Should return 1 million
SELECT COUNT(*) FROM CrewMembers; -- Should return 1 million
SELECT COUNT(*) FROM Missions; -- Should return 1 million
SELECT COUNT(*) FROM Planets; -- Should return 1 million
```
### Step 5: Additional Queries and Stored Procedures
You can create additional queries and stored procedures to perform various operations on the
data, such as finding missions by objective, listing crew members by rank, or generating reports.
For example:
```sql
-- Find missions with a specific objective
CREATE PROCEDURE GetMissionsByObjective
@Objective NVARCHAR(255)
AS
BEGIN
SELECT * FROM Missions WHERE Objective = @Objective;
END
GO
-- List crew members by rank
CREATE PROCEDURE GetCrewMembersByRank
@Rank NVARCHAR(50)
AS
BEGIN
SELECT * FROM CrewMembers WHERE Rank = @Rank;
END
GO
```
### Conclusion
This setup should give you a solid foundation for a Star Trek-themed database with tables and
scripts to generate millions of rows. You can further customize the data generation logic to fit
more specific themes or requirements as needed. Enjoy your exploration of the Star Trek universe
in SQL!
--Question 2
Certainly! Let's add a new table to represent battles and associate them with starships. We'll
also create stored procedures to populate this table with sample data.
### Step 1: Create the Battles Table
First, we'll define the `Battles` table, which will store information about various space
battles. Each battle will be associated with one or more starships.
```sql
CREATE TABLE Battles (
BattleID INT PRIMARY KEY IDENTITY(1,1),
Name NVARCHAR(50) NOT NULL,
Date DATE NOT NULL,
Location NVARCHAR(50) NOT NULL,
Outcome NVARCHAR(50) NOT NULL
);
```
### Step 2: Create a Junction Table for Starships and Battles
To represent the many-to-many relationship between starships and battles, we'll create a junction
table called `BattleStarships`.
```sql
CREATE TABLE BattleStarships (
BattleID INT,
ShipID INT,
PRIMARY KEY (BattleID, ShipID),
FOREIGN KEY (BattleID) REFERENCES Battles(BattleID),
FOREIGN KEY (ShipID) REFERENCES Starships(ShipID)
);
```
### Step 3: Create Stored Procedures to Populate the Tables
We'll create stored procedures to generate a large amount of data for the `Battles` and
`BattleStarships` tables.
#### 1. **Populate Battles Table**
```sql
CREATE PROCEDURE PopulateBattles
@RowCount INT
AS
BEGIN
DECLARE @i INT = 1;
WHILE @i <= @RowCount
BEGIN
INSERT INTO Battles (Name, Date, Location, Outcome)
VALUES (
CONCAT('Battle of ', CHAR(65 + FLOOR(RAND() * 26)), 'Star', CAST(@i AS NVARCHAR)),
DATEADD(DAY, FLOOR(RAND() * 365), '2023-01-01'),
CASE FLOOR(RAND() * 4)
WHEN 0 THEN 'Klingon Space'
WHEN 1 THEN 'Federation Territory'
WHEN 2 THEN 'Romulan Neutral Zone'
ELSE 'Deep Space'
END,
CASE FLOOR(RAND() * 3)
WHEN 0 THEN 'Federation Victory'
WHEN 1 THEN 'Enemy Victory'
ELSE 'Stalemate'
END
);
SET @i = @i + 1;
END
END
GO
-- Execute the procedure to insert 1 million rows
EXEC PopulateBattles @RowCount = 1000000;
```
#### 2. **Populate BattleStarships Table**
To populate the `BattleStarships` table, we'll randomly assign starships to battles. We'll assume
that each battle can involve multiple starships.
```sql
CREATE PROCEDURE PopulateBattleStarships
@RowCount INT
AS
BEGIN
DECLARE @i INT = 1;
WHILE @i <= @RowCount
BEGIN
-- Randomly select a battle and a ship
DECLARE @BattleID INT = FLOOR(RAND() * (SELECT MAX(BattleID) FROM Battles)) + 1;
DECLARE @ShipID INT = FLOOR(RAND() * (SELECT MAX(ShipID) FROM Starships)) + 1;
-- Insert the relationship into BattleStarships
INSERT INTO BattleStarships (BattleID, ShipID)
VALUES (@BattleID, @ShipID);
SET @i = @i + 1;
END
END
GO
-- Execute the procedure to insert 1 million rows
EXEC PopulateBattleStarships @RowCount = 1000000;
```
### Step 4: Verify the Data
After running the stored procedures, you can verify that the data has been inserted correctly by
querying the tables.
```sql
SELECT COUNT(*) FROM Battles; -- Should return 1 million
SELECT COUNT(*) FROM BattleStarships; -- Should return 1 million
```
### Step 5: Additional Queries and Stored Procedures
You can create additional queries and stored procedures to perform various operations on the
data, such as finding battles involving a specific starship, listing all starships involved in a
battle, or generating reports.
For example:
```sql
-- Find battles involving a specific ship
CREATE PROCEDURE GetBattlesByShipID
@ShipID INT
AS
BEGIN
SELECT B.*
FROM Battles B
INNER JOIN BattleStarships BS ON B.BattleID = BS.BattleID
WHERE BS.ShipID = @ShipID;
END
GO
-- List all starships involved in a specific battle
CREATE PROCEDURE GetStarshipsByBattleID
@BattleID INT
AS
BEGIN
SELECT S.*
FROM Starships S
INNER JOIN BattleStarships BS ON S.ShipID = BS.ShipID
WHERE BS.BattleID = @BattleID;
END
GO
-- Example usage of the procedures
EXEC GetBattlesByShipID 1; -- Replace 1 with the ShipID you want to query
EXEC GetStarshipsByBattleID 1; -- Replace 1 with the BattleID you want to query
```
### Conclusion
With these additions, your Star Trek-themed database now includes a `Battles` table and a
junction table `BattleStarships` to represent the many-to-many relationship between battles and
starships. The stored procedures will help you generate millions of rows of sample data. You can
further customize the data generation logic to fit more specific themes or requirements as
needed. Enjoy your exploration of the Star Trek universe in SQL!
--Question 3
Certainly! Here are several interesting reporting queries that you can use with your Star
Trek-themed database. These queries will help you analyze and visualize various aspects of the
data, such as mission outcomes, crew member statistics, and battle details.
### 1. **List All Missions by Ship**
This query lists all missions along with the starship they were conducted on.
```sql
SELECT
M.MissionID,
M.Name AS MissionName,
M.StartDate,
M.EndDate,
M.Objective,
S.Name AS ShipName,
S.Class,
S.Registry
FROM
Missions M
JOIN
Starships S ON M.ShipID = S.ShipID
ORDER BY
M.StartDate;
```
### 2. **Count of Crew Members by Rank**
This query counts the number of crew members for each rank.
```sql
SELECT
Rank,
COUNT(*) AS NumberOfCrewMembers
FROM
CrewMembers
GROUP BY
Rank
ORDER BY
NumberOfCrewMembers DESC;
```
### 3. **List of Starships and Their Crew Capacity**
This query lists all starships along with their crew capacity.
```sql
SELECT
Name,
Class,
Registry,
CrewCapacity
FROM
Starships
ORDER BY
CrewCapacity DESC;
```
### 4. **Battles Won by the Federation**
This query lists all battles won by the Federation, including the date and location of each
battle.
```sql
SELECT
B.Name AS BattleName,
B.Date,
B.Location
FROM
Battles B
WHERE
B.Outcome = 'Federation Victory'
ORDER BY
B.Date;
```
### 5. **Starships Involved in Multiple Battles**
This query lists starships that have been involved in more than one battle.
```sql
SELECT
S.Name AS ShipName,
COUNT(BS.BattleID) AS NumberOfBattles
FROM
Starships S
JOIN
BattleStarships BS ON S.ShipID = BS.ShipID
GROUP BY
S.Name
HAVING
COUNT(BS.BattleID) > 1
ORDER BY
NumberOfBattles DESC;
```
### 6. **Crew Members by Department and Species**
This query lists the number of crew members by department and species.
```sql
SELECT
Department,
Species,
COUNT(*) AS NumberOfCrewMembers
FROM
CrewMembers
GROUP BY
Department, Species
ORDER BY
Department, Species;
```
### 7. **Battles by Year**
This query counts the number of battles that occurred each year.
```sql
SELECT
YEAR(Date) AS BattleYear,
COUNT(*) AS NumberOfBattles
FROM
Battles
GROUP BY
YEAR(Date)
ORDER BY
BattleYear;
```
### 8. **Missions with Longest Duration**
This query lists the missions with the longest duration, along with their start and end dates.
```sql
SELECT
M.Name AS MissionName,
M.StartDate,
M.EndDate,
DATEDIFF(DAY, M.StartDate, M.EndDate) AS DurationDays
FROM
Missions M
ORDER BY
DurationDays DESC;
```
### 9. **Crew Members on Specific Starship**
This query lists all crew members who are assigned to a specific starship.
```sql
CREATE PROCEDURE GetCrewMembersByShipID
@ShipID INT
AS
BEGIN
SELECT
C.FirstName,
C.LastName,
C.Species,
C.Rank,
C.Department
FROM
CrewMembers C
WHERE
C.ShipID = @ShipID
ORDER BY
C.Rank, C.LastName;
END
GO
-- Example usage of the procedure
EXEC GetCrewMembersByShipID 1; -- Replace 1 with the ShipID you want to query
```
### 10. **Battles by Outcome**
This query counts the number of battles for each outcome (Federation Victory, Enemy Victory,
Stalemate).
```sql
SELECT
Outcome,
COUNT(*) AS NumberOfBattles
FROM
Battles
GROUP BY
Outcome
ORDER BY
NumberOfBattles DESC;
```
### 11. **Crew Members Involved in Multiple Missions**
This query lists crew members who have been involved in more than one mission.
```sql
SELECT
C.FirstName,
C.LastName,
COUNT(M.CrewID) AS NumberOfMissions
FROM
CrewMembers C
JOIN
MissionCrew MC ON C.CrewID = MC.CrewID
GROUP BY
C.FirstName, C.LastName
HAVING
COUNT(M.CrewID) > 1
ORDER BY
NumberOfMissions DESC;
```
### Note:
- The `MissionCrew` table is assumed to be a junction table that links crew members to
missions. If you haven't created this table yet, you can add it using the following SQL:
```sql
CREATE TABLE MissionCrew (
MissionID INT,
CrewID INT,
PRIMARY KEY (MissionID, CrewID),
FOREIGN KEY (MissionID) REFERENCES Missions(MissionID),
FOREIGN KEY (CrewID) REFERENCES CrewMembers(CrewID)
);
```
These queries should provide a comprehensive set of reports for your Star Trek-themed
database. You can further customize and expand these queries based on your specific needs and
data structure.