Memory Table vs. CTE

I had posted a week or so ago about a performance issue with Null-able
variables being passed into stored procedures and the performance
gains by setting those parameters to local variables declared in the
stored procedure.  I followed that advice and all was well.  How ever,
the stored procedure started to time out the again, running the stored
proc on my local SQL server would take 3:45 to return 5 rows.
Something was wrong.  The stored procedure was using 4 CTE tables, one
of those tables being referenced in 5 different locations through-out
the stored proc. The only change I made to the stored procedure was
to  replace the CTE that is referenced 5 times, with a local memory
table and re-ran the stored proc.  My processing time went from 3:45
to 2 seconds.  I was under the assumption that CTE's were just memory
tables that were dynamically created by the select statement contained
within the With statement.  We have alot of CTE's being used without
our Stored procs that do reporting on our log tables, which will only
get larger.  And before I go out and start replacing CTE's with memory
tables, I was hoping someone here could explain why there is such a
performance increase using the memory table vs the CTE.

Thanks in advance,
Jeremiah
0
Brocja01
3/4/2010 2:51:36 PM
sqlserver.programming 1873 articles. 0 followers. Follow

7 Replies
1517 Views

Similar Articles

[PageSpeed] 31

I am really not sure what you reference as "memory tables". Perhaps you mean table variables, or temporary tables (but 
both are materialized to tempdb, so they are not really "memory tables"). CTEs are not tables but table expressions. 
They are not materialized but get expanded in the query plan, very much like a view is expanded (assuming regular view, 
not indexed). Because of that if you use CTEs with complex queries you may end up with inefficient execution plans. A 
better approach may be to materialize the temporary result set to temp tables (temp tables have statistics and you can 
create indexes on them; statistics are not maintained for table variables).

Here is one example:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/4/2010 3:14:55 PM
Hi
I assume you mean by memory table  local temporary table and table variable, 
am I right?
I think that having temoprary table helps optimizer to estimate number of 
rows to be returned correctly , io and etc by using statistics
On the other hand , SQL Server is unable to create statistics on CTE   and 
has to guess ...to be on target.

Also   you can create indexes on temprary table and table variable to speed 
up the queries

Finally , take a look into an execution plan for both versions and compare 
them to figure out how the optimizer peforms the query






"Brocja01" <brocja01@gmail.com> wrote in message 
news:4ff2c399-8c2d-4d14-901e-08abd0686248@g10g2000yqh.googlegroups.com...
>I had posted a week or so ago about a performance issue with Null-able
> variables being passed into stored procedures and the performance
> gains by setting those parameters to local variables declared in the
> stored procedure.  I followed that advice and all was well.  How ever,
> the stored procedure started to time out the again, running the stored
> proc on my local SQL server would take 3:45 to return 5 rows.
> Something was wrong.  The stored procedure was using 4 CTE tables, one
> of those tables being referenced in 5 different locations through-out
> the stored proc. The only change I made to the stored procedure was
> to  replace the CTE that is referenced 5 times, with a local memory
> table and re-ran the stored proc.  My processing time went from 3:45
> to 2 seconds.  I was under the assumption that CTE's were just memory
> tables that were dynamically created by the select statement contained
> within the With statement.  We have alot of CTE's being used without
> our Stored procs that do reporting on our log tables, which will only
> get larger.  And before I go out and start replacing CTE's with memory
> tables, I was hoping someone here could explain why there is such a
> performance increase using the memory table vs the CTE.
>
> Thanks in advance,
> Jeremiah 


0
Uri
3/4/2010 3:22:12 PM
Sorry guys, I meant temp tables.  This is the SQL I used to create my
temp table:
		Declare @ReservationHistory TABLE
		(
			HotelId INT,
			EventId INT,
			[Name] nVarchar(max),
			EventRoomTypeId INT,
			RoomTypeName nVarchar(max),
			RoomBookingId INT,
			SmokingPreference nVarchar(max),
			Comments nVarchar(max),
			RoomActivityType nVarchar(max),
			RoomBookingHistoryId INT,
			RoomGuestHistoryId INT,
			CheckInDate DateTime,
			CheckOutDate DateTime,
			IsBookedAttendee Bit,
			AttendeeId INT,
			BookedFirstName nVarchar(max),
			BookedLastName nVarchar(max),
			PartyName nVarchar(max),
			FirstName nVarchar(max),
			LastName nVarchar(max),
			HistoryActivityDate DateTime,
			HistoryActivityType nVarchar(max),
			UserId INT,
			UserName nVarchar(max)
		);

So, what you both are saying then, CTE's are great for "non" complex
SQL?  And anytime you are going to be using the data more than once, a
temp tables is a better choice?

0
Brocja01
3/4/2010 3:29:21 PM
This is table variable. How many rows do you insert into?



"Brocja01" <brocja01@gmail.com> wrote in message 
news:87318006-854c-4d2a-8868-6b8138e51b12@y17g2000yqd.googlegroups.com...
> Sorry guys, I meant temp tables.  This is the SQL I used to create my
> temp table:
> Declare @ReservationHistory TABLE
> (
> HotelId INT,
> EventId INT,
> [Name] nVarchar(max),
> EventRoomTypeId INT,
> RoomTypeName nVarchar(max),
> RoomBookingId INT,
> SmokingPreference nVarchar(max),
> Comments nVarchar(max),
> RoomActivityType nVarchar(max),
> RoomBookingHistoryId INT,
> RoomGuestHistoryId INT,
> CheckInDate DateTime,
> CheckOutDate DateTime,
> IsBookedAttendee Bit,
> AttendeeId INT,
> BookedFirstName nVarchar(max),
> BookedLastName nVarchar(max),
> PartyName nVarchar(max),
> FirstName nVarchar(max),
> LastName nVarchar(max),
> HistoryActivityDate DateTime,
> HistoryActivityType nVarchar(max),
> UserId INT,
> UserName nVarchar(max)
> );
>
> So, what you both are saying then, CTE's are great for "non" complex
> SQL?  And anytime you are going to be using the data more than once, a
> temp tables is a better choice?
> 


0
Uri
3/4/2010 3:57:03 PM
This is table variable, not temp table... With CTEs you can use the data set defined by the CTE only in the same query. 
If you need to use it in multiple queries you have to redefine the CTE and re-execute. When you use a temp table/table 
variable you materialize the result set and can reuse it without the need to query data to define the result set again.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/4/2010 3:57:45 PM
Anywhere from 5 to 5k I would assume.  The most right now that I
"know" of is 1012 rows.  Why do you ask?
0
Brocja01
3/4/2010 4:02:57 PM
Brocja01 wrote:
> 
> Sorry guys, I meant temp tables.  This is the SQL I used to create my
> temp table:
>                 Declare @ReservationHistory TABLE
>                 (
>                         HotelId INT,
>                         EventId INT,
>                         [Name] nVarchar(max),
>                         EventRoomTypeId INT,
>                         RoomTypeName nVarchar(max),
>                         RoomBookingId INT,
>                         SmokingPreference nVarchar(max),
>                         Comments nVarchar(max),
>                         RoomActivityType nVarchar(max),
>                         RoomBookingHistoryId INT,
>                         RoomGuestHistoryId INT,
>                         CheckInDate DateTime,
>                         CheckOutDate DateTime,
>                         IsBookedAttendee Bit,
>                         AttendeeId INT,
>                         BookedFirstName nVarchar(max),
>                         BookedLastName nVarchar(max),
>                         PartyName nVarchar(max),
>                         FirstName nVarchar(max),
>                         LastName nVarchar(max),
>                         HistoryActivityDate DateTime,
>                         HistoryActivityType nVarchar(max),
>                         UserId INT,
>                         UserName nVarchar(max)
>                 );
> 
> So, what you both are saying then, CTE's are great for "non" complex
> SQL?  And anytime you are going to be using the data more than once, a
> temp tables is a better choice?

As Uri and Plamen mentioned, the key is that a CTE is just a result
specification. Each query that uses a CTE will perform the actual
selection (again). When using CTEs SQL Server will not create or save
any intermediate result.

So if your CTE is computationally hard or returns many rows, and it is
accessed multiple times, then a table, temp table or table variable
could perform (much) better. Which is best depends on your situation.
Maybe you only need the temporary table to hold the primary key values?
Maybe you need all the columns you mention...

-- 
Gert-Jan
0
Gert
3/4/2010 10:46:56 PM
Reply:

Similar Artilces:

Sorting in pivot tables
Any web-sites/guides discussing Top 10, descending sorts and the like using pivot tables in Excel. These functions always seem incredibly tempremental... ...

Memory usage in XP (memory leak detection)
Hi How can I found out the memory usage on XP ? My XP box uses over a week all memory and then has no resources available. It has 4GB memory (3.5 GB free) I used typeperf with the following: \Memory\Available Bytes \Memory\Committed Bytes \Memory\Pool Paged Bytes \Memory\Pool Nonpaged Bytes \Memory\Cache Bytes \Memory\Cache Bytes Peak \Process(_total)\Virtual Bytes Peak \Process(_total)\Virtual Bytes \Process(_total)\Working Set Peak \Process(_total)\Working Set \Process(_total)\Page File Bytes Peak \Process(_total)\Page File Bytes \Process(_total)\Private Bytes \Proc...

not enough memory on excel 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel <i>run excel 2008 on mac snow leopard 2 g memory i upgrade to 3 g and same</i>&#32; message not enough memory can some one help me only word working Run Remove Office then re-install Office On 30/03/10 7:08 AM, in article 59bb629f.-1@webcrossing.JaKIaxP2ac0, "Snow_leopard@officeformac.com" <Snow_leopard@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel > run excel 2008 on mac snow leopard 2 g memory i upgrade t...

Pivot table data dissappears
I am using data from an Excel sheet (Excel2003 sp1) that is on the same page as my pivot table. It works exactly as I want it to whenever I click on different categories from the dropdown boxes attached to "page," "column" or "row." It also works when I use the "data" dropdown box; that is, I eliminate some of the categories of data BUT when I click on the "data" dropdown arrow again, the categories that I deselected are no longer shown and I must drag the missing data category from the feild selection box to the pivot table in order t...

Tables and Relationships Question
I'm wanting to generate evaluation forms for student assignments for a series of dates. So far I've created the following tables: Students Table ------------ Name *StudentID Assignments Table -------------- StudentID *Assignment Work Location Start Time *=Primary key The problem I can't get my head around is how to handle the table structure and relationships for the series of dates on which the student will be training on the assignment. Using a report, I'd like to generate one evaluation sheet per student for each of the 7 dates they will be pe...

Grouping In Pivot Tables #3
Hi I have a pivot table linked direct to an access query. In the pivot table I have grouped the date field by year & month and set the start date as 1 Apr 2005 and end date as 31 Mar 2006. Some of the months have no data so I set the field settings to 'include items with no data' however, this then shows blank fields for Jan, Feb & Mar 2005, if I try filtering or hiding them out it also hides Jan, Feb & Mar 2006. How can I get it to only show from Apr 05 to Mar 06 and include fields where there was no data between those dates? Many Thanks GLS -- GLS Uncheck &quo...

Pivot Table Error Message
I received the following message when dragging my "Products" item onto my Pivot Table. "A field in your source data has more unique items than can be used in a Pivot Table." I have 14,802 unique Product items. Does anyone know what the limitation is AND how to get around it? The following MSKB article outlines the PivotTable limits in Excel 2002, and has links to articles on other versions: XL2000: Limits of PivotTables in Microsoft Excel 2000 http://support.microsoft.com/default.aspx?id=211517 XL2002: Limits of PivotTables in Microsoft Excel 2002 http://supp...

Pivot Tables 01-22-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br><br>I have a Pivot Table based on a 5,000 row table which creates a 500 item drop-down list of names which is in alphabetical order. <br><br>I would like to be able to type in the first letter of a name into the drop-down list (or any other drop-down list) and then to choose a name from the shortened list of names beginning with that letter. <br><br>So instead of scrolling through 400 names to get to names beginning with &quot;S&quot;, I could just type in &...

when replicating, I get error message, but there is no table
when replicating with my assistant, I get that there are replication errors, but when I try to open the conflicts table, I get that there are "no conflict tables" =?Utf-8?B?YmttNGVhZ2xl?= <bkm4eagle@discussions.microsoft.com> wrote in news:5246CFE9-3BC1-4411-AB08-3E7B8D67D433@microsoft.com: > when replicating with my assistant, I get that there are > replication errors, but when I try to open the conflicts table, I > get that there are "no conflict tables" You'll have to look at the tables directly. The conflict tables are called BaseTabl...

