Table variable causing slow performance on query?

Question

0.00/5 (No votes)

See more:

Hi, I have a stored procedure in which i will generate records for users. But recently users feedback that the records generation are slow and we have decided to fine tuning the stored procedure itself. In my stored procedure, i have some SELECT statements in which i will join permanent table with table valued function(TVF) and my DBA feedback we can further improve the performance simply by replacing the TVF with table variable. Please see below for example of before and after changes:

SQL

SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c
	INNER JOIN dbo.utf_CustomerDetailInfoStatus('ACTIVE') AS cdi
		ON c.CustomerStatusId = cdi.CustomerStatusId


SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c
	INNER JOIN @CustomerDetailInfoStatus AS cdi
		ON c.CustomerStatusId = cdi.CustomerStatusId

The above example is just a simple mock up to show the changes. There are 5 TVF that I have replaced with table variables but after some testing, i found the performance has deteriorate further from around 20 seconds to 6 minutes of execution duration(both returned 10k of records)! I change the table variables back into TVF and it returned to around 20 seconds. But my DBA insists that by replacing the TVF with table variables, other factors such as table locking, I/O spiking can be reduced but through testing from my end, the result is not what i have been expecting.
Does anyone has knowledge on this?

What I have tried:

1. Done some study on TVF vs Table Variable but none of the topics is somewhere related to my case here.

Posted 19-Sep-18 17:06pm

Jamie888

Updated 19-Sep-18 18:16pm

Add a Solution

Comments

Santosh kumar Pithani 20-Sep-18 0:01am

i think TVF is causing performance issue so try to put TVF code for review else apply proper index on filter of TVF code.

Richard Deeming 20-Sep-18 9:23am

The SQL query optimizer tends to assume that a table variable only contains a single row. If you've got lots of rows in the table variable, you might be better off using a temporary table instead.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Answer 1 · 2018-09-19T18:16:00

SQL

--if you don't have proper index on Customer,TVF i'm help less.

CREATE INDEX Customer_index(CustomerStatusId) INCLUDE(CustomerName,CustomerAddress,CustomerOrderNo);

SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c 
	  CROSS APPLY dbo.utf_CustomerDetailInfoStatus('ACTIVE') AS cdi
		WHERE
             c.CustomerStatusId = cdi.CustomerStatusId OPTION(FAST 10);

 --try like this too

SELECT
	 c.CustomerName
	,c.CustomerAddress
	,c.CustomerOrderNo
FROM
	dbo.Customer AS c   WHERE EXISTS(
  SELECT 1 FROM  dbo.utf_CustomerDetailInfoStatus('ACTIVE') AS cdi
		WHERE
                   c.CustomerStatusId = cdi.CustomerStatusId 
                                         );