March 31, 2015 at 4:50 am
We have been trying to improve the performance of a particular SELECT query in our client's database, but it is proving to be a little tricky. Previously, the SELECT query (called from a stored procedure) was constructed dynamically, with filtering criteria appended to the WHERE clause before execution depending on the input parameter values provided. We have been trying to steer this procedure away from dynamic SQL in favour of 3-4 separate SELECT queries.
Any suggestions on the below would be greatly appreciated. The original client table/column names have been substituted for generic names, and the data is randomly generated to approximate the scenario. The query to optimise is contained lower down, where postcode values can be inserted into the @postcode variable (this could also be a blank string, which executes very quickly). As you will see, a simple postcode search results in a wait of several seconds.
USE tempdb
GO
-- Create tables.
CREATE TABLE table8 (
t8id int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED
)
GO
CREATE TABLE table7 (
t7id int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED
)
GO
CREATE TABLE table6 (
t7id int NOT NULL,
t8id int NOT NULL,
CONSTRAINT pk_t6 PRIMARY KEY CLUSTERED (t7id, t8id),
CONSTRAINT fk_t6_t7 FOREIGN KEY (t7id) REFERENCES table7 (t7id),
CONSTRAINT fk_t6_t8 FOREIGN KEY (t8id) REFERENCES table8 (t8id)
)
GO
CREATE TABLE table5 (
t8id int NOT NULL,
t5id int NOT NULL,
active bit NOT NULL,
CONSTRAINT pk_t5 PRIMARY KEY CLUSTERED (t5id, t8id),
CONSTRAINT fk_t5_t8 FOREIGN KEY (t8id) REFERENCES table8 (t8id)
)
GO
CREATE TABLE table3 (
t3id int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
postcode nchar(10) NULL
)
GO
CREATE TABLE table2 (
t2id int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
t3id int NULL,
CONSTRAINT fk_t2_t3 FOREIGN KEY (t3id) REFERENCES table3 (t3id)
)
GO
CREATE TABLE table1 (
t1id int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
t2id int NULL,
t7id int NULL,
CONSTRAINT fk_t1_t2 FOREIGN KEY (t2id) REFERENCES table2 (t2id),
CONSTRAINT fk_t1_t7 FOREIGN KEY (t7id) REFERENCES table7 (t7id)
)
GO
CREATE TABLE table4 (
t4id int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
t1id int NOT NULL,
t2id int NOT NULL,
t3id int NOT NULL,
CONSTRAINT fk_t4_t1 FOREIGN KEY (t1id) REFERENCES table1 (t1id),
CONSTRAINT fk_t4_t2 FOREIGN KEY (t2id) REFERENCES table2 (t2id),
CONSTRAINT fk_t4_t3 FOREIGN KEY (t3id) REFERENCES table3 (t3id)
)
GO
-- Insert data.
SET IDENTITY_INSERT table8 ON;
DECLARE @C int
SET @C = 1
WHILE @C < 35
BEGIN
INSERT table8 (t8id)
SELECT (@c)
SET @C +=1
END
SET IDENTITY_INSERT table8 OFF;
GO
SET IDENTITY_INSERT table7 ON;
DECLARE @C int
SET @C = 1
WHILE @C < 16
BEGIN
INSERT table7 (t7id)
SELECT (@c)
SET @C +=1
END
SET IDENTITY_INSERT table7 OFF;
GO
INSERT table6 (t7id, t8id)
SELECT t7id, t8id
FROM table7
CROSS JOIN table8
GO
INSERT table5 (t8id, t5id, active)
SELECT TOP 18000
t8.t8id,
ROW_NUMBER () OVER (ORDER BY t8.t8id),
CASE WHEN t6.t8id % 3 = 0 THEN 0 ELSE 1 END
FROM table8 t8
CROSS JOIN table6 t6
GO
INSERT table3 (postcode)
SELECT
CHAR(90 - ABS(CHECKSUM(NEWID())) % 26) + -- Alpha
CHAR(90 - ABS(CHECKSUM(NEWID())) % 26) + -- Alpha
CHAR(57 - ABS(CHECKSUM(NEWID())) % 10) + -- Numeric
CHAR(57 - ABS(CHECKSUM(NEWID())) % 10) + -- Numeric
' ' +
CHAR(57 - ABS(CHECKSUM(NEWID())) % 10) + -- Numeric
CHAR(90 - ABS(CHECKSUM(NEWID())) % 26) + -- Alpha
CHAR(90 - ABS(CHECKSUM(NEWID())) % 26) -- Alpha
FROM table5
CROSS JOIN table8
UNION
SELECT 'AB12 3CD'
GO
INSERT table2 (t3id)
SELECT t3id
FROM table3
ORDER BY postcode
GO
INSERT table1 (t2id, t7id)
SELECT --TOP 150000
ABS(CHECKSUM(NEWID())) % (SELECT COUNT(t2id) - 1 FROM table2) + 1,
ABS(CHECKSUM(NEWID())) % (SELECT COUNT(t7id) - 1 FROM table7) + 1
FROM table3
GO
INSERT table4 (t1id, t2id, t3id)
SELECT
t1id,
t2id,
ABS(CHECKSUM(NEWID())) % (SELECT COUNT(t3id) - 1 FROM table3) + 1
FROM table1
GO
-- Query to optimise:
DECLARE@postcode nchar(10), @t5id int = 1
SELECT @postcode = 'AB12 3CD'
SELECT TOP 1000
t1.t1id
FROM dbo.table2 t2
INNER JOIN dbo.table1 t1 ON t2.t2id = t1.t2id
LEFT OUTER JOIN dbo.table3 t3 ON t2.t3id = t3.t3id
WHERE EXISTS
(
SELECT t6.t7id
FROM dbo.table5 t5
INNER JOIN dbo.table6 t6 ON t5.t8id = t6.t8id AND t6.t7id = t1.t7id
WHERE t5.t5id = @t5id
AND t5.active = 1
)
AND t1.t1id IN
(
SELECT t1.t1id WHERE @postcode = ''
UNION
SELECT t4.t1id
FROM dbo.table4 t4
INNER JOIN dbo.table3 t3b ON t3b.t3id = t4.t3id
WHERE t3b.postcode =
CASE @postcode
WHEN ''
THEN ISNULL(t3b.postcode, '')
ELSE @postcode
END
)
/*
-- Drop foreign key constraints and tables.
ALTER TABLE table6 DROP CONSTRAINT fk_t6_t8
GO
ALTER TABLE table6 DROP CONSTRAINT fk_t6_t7
GO
ALTER TABLE table5 DROP CONSTRAINT fk_t5_t8
GO
ALTER TABLE table2 DROP CONSTRAINT fk_t2_t3
GO
ALTER TABLE table1 DROP CONSTRAINT fk_t1_t7
GO
ALTER TABLE table1 DROP CONSTRAINT fk_t1_t2
GO
ALTER TABLE table4 DROP CONSTRAINT fk_t4_t1
GO
ALTER TABLE table4 DROP CONSTRAINT fk_t4_t2
GO
ALTER TABLE table4 DROP CONSTRAINT fk_t4_t3
GO
DROP TABLE table5
GO
DROP TABLE table8
GO
DROP TABLE table7
GO
DROP TABLE table6
GO
DROP TABLE table4
GO
DROP TABLE table1
GO
DROP TABLE table2
GO
DROP TABLE table3
GO
*/
March 31, 2015 at 5:40 am
You can explore the reasons why this query is performing poorly using a simplified model of your query - one of the EXIST subqueries.
Here's yours. The filter for postcode isn't SARGable: if a supporting index is available, it can't and won't be used because CASE or a function around the searched column prevent it.
SELECT 1 WHERE @postcode = ''
UNION
SELECT t4.t1id
FROM dbo.table4 t4
INNER JOIN dbo.table3 t3b ON t3b.t3id = t4.t3id
WHERE t3b.postcode =
CASE @postcode
WHEN ''
THEN ISNULL(t3b.postcode, '')
ELSE @postcode
END
Here's an alternative. The filter for postcode is now SARGable. With a suitable supporting index in place, a seek for the postcode will replace an index or table scan you can see in the query above.
SELECT 1 WHERE @postcode = N''
UNION ALL
SELECT t4.t1id
FROM dbo.table4 t4
INNER JOIN dbo.table3 t3b
ON t3b.t3id = t4.t3id
WHERE (@postcode = N'' AND t3b.postcode IS NULL)
OR t3b.postcode = @postcode
Compare both of these queries in terms of performance and output.
Now add this index to support a seek on postcode and try both queries again.
CREATE NONCLUSTERED INDEX [ix_Postcode] ON [dbo].[table3]
([postcode] ASC) INCLUDE ([t3id])
Also, add this index to accelerate the join between t3 and t4:
CREATE NONCLUSTERED INDEX [ix_t3id] ON [dbo].[table4]
([t3id] ASC) INCLUDE ([t1id])
Finally, lose the expensive sort operator by changing UNION to UNION ALL. You don’t care how many rows you are getting back from this query, but more importantly, SQL Server will stop processing the result from it as soon as a single qualifying row is returned.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 31, 2015 at 7:06 am
And when you think you have a handle on the changes suggested, all good, take a look at the execution plan to understand how the optimizer is resolving the query. There may be further tuning opportunities exposed there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply