• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

CTE, table variable, or temp table pros and cons

I've written a lot of SQL over the years, but I recently ran into a situation where I wanted to select records from a SQL table based on values in a table which is local to Access.  I can simply create the query in Access joining the linked table to the Access table, but I'm wondering if there isn't a more efficient method.  This stemmed from an EE question in which the user indicated that they have read-only permissions on the SQL Server

This is for a report, so the recordset returned from SQL Server does not have to be updateable.  With the help of a couple of Access MVPs, I found several syntax which worked in a pass-through query:
SET NOCOUNT ON 

CREATE TABLE #TempData (PO_Number nvarchar(10))
Insert into #TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * 
FROM yourTable INNER JOIN #TempData ON yourTable.FieldName = #TempDate.PO_Number

Open in new window

However, I doubt this syntax would work in a read-only connection to the server.
SET NOCOUNT ON 

declare @TempData as table (PO_Number nvarchar(10))
insert into @TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * 
FROM yourTable INNER JOIN @TempData ON yourTable.FieldName = @TempDate.PO_Number

Open in new window

and one of the MVPs' recommended a syntax of:
;WITH
cteC AS
(SELECT 'abc123' PO_Num
 UNION ALL SELECT 'xyz456'
 UNION ALL SELECT 'mno789'
)
SELECT SomeTable.*
FROM SomeTable INNER JOIN cteC ON SomeTable.PO_Number = cteC.PO_Num

Open in new window


So, what are the advantages/disadvantages of each of these?  

Do any of these techniques allow you to create an index on the new table (temp, cte, or variable)?  

If the tempdb method will not work with a read-only connection, can you modify the syntax of that example so that it would run with a read-only connection? Maybe with a Run As command?

I don't know what version SQL Server the OP is using, so can someone tell me what versions of SQL Server that the CTE and table variables became available.

Thanks for your assistance.
0
Dale Fye
Asked:
Dale Fye
  • 2
  • 2
3 Solutions
 
ste5anSenior DeveloperCommented:
As far as I know, there is no read only connection. You have either only SELECT permissions or the database is set to read only. The tempdb cannot be set to read-only.

When your given samples show the correct magnitude of cardinatlity, then performance is not a problem and you don't need any index on your "filter table". So I assume the performance should be almost equal.

When your "filter table" is large, then indices can help:

CREATE TABLE #TempData (PO_Number nvarchar(10) primary key)

Open in new window


But the biggest performance problems will arise from your later JOIN in Access. When your passthrough returns many rows, than you will see "performance" problems on the Access side due to network latency cause ACE needs to retrieve the entire dataset first.

or on SQL Server 2012+

declare @TempData as table (PO_Number nvarchar(10) primary key)

Open in new window

0
 
Dale FyeAuthor Commented:
ste5an,

Thanks for the response.  The whole point of using this techniques is to avoid the link to the Access table on the Access FE.  Thanks for the input on simply adding the PK to the tempdb or table variable method.  

The OP will build the pass-through query in Access with one of these options and will create the "filter table" to perform the filter operation on the server.

So, are there any advantages or disadvantages to the tempdb, CTE or table variable technique?
0
 
ste5anSenior DeveloperCommented:
As I alread said: there is a difference only when the filter table in the passthrough query itself is large, then a index helps. In this case using a indexed temp table or index table variable is better then the CTE approach. For only small sets all three should perform the same. And the sample with only three rows should perform also the same for all three approaches.

The only caveats from my experience: In rare cases the indexed table variable is slower than an unindexed one. And for very large filter tables use the temp table, cause the table variable is also materialized in such cases to tempdb, but the process doing that is somewhat slower than using an explict temp table.
0
 
Mark WillsTopic AdvisorCommented:
If you can connect to the server, then you can create a temporary table.

It is possible to revoke the connect permission to tempdb but doubt that would be in place unless they have an anally retentive security mastermind playing with fire. Not entirely unheard of, but extremely doubtful.

Temp tables would be best - but will be automatically dropped at end of procedure / session etc.  So beware, it will be dropped when they go out of scope. Global temp tables are visible to all current sessions.

CTE is a Common Table Expression, so you materialise the data in a select statement. Table variables are not much different in that regard, and both only exist for the scope of the batch.

For small data counts, it probably wont matter.

For large data and/or multi steps, multi statements then you need a temp table and would be my recommendation.
0
 
Dale FyeAuthor Commented:
Thanks for your help guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now