table variable query optimisation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

table variable query optimisation

Author

Topic

Exgliderpilot
Starting Member

14 Posts

Posted - 2015-01-11 : 08:32:40

I have a table variable which (sometimes) needs to contain a couple of thousand rows, this slews the execution plan. This is fixed by DBCC TRACEON(2453) which causes the rows in the table variable to be estimated properly, this requires sysadmin rights which I am reluctant to grant to other than this stored proc. The proc is excuted by most users with SQL authentication. I've tried with exec as 'NT AUTHORITY\SYSTEM' but that does not seem to be recognized as I'm using sql authentication. Suggestions please? Is there another way to tell the proc to count the row in a table variable?

Staff bank agency scheduling software http:\\www.ava.co.uk
Blog
http://creamteadiet.blogspot.com/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-11 : 08:59:17

A couple thousand rows? How bad can the plan be? Do you have indexes on it? Have you tried a temp table?

Exgliderpilot
Starting Member

14 Posts

Posted - 2015-01-11 : 10:04:25

well it went from 10 seconds to abour 0.3 secs, and use a table variable because that blows linq and asp net, least I've never been able to get a stored proc with a temp table to produce a wrapper with linq...

Staff bank agency scheduling software http:\\www.ava.co.uk
Blog
http://creamteadiet.blogspot.com/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-11 : 12:49:46

I'm not sure why linq would care (or how it would even know) that you are using a temp table somewhere in your proc. I'm pretty sure that linq doesn't parse the proc, just the result set(s).

Try to replace the table variable with a temp table and change nothing else. (except to add indexes if appropriate). See if it works and makes a difference.

Try specifying with RECOMPILE on the proc. Check out the list of query hints to see if anything might fit. Note that you can force join types, among other things.

Exgliderpilot
Starting Member

14 Posts

Posted - 2015-01-12 : 04:54:08

From experience there are four issues with the linq engine and its "reflection" process. Its very clever, for instance having the procedure encrypted is NOT one of them, however changing a a table variable to a temp table is one of them, try it!

Staff bank agency scheduling software http:\\www.ava.co.uk
Blog
http://creamteadiet.blogspot.com/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 09:00:58

OK -- so I created two procs:


create procedure testtemp
as
set nocount on;

create table #temp (a int);

insert into #temp (a) values
	(1),(2),(3);

select a from #temp;
go

create proc testtable
as
set nocount on;

declare @temp table (a int);

insert into @temp (a) values
	(1),(2),(3);

select a from @temp;

Then, using LINQPad (I was in a hurry), I successfully executed both procs. The results were identical, as expected.

Can you post an example that fails?

Exgliderpilot
Starting Member

14 Posts

Posted - 2015-01-12 : 09:43:41

Ok so if you drag both procedures into a aspnet MSLinqToSQLGenerator, the testtemp will generate a wrapper and explicit objects the testtable will not. (upto net 4.0)

Staff bank agency scheduling software http:\\www.ava.co.uk
Blog
http://creamteadiet.blogspot.com/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 10:26:04

OK -- did that. The only difference is that Linq could not determine the datatypes when using testtable. Then I found that this is official, but there is a simple workaround:

Linq to SQL does not support code generation for stored procedures that produce results based on temporary tables. See the LINQ to SQL Limitations on Stored Procedures section in http://msdn.microsoft.com/en-us/library/bb425822.aspx for more details. One way to get round this is to temporarily change your stored procedure to use normal tables instead of temporary tables, generate the Linq to Sql code in Visual Studio and then change your stored procedure back to use temporary tables. Delete any unwanted tables created in the temporary version of the stored procedure.

http://stackoverflow.com/questions/5630757/why-stored-procedure-result-is-zero

So, you can write your stored proc using table variables, use Linq to Sql to build the object wrappers, then change the proc to use a temp table.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 10:27:23

...you might also try wrapping the call to the proc in another proc and use the WITH RESULT SETS option:

http://sqlmag.com/blog/sql-server-2012-t-sql-glance-execute-result-sets

Haven't tried this myself, but it looks promising.

Edit: just tried it, doesn't work!

I added:


create proc testtempresultset
as
set nocount on;

exec testtemp
with result sets (( a int));

but when I dragged this onto the design surface, I got the same error

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-12 : 12:22:22

Be sure to key the table variable or temp table as appropriate. For example:
declare @temp table (a int, unique clustered (a));

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources