Messing With Local LLMs – Part 1 – A Simple Task

(A big sausage dog)

Like lots of people who work in IT or have a passing interest in technology, I like to play with LLMs for personal projects and so forth. However, up until now, I’ve been using the big guns like ChatGPT and Claude to achieve my goals. Over the Christmas break, I have decided to look at some local LLMs you can run locally on your own gear. I want to gauge the following:

• Can it be used to refactor basic SQL in a way I have told it to (this blog post)
• What is the overhead on the local rig running the local LLM, and how many tokens per second do I get on modest hardware? A more interesting SQL challenge will be used here. (next blog post)
• Test its coding abilities for a simple app (speculative future blog post, might not happen)

My current rig is modest in comparison to some setups I have seen out there:

• AMD Ryzen 9 9950x3d
• 96GB RAM
• NVIDIA 4070 Super (12GB)

The elephant in the room is the fact that my GPU is not very capable from an LLM point of view, only having 12GB.

Why would one want to use a local LLM? Here are some reasons:

• Hobbyist uses
• Data privacy
• Regulatory compliance
• etc
• etc

A Simple Task

I wanted to ask the various local LLMs (I will be testing multiple) to inline a scalar-valued function into the main body of a very slow stored procedure. This is a common performance tuning method to avoid ‘row by agonising row’ (RBAR for short). By inlining the function, we should get a more set-based execution path.

We will be using the StackOverflow2010 sample database (under a Creative Commons license), and here is the stored procedure and function it calls:

CREATE OR ALTER PROCEDURE dbo.sp_GetHighValueUsers
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP 50
        u.Id,
        u.DisplayName,
        u.Reputation
    FROM dbo.Users u
    WHERE dbo.fn_IsHighValueUser(u.Id) = 1
    ORDER BY u.Reputation DESC;
END;
GO

