|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
temporary viewI have many tables with name convention tYYYYMMDD and set of SPs to perform some calculations. Ideally I would need "temporary view' functionality. CREATE PROC MyProc (@tablename) AS CREATE TABLE #mytable (dt DATETIME) EXEC ('CREATE VIEW #myview AS SELECT * FROM ' + tablebame) EXEC Calc1 EXEC Calc2 SELECT * FROM #mytable DROP TABLE #mytable DROP VIEW #myview RETURN 0. CREATE PROC Calc1 AS INSERT #mytable SELECT max(col1) FROM #myview CREATE PROC Calc2 AS INSERT #mytable SELECT min(col1) FROM #myview CalcX - can be very complex and I got many of them. Unfortunately SQL doesn't support "temporary view'. Is there a workarround? Bye, Anatol A"temporary view" would be a temporary table. Use two temp tables to do
what you need. However, I don't see why you need to use this view at all. Why not just do this... Even better, post a full description of what you are trying to do, including DDL and sample data, and you may be able to eliminate the dynamic SQL and temp tables entirely. Most folks recommend avoiding both of these and using them only when necessary. CREATE PROC MyProc (@tablename) AS CREATE TABLE #mytable (dt DATETIME) EXEC ('INSERT #mytable SELECT max(col1) FROM ' + (@tablename) EXEC ('INSERT #mytable SELECT min(col1) FROM ' + (@tablename) SELECT * FROM #mytable DROP TABLE #mytable DROP VIEW #myview RETURN 0. Show quote "Anatoli Dontsov" <Anat***@dontsov.com> wrote in message news:%23JWq$C4eGHA.1436@TK2MSFTNGP05.phx.gbl... > Hi, All! > > I have many tables with name convention tYYYYMMDD > and set of SPs to perform some calculations. > > Ideally I would need "temporary view' functionality. > > CREATE PROC MyProc (@tablename) > AS > CREATE TABLE #mytable (dt DATETIME) > EXEC ('CREATE VIEW #myview AS SELECT * FROM ' + tablebame) > EXEC Calc1 > EXEC Calc2 > SELECT * FROM #mytable > DROP TABLE #mytable > DROP VIEW #myview > RETURN 0. > > > CREATE PROC Calc1 > AS > INSERT #mytable SELECT max(col1) FROM #myview > > CREATE PROC Calc2 > AS > INSERT #mytable SELECT min(col1) FROM #myview > > > CalcX - can be very complex and I got many of them. > > Unfortunately SQL doesn't support "temporary view'. > Is there a workarround? > > Bye, Anatol > > > > > > > Jim,
the issue is very simple - table I need to query is parameter of SP. CREATE TABLE t20060101 (dt DATETIME, id INT, Col1 INT, Col2 INT) CREATE TABLE t20060102 (dt DATETIME, id INT, Col1 INT, Col2 INT) CREATE TABLE t20060103 (dt DATETIME, id INT, Col1 INT, Col2 INT) .... each daily table has dozens millions rows. dt+id is primary key sample data ... INSERT t20060101 VALUES ( '2006/1/1 7:00', 1 ,0, 0 ) INSERT t20060101 VALUES ( '2006/1/1 7:00', 2 ,0, 0 ) INSERT t20060101 VALUES ( '2006/1/1 7:01', 1 ,0, 0 ) .... it doesn't matter acctually. Dynamilc SQL works only when I do simple queries. I need hundreds lines of t-sql code (some data mining) and want reorgananize that and divide into dozen simpler SPs. Partitioned view is nice idea but has bad performance CREATE VIEW MyView as SELECT * FROM t20060101 UNION ALL SELECT * FROM t20060102 UNION ALL SELECT * FROM t20060103 .... Bye, Anatoli Show quote "Jim Underwood" <james.underwoodATfallonclinic.com> wrote > A"temporary view" would be a temporary table. Use two temp tables to do > what you need. > > However, I don't see why you need to use this view at all. Why not just > do > this... > > Even better, post a full description of what you are trying to do, > including > DDL and sample data, and you may be able to eliminate the dynamic SQL and > temp tables entirely. Most folks recommend avoiding both of these and > using > them only when necessary. > > > CREATE PROC MyProc (@tablename) > AS > CREATE TABLE #mytable (dt DATETIME) > EXEC ('INSERT #mytable SELECT max(col1) FROM ' + (@tablename) > EXEC ('INSERT #mytable SELECT min(col1) FROM ' + (@tablename) > > SELECT * FROM #mytable > DROP TABLE #mytable > DROP VIEW #myview > RETURN 0. > > > "Anatoli Dontsov" <Anat***@dontsov.com> wrote in message > news:%23JWq$C4eGHA.1436@TK2MSFTNGP05.phx.gbl... >> Hi, All! >> >> I have many tables with name convention tYYYYMMDD >> and set of SPs to perform some calculations. >> >> Ideally I would need "temporary view' functionality. >> >> CREATE PROC MyProc (@tablename) >> AS >> CREATE TABLE #mytable (dt DATETIME) >> EXEC ('CREATE VIEW #myview AS SELECT * FROM ' + tablebame) >> EXEC Calc1 >> EXEC Calc2 >> SELECT * FROM #mytable >> DROP TABLE #mytable >> DROP VIEW #myview >> RETURN 0. >> >> >> CREATE PROC Calc1 >> AS >> INSERT #mytable SELECT max(col1) FROM #myview >> >> CREATE PROC Calc2 >> AS >> INSERT #mytable SELECT min(col1) FROM #myview >> >> >> CalcX - can be very complex and I got many of them. >> >> Unfortunately SQL doesn't support "temporary view'. >> Is there a workarround? >> >> Bye, Anatol >> >> >> >> >> >> >> > > On Sun, 21 May 2006 22:12:01 -0400, Anatoli Dontsov wrote:
(snip) >Partitioned view is nice idea but has bad performance Hi Anatoli,> >CREATE VIEW MyView as > SELECT * FROM t20060101 > UNION ALL > SELECT * FROM t20060102 > UNION ALL > SELECT * FROM t20060103 Based on the CREATE TABLE statements you posted, this is not a partitioned view at all. It will only be a partitioned view (and gain the advantages of it) if you add a CHECK constraint to the underlying table, so that SQL Server knows what data lives in which table. For instance: CREATE TABLE t20060101 (dt DATETIME NOT NULL, id INT NOT NULL, Col1 INT, Col2 INT, PRIMARY KKEY (dt, id), CHECK (dt >= '20060101' AND dt < '20060102') ) With that, a query against the partitioned view that includes a filter on the dt column should only hit the table(s) with matching rows. -- Hugo Kornelis, SQL Server MVP Hi, Hugo.
I ommited CHECK and PK to not overload text. I created partitioned view exaclty as described in BOL. The resourses to select table to get data from are not 0 and for intensive data mining performace penalty in visible. the same SP accessing the first table in chain of tables in the view takes 2 minutes, and for the second table - 15 minutes. Best regards, Anatoli Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrot > Based on the CREATE TABLE statements you posted, this is not a > partitioned view at all. It will only be a partitioned view (and gain > the advantages of it) if you add a CHECK constraint to the underlying > With that, a query against the partitioned view that includes a filter > on the dt column should only hit the table(s) with matching rows. On Thu, 25 May 2006 22:58:59 -0400, Anatoli Dontsov wrote:
>Hi, Hugo. Hi Anatoli,> >I ommited CHECK and PK to not overload text. >I created partitioned view exaclty as described in BOL. >The resourses to select table to get data from are not 0 and for intensive >data mining performace penalty in visible. > >the same SP accessing the first table in chain of tables in the view takes 2 >minutes, and for the second table - 15 minutes. A partitioned view should perform better than that. Can you post the *exact* query you are using to query the data? Adding the *exact* CREATE TABLE and CREATE VIEW statements would help as well. Also, can you post the query plan of a query that takes too long? -- Hugo Kornelis, SQL Server MVP Anatoli,
Hugo has given you good advice, and I agree that a partitioned view should have good performance. It is not a good idea to use many tables with names like tYYYYMMDD without a partitioned view However, here is a suggestion if you cannot make the partitioned view work correctly - use CONTEXT_INFO to do the work of the partitioned view more directly. Here is a repro script to give you the idea. Warning: On my instance of SQL Server 2005, the repro fails because some metadata is not cleared for the database Anatoli I create for the script. So in 2005 add a statement to drop the temp table for the repro. The repro should work in 2000, and in 2005, you would want to use the new CONTEXT_INFO function anyway. -- *** Don't run this if you have a database named Anatoli !! use master go create database Anatoli go use Anatoli go create table T1 (i int primary key, d datetime) create table T2 (i int primary key, d datetime) create table T3 (i int primary key, d datetime) create table T4 (i int primary key, d datetime) insert into T1 values(1,getdate()) insert into T2 values(2,getdate()) insert into T3 values(3,getdate()) insert into T4 values(4,getdate()) go create view aView as select i, d from T1 where ( select context_info from master.dbo.sysprocesses where spid = @@spid ) = cast('T1' as varbinary(20)) union all select i, d from T2 where ( select context_info from master.dbo.sysprocesses where spid = @@spid ) = cast('T2' as varbinary(20)) union all select i, d from T3 where ( select context_info from master.dbo.sysprocesses where spid = @@spid ) = cast('T3' as varbinary(20)) union all select i, d from T4 where ( select context_info from master.dbo.sysprocesses where spid = @@spid ) = cast('T4' as varbinary(20)) go create table #temp(i int) go create proc Calc1 as insert into #temp select max(i) from aView go DECLARE @tname VARBINARY(20) SET @tname = CAST('T3' AS VARBINARY(20)) SET CONTEXT_INFO @tname go set statistics io on exec Calc1 set statistics io off select * from #temp go use master go drop database Anatoli go Anatoli Dontsov wrote: Show quote >Hi, All! > >I have many tables with name convention tYYYYMMDD >and set of SPs to perform some calculations. > >Ideally I would need "temporary view' functionality. > >CREATE PROC MyProc (@tablename) >AS >CREATE TABLE #mytable (dt DATETIME) >EXEC ('CREATE VIEW #myview AS SELECT * FROM ' + tablebame) >EXEC Calc1 >EXEC Calc2 >SELECT * FROM #mytable >DROP TABLE #mytable >DROP VIEW #myview >RETURN 0. > > >CREATE PROC Calc1 >AS > INSERT #mytable SELECT max(col1) FROM #myview > >CREATE PROC Calc2 >AS > INSERT #mytable SELECT min(col1) FROM #myview > > >CalcX - can be very complex and I got many of them. > >Unfortunately SQL doesn't support "temporary view'. >Is there a workarround? > >Bye, Anatol > > > > > > > > > Correction - the repro should have
DROP TABLE #temp at the end, no matter what version of SQL Server you are running. I was not thinking when I added the Warning - obviously #temp does not get dropped when the database is dropped... This should not affect the possibility of using this idea. SK Steve Kass wrote: Show quote > Anatoli, > > Hugo has given you good advice, and I agree that > a partitioned view should have good performance. > It is not a good idea to use many tables with names > like tYYYYMMDD without a partitioned view > > However, here is a suggestion if you cannot make the > partitioned view work correctly - use CONTEXT_INFO > to do the work of the partitioned view more directly. > Here is a repro script to give you the idea. > > Warning: On my instance of SQL Server 2005, the > repro fails because some metadata is not cleared for > the database Anatoli I create for the script. So in 2005 > add a statement to drop the temp table for the repro. > The repro should work in 2000, and in 2005, you would > want to use the new CONTEXT_INFO function anyway. > > -- *** Don't run this if you have a database named Anatoli !! > > use master > go > create database Anatoli > go > use Anatoli > go > > create table T1 (i int primary key, d datetime) > create table T2 (i int primary key, d datetime) > create table T3 (i int primary key, d datetime) > create table T4 (i int primary key, d datetime) > insert into T1 values(1,getdate()) > insert into T2 values(2,getdate()) > insert into T3 values(3,getdate()) > insert into T4 values(4,getdate()) > go > > create view aView as > select i, d from T1 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T1' as varbinary(20)) > union all > select i, d from T2 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T2' as varbinary(20)) > union all > select i, d from T3 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T3' as varbinary(20)) > union all > select i, d from T4 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T4' as varbinary(20)) > go > > create table #temp(i int) > go > > create proc Calc1 as > insert into #temp select max(i) from aView > go > > DECLARE @tname VARBINARY(20) > SET @tname = CAST('T3' AS VARBINARY(20)) > SET CONTEXT_INFO @tname > go > > set statistics io on > exec Calc1 > set statistics io off > > select * from #temp > go > use master > go > drop database Anatoli > go > > > > > Anatoli Dontsov wrote: > >> Hi, All! >> >> I have many tables with name convention tYYYYMMDD >> and set of SPs to perform some calculations. >> >> Ideally I would need "temporary view' functionality. >> >> CREATE PROC MyProc (@tablename) >> AS >> CREATE TABLE #mytable (dt DATETIME) >> EXEC ('CREATE VIEW #myview AS SELECT * FROM ' + tablebame) >> EXEC Calc1 >> EXEC Calc2 >> SELECT * FROM #mytable >> DROP TABLE #mytable >> DROP VIEW #myview >> RETURN 0. >> >> >> CREATE PROC Calc1 >> AS >> INSERT #mytable SELECT max(col1) FROM #myview >> >> CREATE PROC Calc2 >> AS >> INSERT #mytable SELECT min(col1) FROM #myview >> >> >> CalcX - can be very complex and I got many of them. >> >> Unfortunately SQL doesn't support "temporary view'. >> Is there a workarround? >> >> Bye, Anatol >> >> >> >> >> >> >> >> >> That's a very nice use of context_info, Steve! I would add to pick just one
row from sysprocesses ...paralellism can bite...;-) where ( select top 1 context_info from master.dbo.sysprocesses where spid = @@spid ) = cast('T2' as varbinary(20)) -- Show quote-oj "Steve Kass" <sk***@drew.edu> wrote in message news:uuaoGgHgGHA.3916@TK2MSFTNGP04.phx.gbl... > Anatoli, > > Hugo has given you good advice, and I agree that > a partitioned view should have good performance. > It is not a good idea to use many tables with names > like tYYYYMMDD without a partitioned view > > However, here is a suggestion if you cannot make the > partitioned view work correctly - use CONTEXT_INFO > to do the work of the partitioned view more directly. > Here is a repro script to give you the idea. > > Warning: On my instance of SQL Server 2005, the > repro fails because some metadata is not cleared for > the database Anatoli I create for the script. So in 2005 > add a statement to drop the temp table for the repro. > The repro should work in 2000, and in 2005, you would > want to use the new CONTEXT_INFO function anyway. > > -- *** Don't run this if you have a database named Anatoli !! > > use master > go > create database Anatoli > go > use Anatoli > go > > create table T1 (i int primary key, d datetime) > create table T2 (i int primary key, d datetime) > create table T3 (i int primary key, d datetime) > create table T4 (i int primary key, d datetime) > insert into T1 values(1,getdate()) > insert into T2 values(2,getdate()) > insert into T3 values(3,getdate()) > insert into T4 values(4,getdate()) > go > > create view aView as > select i, d from T1 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T1' as varbinary(20)) > union all > select i, d from T2 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T2' as varbinary(20)) > union all > select i, d from T3 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T3' as varbinary(20)) > union all > select i, d from T4 > where ( > select context_info from master.dbo.sysprocesses > where spid = @@spid > ) = cast('T4' as varbinary(20)) > go > > create table #temp(i int) > go > > create proc Calc1 as > insert into #temp select max(i) from aView > go > > DECLARE @tname VARBINARY(20) > SET @tname = CAST('T3' AS VARBINARY(20)) > SET CONTEXT_INFO @tname > go > > set statistics io on > exec Calc1 > set statistics io off > > select * from #temp > go > use master > go > drop database Anatoli > go > > Good point. I should add that I've never tried this out
in production, but I'd be very interested to know if it turns out to work well ... or not. :| SK oj wrote: Show quote >That's a very nice use of context_info, Steve! I would add to pick just one >row from sysprocesses ...paralellism can bite...;-) > >where ( > select top 1 context_info from master.dbo.sysprocesses > where spid = @@spid >) = cast('T2' as varbinary(20)) > > > >  |
|||||||||||||||||||||||
Other interesting topics