Eliminate #tmp table to single SQL Select statement
in [Programming SQL]
|
From: Jennifer Mathews on 14 Feb 2010 11:51 >Sometimes it is performs better compared to temp tables, sometimes it performs worse. Couldn't everyone just have said it ALWAYS performs better? :) On tiny developer test tables where there are not many records, is their a way to compare performance? "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:evMWufVrKHA.5036(a)TK2MSFTNGP02.phx.gbl... > It is pretty much like a dynamically defined view. I.e., the query of the CTE and the > "outer query" are combined (like a macro) and the whole is optimized. Sometimes it is > performs better compared to temp tables, sometimes it performs worse. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message > news:#WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl... >> But I have a question. Is a CTE just another name for a temp table? Is it creating >> a physical temp table or it it a memory temp table? >> >> "Jay" <spam(a)nospam.org> wrote in message >> news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>> Wait till you get a load of CTE's and Pivots >>> >>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I had >>>> known this awhile ago instead of using temp tables. >>>> >>>> Thanks again. >>>> >>>> >>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>>>I have been trying to redo some of my SQL without temp tables and Loops so here is >>>>>another question I have not been able to figure out. >>>>> >>>>> Table Bk_Reviews >>>>> Person_ID int >>>>> Book_ID int Book_Comment varchar(2000) >>>>> Person_ID Book_ID Book_Comment >>>>> 100 50 whatever1 >>>>> 100 60 whatever2 >>>>> 105 70 whatever3 >>>>> 105 80 whatever4 << same book >>>>> 105 80 whatever5 << same book >>>>> >>>>> To get the total number of comments by a person I use: >>>>> >>>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105 >>>>> RETURNS 3 for Person_ID=105 >>>>> >>>>> Most people only comment once for a book but people can have multiple comments per >>>>> book such as Person_ID=105 \ Book_ID=80 >>>>> >>>>> I am currently using a temp table to count the number of Total UNIQUE comments >>>>> excluding duplicates per book. >>>>> >>>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>>>> FROM Bk_Reviews WHERE Person_ID = 105 >>>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105 >>>>> >>>>> Person_ID Book_ID 105 70 105 80 << doesn't matter that >>>>> has two comments >>>>> >>>>> Is there anyway I can do this in a single SELECT statement instead of using the >>>>> temp table tmp_Person2Book? >>>>> Thanks >>>>> >>>> >>> >>> >>
From: Tibor Karaszi on 14 Feb 2010 23:49 > On tiny developer test tables where there are not many records, > is their a way to compare performance? No, such comparison wouldn't be relevant for what you'd see for a larger amount of data, I'm afraid... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message news:eUT#3XZrKHA.728(a)TK2MSFTNGP04.phx.gbl... >>Sometimes it is performs better compared to temp tables, sometimes it >>performs worse. > > Couldn't everyone just have said it ALWAYS performs better? :) > > On tiny developer test tables where there are not many records, > is their a way to compare performance? > > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:evMWufVrKHA.5036(a)TK2MSFTNGP02.phx.gbl... >> It is pretty much like a dynamically defined view. I.e., the query of the >> CTE and the "outer query" are combined (like a macro) and the whole is >> optimized. Sometimes it is performs better compared to temp tables, >> sometimes it performs worse. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >> news:#WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl... >>> But I have a question. Is a CTE just another name for a temp table? Is >>> it creating a physical temp table or it it a memory temp table? >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>> Wait till you get a load of CTE's and Pivots >>>> >>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>>> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>>>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I >>>>> wish I had known this awhile ago instead of using temp tables. >>>>> >>>>> Thanks again. >>>>> >>>>> >>>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>>>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>>>>I have been trying to redo some of my SQL without temp tables and >>>>>>Loops so here is another question I have not been able to figure out. >>>>>> >>>>>> Table Bk_Reviews >>>>>> Person_ID int >>>>>> Book_ID int Book_Comment varchar(2000) >>>>>> Person_ID Book_ID Book_Comment >>>>>> 100 50 whatever1 >>>>>> 100 60 whatever2 >>>>>> 105 70 whatever3 >>>>>> 105 80 whatever4 << same book >>>>>> 105 80 whatever5 << same book >>>>>> >>>>>> To get the total number of comments by a person I use: >>>>>> >>>>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE >>>>>> Person_ID = 105 RETURNS 3 for Person_ID=105 >>>>>> >>>>>> Most people only comment once for a book but people can have multiple >>>>>> comments per book such as Person_ID=105 \ Book_ID=80 >>>>>> >>>>>> I am currently using a temp table to count the number of Total UNIQUE >>>>>> comments excluding duplicates per book. >>>>>> >>>>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>>>>> FROM Bk_Reviews WHERE Person_ID = 105 >>>>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for >>>>>> Person_ID=105 >>>>>> >>>>>> Person_ID Book_ID 105 70 105 80 << >>>>>> doesn't matter that has two comments >>>>>> >>>>>> Is there anyway I can do this in a single SELECT statement instead of >>>>>> using the temp table tmp_Person2Book? >>>>>> Thanks >>>>>> >>>>> >>>> >>>> >>> >
From: Jennifer Mathews on 15 Feb 2010 12:55 Thanks "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:%23IhhOofrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >> On tiny developer test tables where there are not many records, >> is their a way to compare performance? > > No, such comparison wouldn't be relevant for what you'd see for a larger amount of > data, I'm afraid... > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message > news:eUT#3XZrKHA.728(a)TK2MSFTNGP04.phx.gbl... >>>Sometimes it is performs better compared to temp tables, sometimes it performs worse. >> >> Couldn't everyone just have said it ALWAYS performs better? :) >> >> On tiny developer test tables where there are not many records, >> is their a way to compare performance? >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message >> news:evMWufVrKHA.5036(a)TK2MSFTNGP02.phx.gbl... >>> It is pretty much like a dynamically defined view. I.e., the query of the CTE and >>> the "outer query" are combined (like a macro) and the whole is optimized. Sometimes >>> it is performs better compared to temp tables, sometimes it performs worse. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> >>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>> news:#WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl... >>>> But I have a question. Is a CTE just another name for a temp table? Is it >>>> creating a physical temp table or it it a memory temp table? >>>> >>>> "Jay" <spam(a)nospam.org> wrote in message >>>> news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>>> Wait till you get a load of CTE's and Pivots >>>>> >>>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>>>> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>>>>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I had >>>>>> known this awhile ago instead of using temp tables. >>>>>> >>>>>> Thanks again. >>>>>> >>>>>> >>>>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>>>>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>>>>>I have been trying to redo some of my SQL without temp tables and Loops so here >>>>>>>is another question I have not been able to figure out. >>>>>>> >>>>>>> Table Bk_Reviews >>>>>>> Person_ID int >>>>>>> Book_ID int Book_Comment varchar(2000) >>>>>>> Person_ID Book_ID Book_Comment >>>>>>> 100 50 whatever1 >>>>>>> 100 60 whatever2 >>>>>>> 105 70 whatever3 >>>>>>> 105 80 whatever4 << same book >>>>>>> 105 80 whatever5 << same book >>>>>>> >>>>>>> To get the total number of comments by a person I use: >>>>>>> >>>>>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105 >>>>>>> RETURNS 3 for Person_ID=105 >>>>>>> >>>>>>> Most people only comment once for a book but people can have multiple comments >>>>>>> per book such as Person_ID=105 \ Book_ID=80 >>>>>>> >>>>>>> I am currently using a temp table to count the number of Total UNIQUE comments >>>>>>> excluding duplicates per book. >>>>>>> >>>>>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>>>>>> FROM Bk_Reviews WHERE Person_ID = 105 >>>>>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105 >>>>>>> >>>>>>> Person_ID Book_ID 105 70 105 80 << doesn't matter >>>>>>> that has two comments >>>>>>> >>>>>>> Is there anyway I can do this in a single SELECT statement instead of using the >>>>>>> temp table tmp_Person2Book? >>>>>>> Thanks >>>>>>> >>>>>> >>>>> >>>>> >>>> >>
From: TheSQLGuru on 15 Feb 2010 13:45
Yep, we COULD have said that . . . but we would have been unhelpful and fibbing. :-) -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message news:eUT%233XZrKHA.728(a)TK2MSFTNGP04.phx.gbl... > >Sometimes it is performs better compared to temp tables, sometimes it > >performs worse. > > Couldn't everyone just have said it ALWAYS performs better? :) > > On tiny developer test tables where there are not many records, > is their a way to compare performance? > > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:evMWufVrKHA.5036(a)TK2MSFTNGP02.phx.gbl... >> It is pretty much like a dynamically defined view. I.e., the query of the >> CTE and the "outer query" are combined (like a macro) and the whole is >> optimized. Sometimes it is performs better compared to temp tables, >> sometimes it performs worse. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >> news:#WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl... >>> But I have a question. Is a CTE just another name for a temp table? Is >>> it creating a physical temp table or it it a memory temp table? >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>> Wait till you get a load of CTE's and Pivots >>>> >>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>>> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>>>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I >>>>> wish I had known this awhile ago instead of using temp tables. >>>>> >>>>> Thanks again. >>>>> >>>>> >>>>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>>>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>>>>I have been trying to redo some of my SQL without temp tables and >>>>>>Loops so here is another question I have not been able to figure out. >>>>>> >>>>>> Table Bk_Reviews >>>>>> Person_ID int >>>>>> Book_ID int Book_Comment varchar(2000) >>>>>> Person_ID Book_ID Book_Comment >>>>>> 100 50 whatever1 >>>>>> 100 60 whatever2 >>>>>> 105 70 whatever3 >>>>>> 105 80 whatever4 << same book >>>>>> 105 80 whatever5 << same book >>>>>> >>>>>> To get the total number of comments by a person I use: >>>>>> >>>>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE >>>>>> Person_ID = 105 RETURNS 3 for Person_ID=105 >>>>>> >>>>>> Most people only comment once for a book but people can have multiple >>>>>> comments per book such as Person_ID=105 \ Book_ID=80 >>>>>> >>>>>> I am currently using a temp table to count the number of Total UNIQUE >>>>>> comments excluding duplicates per book. >>>>>> >>>>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>>>>> FROM Bk_Reviews WHERE Person_ID = 105 >>>>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for >>>>>> Person_ID=105 >>>>>> >>>>>> Person_ID Book_ID 105 70 105 80 << >>>>>> doesn't matter that has two comments >>>>>> >>>>>> Is there anyway I can do this in a single SELECT statement instead of >>>>>> using the temp table tmp_Person2Book? >>>>>> Thanks >>>>>> >>>>> >>>> >>>> >>> > |