June 27, 2018 at 1:20 pm
I have bunch of views (some of them pretty complex with many inner and outer joins) that basically filter on TermCode:
Select Residents.FirstName, Rentals.Description
From Residents
Inner Join Rentals ON Rentals.Term = Residents.Term
Where Rentals.Term IN (11,22)
Residents table:
ResidentsID - FirstName - Term
1 - David - 11
2 - John - 11
3 -David - 12
4 - Bob -14
5 - John - 16
The Rentals table:
ID - Description
11 - 2017 Spring 2
12 - 2017 Summer 1
13 - 2017 Fall
14 - 2018 Spring
15 - 2018 Summer 1
16 - 2018 Summer 2
Every few weeks I have to ALTER views to update the new terms. I tried the following by creating a table to hold values just for the current terms:
Select *
From Residents r
Where r.TermCode IN (select termcode from dbo.tbl_termcode)
But this has a big performance hit. Is there an efficient way to automate this process where I don't have to go into each view and make the change?
June 27, 2018 at 1:33 pm
What about adding a column to the table to add a flag. Then update the flag whenever needed and use that column in your views instead of the Term column.
You could keep the table that defines the "active" terms and build a GUI to maintain it and give that responsibility to someone else.
June 27, 2018 at 1:39 pm
That's an idea, but the tables are vendor proprietary tables and can't be modified.
June 27, 2018 at 1:56 pm
To be honest, with out seeing what you are working with it is hard to provide a good answer.
June 27, 2018 at 2:04 pm
aftab97 - Wednesday, June 27, 2018 1:39 PMThat's an idea, but the tables are vendor proprietary tables and can't be modified.
Does that include changing the existing clustered index? Because if you (almost) always qualify by term, the best way to get good performance is to cluster the Residents table on Term first.
If you can't do that, then if this query is slow:
Select * From Residents r Where r.TermCode IN (select termcode from dbo.tbl_termcode)
it will always be slow, no matter what you do.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 27, 2018 at 2:07 pm
What about creating a huge script with all the views' definitions and use replace functionality of the text/code editor (SSMS?) to change the values?
This can also be done using T-SQL but you should be careful when altering the code like this. Here's an example:
--Creating sample views
CREATE VIEW vwTables
AS
SELECT *
FROM sys.tables
WHERE schema_id IN(1)
GO
CREATE VIEW vwProcedures
AS
SELECT *
FROM sys.procedures
WHERE schema_id IN(1)
GO
--Reviewing the results of the views
SELECT * FROM dbo.vwTables AS vt
SELECT * FROM dbo.vwProcedures AS vp
--Beginning of the solution
DECLARE @SQL NVARCHAR(MAX);
DECLARE views_cursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT REPLACE( REPLACE( REPLACE( definition, 'CREATE VIEW', 'ALTER VIEW'), '''', ''''''), 'schema_id IN(1)', 'schema_id IN(4)')
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) IN ('vwTables', 'vwProcedures');
OPEN views_cursor;
FETCH NEXT FROM views_cursor INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SQL;
EXEC sp_executesql @SQL;
FETCH NEXT FROM views_cursor INTO @SQL;
END
CLOSE views_cursor;
DEALLOCATE views_cursor;
--Testing that the views were actually changed
SELECT * FROM dbo.vwTables AS vt
SELECT * FROM dbo.vwProcedures AS vp
GO
--Cleaning my sandbox
DROP VIEW vwTables, vwProcedures
June 27, 2018 at 3:00 pm
Have you tried to change the SQL to do an INNER join on your table dbo.tbl_termcode? I would think that would be faster than in the WHERE clause.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 27, 2018 at 3:31 pm
I would hope that SQL itself would change it to an INNER JOIN when applicable, but it certainly won't hurt to try -- or verify using the query plan that SQL is already doing a join.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 27, 2018 at 3:36 pm
Luis Cazares - Wednesday, June 27, 2018 2:07 PMWhat about creating a huge script with all the views' definitions and use replace functionality of the text/code editor (SSMS?) to change the values?
This can also be done using T-SQL but you should be careful when altering the code like this. Here's an example:
--Creating sample views
CREATE VIEW vwTables
AS
SELECT *
FROM sys.tables
WHERE schema_id IN(1)GO
CREATE VIEW vwProcedures
AS
SELECT *
FROM sys.procedures
WHERE schema_id IN(1)
GO
--Reviewing the results of the views
SELECT * FROM dbo.vwTables AS vt
SELECT * FROM dbo.vwProcedures AS vp--Beginning of the solution
DECLARE @SQL NVARCHAR(MAX);DECLARE views_cursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT REPLACE( REPLACE( REPLACE( definition, 'CREATE VIEW', 'ALTER VIEW'), '''', ''''''), 'schema_id IN(1)', 'schema_id IN(4)')
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) IN ('vwTables', 'vwProcedures');OPEN views_cursor;
FETCH NEXT FROM views_cursor INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SQL;
EXEC sp_executesql @SQL;FETCH NEXT FROM views_cursor INTO @SQL;
ENDCLOSE views_cursor;
DEALLOCATE views_cursor;--Testing that the views were actually changed
SELECT * FROM dbo.vwTables AS vt
SELECT * FROM dbo.vwProcedures AS vp
GO
--Cleaning my sandbox
DROP VIEW vwTables, vwProcedures
Luis, I don't understand the code in its entirety and will go over it when I have a moment. Thanks for the help.
June 27, 2018 at 3:36 pm
ScottPletcher - Wednesday, June 27, 2018 3:31 PMI would hope that SQL itself would change it to an INNER JOIN when applicable, but it certainly won't hurt to try -- or verify using the query plan that SQL is already doing a join.
I did. The performance was equally bad.
June 27, 2018 at 3:51 pm
aftab97 - Wednesday, June 27, 2018 3:36 PMScottPletcher - Wednesday, June 27, 2018 3:31 PMI would hope that SQL itself would change it to an INNER JOIN when applicable, but it certainly won't hurt to try -- or verify using the query plan that SQL is already doing a join.I did. The performance was equally bad.
The only real solution is to best cluster the table. Anything else is just a laborious work-around.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 28, 2018 at 7:54 am
Going to say it again, it would help to see what the issue with the views are so that we can provide better answers. We can't see what you see.
June 28, 2018 at 8:25 am
aftab97 - Wednesday, June 27, 2018 3:36 PMScottPletcher - Wednesday, June 27, 2018 3:31 PMI would hope that SQL itself would change it to an INNER JOIN when applicable, but it certainly won't hurt to try -- or verify using the query plan that SQL is already doing a join.I did. The performance was equally bad.
As Lynn has said, give us more details. How fast did it run with the hard coded values? How long with the join? What indexes are on the Term column in each table? Attach execution plans for both.
I had a query once that a similar hard coded values, in my case the performance was bad, and I changed it to use a join to a table and it ran much faster. So without details I'm finding it hard to believe that the join is performing worse.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 28, 2018 at 11:57 am
aftab97 - Wednesday, June 27, 2018 3:36 PMScottPletcher - Wednesday, June 27, 2018 3:31 PMI would hope that SQL itself would change it to an INNER JOIN when applicable, but it certainly won't hurt to try -- or verify using the query plan that SQL is already doing a join.I did. The performance was equally bad.
You probably need to either change the clustered index on that table or provide a new non-clustered index that covers your queries. It's not likely a problem with the new table that holds Term values, but the other tables that have Term in them. Those tables will at least need a covering index that starts with Term and any other where clause stuff, epsecially date ranges, and then includes all other columns that get selected when Term is joined to the new table.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 29, 2018 at 9:45 am
sgmunson - Thursday, June 28, 2018 11:57 AMaftab97 - Wednesday, June 27, 2018 3:36 PMScottPletcher - Wednesday, June 27, 2018 3:31 PMI would hope that SQL itself would change it to an INNER JOIN when applicable, but it certainly won't hurt to try -- or verify using the query plan that SQL is already doing a join.I did. The performance was equally bad.
You probably need to either change the clustered index on that table or provide a new non-clustered index that covers your queries. It's not likely a problem with the new table that holds Term values, but the other tables that have Term in them. Those tables will at least need a covering index that starts with Term and any other where clause stuff, epsecially date ranges, and then includes all other columns that get selected when Term is joined to the new table.
INNER JOIN with clustered index resolved the issue.
This is a great support forum. Thank you to Luis Cazares, Lynn Pettis, ScottPletcher, below86, and sgmunson for being so quick to suggest a solution. You guys are all awesome.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply