mathematical formula

Article

As SQL developers, we tend to think of performance tuning in terms of crafting the best table indices, avoiding scalar and table valued functions, and analyzing query plans (among other things). But sometimes going back to the spec and applying some properties of elementary math can be the best way to begin to improve performance of SQL queries which implement mathematical formulas. This article is a case study of how I used this technique to optimize my SQL implementation of the Inverse Simpson Index.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

(3)

You rated this post out of 5. Change rating

2021-05-07 (first published: 2019-09-12)

5,389 reads

Discuss

Read the latest Blogs

Visit the forum

Question of the Day

Which Table I

I have this code in SQL Server 2022:

CREATE SCHEMA etl;
GO
CREATE TABLE etl.product
(
    ProductID INT,
    ProductName VARCHAR(100)
);
GO
INSERT etl.product
VALUES
(2, 'Bee AI Wearable');
GO
CREATE TABLE dbo.product
(
    ProductID INT,
    ProductName VARCHAR(100)
);
GO
INSERT dbo.product
VALUES
(1, 'Spiral College-ruled Notebook');
GO
CREATE OR ALTER PROCEDURE etl.GettheProduct
AS
BEGIN
    SELECT  ProductName
    FROM product;
END;
GO

When I execute this code as a user whose default schema is dbo and has rights to the tables and proc, what is returned?

See possible answers

mathematical formula

Optimize Your SQL by Reformulating the Spec

Blogs

An LLM Saved My ATtiny85 From Certain Death

I recommend Smart Brevity (book) for communications

SQL Server 2025 Developer Edition – One size fits all

Forums

Which Table I

Using Python notebooks to save money in Fabric: The Fabric Modern Data Platform

Your AI Successes

Question of the Day

Which Table I