Stock Count Entry window - Captured, Counted qty vs
Hi, I have a problem, in which Captured qty of an item is different from Captured Lot number qty. Basically the captured qty of an item is equal to our counted qty, but lot number had less. Due to that we cannot post our inventory. I found in the IV00300, there is on lot for this item and this site, which is not captured in the captured information of the lot numbers. That lot number was created during the reconciliation of the inventory of that item. Why is doing like this? Does Microsoft has any answer for that? Thanks I have run into this problem. For my site we ran both the Item...

Database list vs pivot
Hallo everyone, I am following a tutorial to do a simple database list in excel. The tutorial suggest me to do Data>List but in Data menu in excel 2000 I don't have list!!!! Someone could tall me how to create a list with Excel 2000. If I select Pivot start a wizard that cover with a mask my data Tnx Data|List was added in xl2003 (IIRC). It doesn't exist in xl2k. Maybe you could use data|filter to see some of the features of data|list. "gabinettoski@supereva.it" wrote: > > Hallo everyone, > > I am following a tutorial to do a simple database list in ex...

Is it a memory leak
AoA I have used standard template library in one of my applications. I belive that memory leaks are due to stl components, mainly string. i wrote a sample program in VC 6.0 that clearly shows memory leaks in stl wstring str = L""; for( int a = 0 ; a < 100000 ; a++ ) { str += L"int"; } str.str.erase( str.begin() , str.end() ); str = L"int"; i wrote this code against a button in dialog based application. Before clicking the button, the memory usage is 3MB and after this code it is 17MB although i believe it should have been bac...

What is the difference between table relationships and links?
I am new to Access & need to understand the differences between a link & a relat ionship to a table or database. I am told the table must be created before the database, but I need several tables for my data. I need to ensure I use the correct method to allow me to create a database using the various connected tables. Please help! A link to a table is basically a connection path to a source of data that exists somewhere other than the current Access application. The data source can be to a table in an MS SQL Server, Oracle, a text file, an Excel sheet, or anoth...

update yes / no field in table based on another table
I am wanting to update a yes/no field in one table based on another table. If the name is in both tables to put a "yes" value in the field If the name is in one table but not the other to put a "no" value in the field For example: Table 1: Name Table 2: Name Check box = yes Table 1: Null (Name not in table) Table 2: Name check box = no On Mon, 3 Mar 2008 14:55:01 -0800, Bryan <Bryan@discussions.microsoft.com> wrote: >I am wanting to update a yes/no field in one table based on another table. > >If the name is in both tables to put a "yes" va...

Re-set autonumber in a table (primary key)
I have built a database and tested it thoroughly. I deleted the test records but now my first record has an automunber or 15 and this is winding me up. Can I re-set the autonumber back to 0? Try a compact and repair on the DB (front- or back-end) having that table. "Biffo" wrote: > I have built a database and tested it thoroughly. I deleted the test records > but now my first record has an automunber or 15 and this is winding me up. > Can I re-set the autonumber back to 0? Delete the field. Save the table. Insert a new autonumber field -- Wayne Manchester, Englan...

Lines of text disappearing in Word Tables
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Hi, <br> I'm having a weird issue with text within tables in my word document. For some reason, if I delete the last row of a table that I'm working on, the previous row's text disappears. What's even more strange is that the first line of text disappears if there are two lines of text in the row, and the second row is fine. <br><br>Any help would be greatly appreciated! Sorry, not enough detail to answer. 1) Select the entire row and choose Edit>Clear>Clear Formatting. Now what ha...

Exchange memory utilization
Is there any documentation detailing the exchange 2003 memory utilization , especially in coordination with the max store size. thanks CR What do you want to know? Exchange will use all the memory it can in the box, up to 4GB, for caching, and this isn't a function of store size. It will release memory so that other processes can get what they need. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "MSNews" <Craig@nowhere.org> wrote in message news:etwPVF%23RHHA.4832@TK2MSFTNGP03.phx.gbl... > Is there any documentati...

Lookup and tables
Orientation of collector Tilt of collector South SE/SW E/W NE/NW North Horizontal 933 933 933 933 933 30 degrees 1042 997 886 762 709 45 degrees 1023 968 829 666 621 60 degrees 960 900 753 580 485 Vertical 724 684 565 427 360 I need to make it so Excel will Lookup a piece of data in the above table when someone writes in the Tilt of the collector in one cell, and the orientation in another. IE Tilt of collector: 30 Orientation ...

Pivot Table Will Not Expand Beyond Ten Columns
I have designed a pivot table that I intended to update each month. My data table includes a column for the date at the monthly level. Until this month, it was working fine. As each month passed, I would simply check off the new month in the drop down menu in the pivot table. However, for some reason, when I select November, the table does not expand one column. Instead, the table stays the same size, but drops March data (though March remains selected in the drop down). I've never come across this before. Any ideas what I'm doing wrong? Thanks! Is there still March data in the ...

Create One Record in a New Table from several tables
I have 4 "master" tables of information - Agent, City, Dept, and Level. I have a form that has a list box for each field in each of the 4 tables [4 fields total]. What I need is: The User clicks to select the field information needed in each of the 4 tables, then I want them to hit a command button and it creates a new record in another table with the selected information. Can anyone help me? Would greatly appreciate it! On Tue, 8 Jan 2008 10:00:03 -0800, DF2008 <DF2008@discussions.microsoft.com> wrote: >I have 4 "master" tables of information - Agent, ...

Text Effects: Word 2003 vs. Word 7
I sent a corrected document to a family member and where I made the corrections I inserted text effects (las vegas lights). I have Word 2003 and she has Word 7. Somehow the LV lights got saved to default in her word program and now she can't remove it. Every new document she types has the infernal lights surrounding the text. Does anyone know how to get that permanently deleated from the default? Thank you. On Wed, 9 Dec 2009 19:47:02 -0800, DertyChenchilla <DertyChenchilla@discussions.microsoft.com> wrote: >I sent a corrected document to a family member and wher...

how to count columns i pivot table?
Hi My pivot is dynamic, created with VB, and number of columns will vary. I need this count in a variable for use in further processing. Any assistance appreciated! /ulf Untested: Sub aj() myCol = ActiveSheet.PivotTables(1).TableRange2.Columns.Count MsgBox myCol End Sub "ulfb" <ulfb@discussions.microsoft.com> wrote in message news:E4C9BA4A-F065-48D3-9852-F2A7E2BF30B9@microsoft.com... > Hi > My pivot is dynamic, created with VB, and number of columns will vary. > I need this count in a variable for use in further processing. > Any assistance ap...

Out of Memory
I am using Excel 2000. Operating system is MS 2000. Ram = 512. I am running a large Excel file 65,536 rows by 24 columns. The columns may expand somewhat; say 5 extra rows as the Macro runs. The Excel file links to another Excel file to do some vlookups. Some formatting is going on as well as Paste Special Value. I get an out of error message and Excel stops. If I only have 50,000 rows it works fine. I have increased the virtual memory to the maximum, same thing. Any suggestions???? Hi, There are only 65536 rows in a worksheet. You cannot use all the rows. Split your sheet into sevra...

memory growing spreadsheet
Hi Again I have another query- For some reason when working on a spreadsheet it can start with say, 925kb in size,I do a couple of changes, then "Wolla" 10+mb It only happens every now and again, but I have tried copy then paste special, taken all macro's out, save as a different name,copy sheet into different book, but still can't bring it back down in size, "Hu" I even took my changes back out What is going on? John .. Hi John, What are you doing? Whatever it is, I think I'd want to do something else. But I haven't any hint what to avoid doing &...

starting a pivot table
I've never done a pivot table before, so i don't really know what i'm doing. I'm trying to create one but the data i have isnt single rows. Instead it is chunks of rows and columns. For example, i have five columns and the first column has one thing under it, the next has one, but the next has three things and the next has one and the last has five things. But those are all one group of data because they all belong to whats listed in the first column. Can anyone understand what i mean? Hi It sounds like the data contained in the other columns are not 'linked' to ...