i working on creating stored procedure table in database; however, runs extremely slow due large number of joins must performed. trying think of way optimize query not need perform many left joins, having trouble finding way this. code looks following:
alter procedure [dbo].[sts] declare @t0 table(id nvarchar(7), date1 date, tiv float, [1mo] float, ran nvarchar(50), san nvarchar(50)) insert @t0(id, date1, tiv, [1mo], ran, san) select id, date1, tiv, tmr, ran, san dbo.history (date1 not null) , (valid null or valid <> 0) , (include <> 0) declare @t1 table(id nvarchar(7), date1 date, tiv float, [3mo] float, ran nvarchar(50), san nvarchar(50)) insert @t1(id, date1, tiv, [3mo], ran, san) select * dbo.series(3) declare @t2 table(id nvarchar(7), date1 date, tiv float, [6mo] float, ran nvarchar(50), san nvarchar(50)) insert @t2(id, date1, tiv, [6mo], ran, san) select * dbo.series(6) declare @t3 table(id nvarchar(7), date1 date, tiv float, [9mo] float, ran nvarchar(50), san nvarchar(50)) insert @t3(id, date1, tiv, [9mo], ran, san) select * dbo.series(9) select t0.*, join2.[3mo], join2.[6mo], join2.[9mo] @t0 t0 left outer join (select t1.*, join1.[6mo], join1.[9mo] @t1 t1 left outer join (select t2.*, t3.[9mo] @t2 t2 left outer join @t3 t3 on t2.id = t3.id , t2.date1 = t3.date1 , t2.ran = t3.ran , t2.san = t3.san) join1 on t1.id = join1.id , t1.date1 = join1.date1 , t1.ran = join1.ran , t1.san = join1.san) join2 on t0.id = join2.id , t0.date1 = join2.date1 , t0.ran = join2.ran , t0.san = join2.san is there simple way optimize slow query, or need think of new way altogether?
try adding option (recompile) @ end of final join
this article explains lot table variables , temporary tables: https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
Comments
Post a Comment