CREATE OR ALTER FUNCTION dbo.fn_IsHighValueUser
(
    @UserId INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @PostCount INT;
    DECLARE @Reputation INT;

    SELECT @Reputation = Reputation
    FROM dbo.Users
    WHERE Id = @UserId;

    SELECT @PostCount = COUNT(*)
    FROM dbo.Posts
    WHERE OwnerUserId = @UserId;

    IF @Reputation > 10000 AND @PostCount > 100
        RETURN 1;

    RETURN 0;
END;
GO

Our stored procedure identifies users with both a post count of 10000 and a reputation over 100. If you attempt to call this stored procedure without any useful indexes, we have a runtime that might as well be infinite (I cancelled it after 4 days running). To actually get the stored procedure to finish, I added an index on owneruserid in the interest of my sanity:

CREATE INDEX ix_owneruserid on dbo.Posts(owneruserid);

We get an execution time of 26 seconds. Whilst this is an improvement (order of magnitude), I wanted to rewrite the query for the sake of testing all our local LLMs.

The local LLMs tested

Here is the list of local LLMs I tested. Some are old, some are more recent, and some are newer versions of older ones:

(The above release dates are rough)

Ollama version 0.13.5 was used to run the tests via WSL2 on Windows 11. I have placed the LLMs in tiers to denote their size relative to my modest kit. eg some Tier 1 can fit in my GPU’s memory, where Tier 3 cannot, as they are much larger. These tiers are not real and are just for my benefit. In addition, everything was run as-is and no settings were tweaked or tuned.

How the LLMs were tested

Due to the non-deterministic nature of LLMs, I asked the same LLM (after clearing the context) the same question three times:

This TSQL is a stored procedure and a scalar function. Can you inline the function into the body of the stored procedure to speed up performance? Here is the code:
<CODE WENT HERE>

The full output was copied into a text file for later study. Once I got all the results from all the LLMs, I deployed the rewritten stored procedures to my test server. See the below disaster zone (not an exhaustive list either!):

Local LLM results

Measuring success was simple

• Does it compile?
• Does it run?
• Are the results correct?
• Is it faster than 26 seconds?

Things like execution plans and so forth were not analysed.

Here are the high-level results (‘runs fast’ means it returns data instantly):

Here is a breakdown of the success rate:

The output and verboseness of the responses varied wildly, but for the sake of testing, I just copied out the TSQL without really reading any of the responses properly. Most of them did hint at looking at indexes, too, which was correct.

The following models had a 100% sucess rate:

• qwen2.5:7b
• mixtral:7b
• qwen3:8b
• gemma2:9b
• qwen2.5:14b
• qwen2.5:32b
• mixtral:8x7b
• qwen3:32b

Here is a quick breakdown of the success rate:

In my testing, the Tier 2 models produced the poorest results, while the small and large models were more successful. It is worth noting that the large models were absolutely painfully slow (more on that in the next blog post).

Here is a breakdown of the styles of query for the successful LLMs for this task.

Here are the ‘styles’ of queries the LLMs came up with:

Direct Correlated Subquery

SELECT TOP 50 u.Id, u.DisplayName, u.Reputation
FROM dbo.Users u
WHERE u.Reputation > 10000
  AND (SELECT COUNT(*) FROM dbo.Posts WHERE OwnerUserId = u.Id) > 100
ORDER BY u.Reputation DESC;

Used by: qwen2.5:7b, mixtral:7b, gemma2:9b, llama3.1:8b

CTE with join

WITH PostCounts AS (
    SELECT OwnerUserId, COUNT(*) AS PostCount
    FROM dbo.Posts
    GROUP BY OwnerUserId
)
SELECT TOP 50 u.Id, u.DisplayName, u.Reputation
FROM dbo.Users u
INNER JOIN PostCounts pc ON u.Id = pc.OwnerUserId
WHERE u.Reputation > 10000 AND pc.PostCount > 100
ORDER BY u.Reputation DESC;

Used by: qwen3:8b, qwen2.5:32b, qwen3:32b, mixtral:8x7b, deepseek-r1:14b

Derived Table with join

SELECT TOP 50 u.Id, u.DisplayName, u.Reputation
FROM dbo.Users u
INNER JOIN (
    SELECT OwnerUserId, COUNT(*) AS PostCount
    FROM dbo.Posts
    GROUP BY OwnerUserId
) p ON u.Id = p.OwnerUserId
WHERE u.Reputation > 10000 AND p.PostCount > 100
ORDER BY u.Reputation DESC;

Used by: qwen2.5:14b (some attempts), gemma2:9b (alternative)

CROSS APPLY

SELECT TOP 50 u.Id, u.DisplayName, u.Reputation
FROM dbo.Users u
CROSS APPLY (
    SELECT COUNT(*) AS PostCount
    FROM dbo.Posts p
    WHERE p.OwnerUserId = u.Id
) postCounts
WHERE u.Reputation > 10000 AND postCounts.PostCount > 100
ORDER BY u.Reputation DESC;

Used by: qwen2.5-coder:14b, phi4:14b

EXISTS with HAVING

SELECT TOP 50 u.Id, u.DisplayName, u.Reputation
FROM dbo.Users u
WHERE EXISTS (
    SELECT 1 FROM dbo.Posts p
    WHERE p.OwnerUserId = u.Id
    GROUP BY p.OwnerUserId
    HAVING COUNT(*) > 100
)
AND u.Reputation > 10000
ORDER BY u.Reputation DESC;

Used by: qwen2.5:14b (one attempt)

Conclusion

Running LLMs locally was a fun learning experience. It showed that yes, we can do a basic TSQL task that we have set.

However, this single, simple task is unlikely to give us a definitive answer as to what LLM is best, as it would take weeks/months of constant use to find one I actually want to use as a daily driver. It could be that there isn’t a ‘best’ one that suits me for everything.

The next blog post will involve a much bigger task, and we will see what sort of ‘knowledge’ these LLMs have of a popular pop culture reference at the same time. This next post will also investigate how these LLMs perform and what the overhead is on my rig.

Resources

https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent