December 12, 2011 at 4:12 pm
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
December 12, 2011 at 4:29 pm
The major difference in this scenario is the missing statistic information. If you look at the actual execution plan, you'll se a big difference between estimated number of rows (=1 for @table_variable) and actual number of rows.
This might result in a very different execution plan.
You might want to have a look at this article[/url] including the referenced articles. Very informative.
December 12, 2011 at 5:36 pm
You may also want to look at this:
December 12, 2011 at 9:41 pm
thanks . I am trying to tune this one query, unfortunately i wont be able to use #temp tables because this is a function, though just the query itself with the #tables works pretty good. I tried few other option with different joins no help.I will try to post the query, in the mean while if anyone has better ideas please let me know.
December 13, 2011 at 1:10 am
We'd need to see the function itself together with DDL of all tables involved to make any suggestion. It sounds like a scalar function anyway. So, the next question would be: how is it used (what does the query look like that's using the function?)? Maybe a stored procedure is the way to go here...
December 13, 2011 at 1:47 pm
Converting function to sproc is not a viable solution , too many changes need to be done.
After reading this article , i have added "option (recompile)" to batch of sql which was running slow and it worked like a charm.Now the problem is, all this time i was testing the sql with in the function, when i do a "select * from my_functionname" it seems it is not recompiling. How can i recompile a function?
December 13, 2011 at 2:11 pm
your temp table could be materialized as a CTE, and used for further processing...that is allowed in a function.
show us your function, and we can probably help.
Lowell
December 13, 2011 at 2:25 pm
sqldba_newbie (12/13/2011)
Now the problem is, all this time i was testing the sql with in the function, when i do a "select * from my_functionname" it seems it is not recompiling.
How are you seeing that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2011 at 4:06 pm
Lowell (12/13/2011)
your temp table could be materialized as a CTE, and used for further processing...that is allowed in a function.show us your function, and we can probably help.
I have attached the function with the changes. Now when i remove the sql portion outside the function( that is also attached) it runs very quickly, but when i run select * from function it takes much much longer. Only the bottom piece (select distinct PRID from @CHS) was taking most time so adding hint helped however doesn't do anything good while used in a function..
December 13, 2011 at 5:56 pm
I may be wrong as I can't see your data, but it looks like this portion of your code always returns one row:
If @RId is not null and @ADt is not null and @PTp is not null
Begin
INSERT INTO @RPL(ReId,ADT, PTT, CDTB)
SELECT @RId,@ADt, @PTp, @CDT
End
Else
Begin
INSERT INTO @RPL(ReId,ADT, PTT, CDTB)
SELECT ReId,ADT, PTT, @CDT
FROM dbo.fn_CA_APL(@RKey)
End
If this is correct couldn't you try replacing the inner joins with a WHERE clause here?
FROM @RPL p
INNER JOIN RXF xref
ON p.ReId = xref.PRID
INNER JOIN RGTN r
ON r.Id= xref.ReId
INNER JOIN Chsc
ON xref.ReId = c.ReId
LEFT OUTER JOIN vwCDB_CA cdmb
ON cdmb.CIC = c.CIC
AND r.ADT BETWEEN cdmb.StartDate AND cdmb.EndDate
AND cdmb.CDT= @CDT
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
December 13, 2011 at 8:38 pm
Leo.Miller (12/13/2011)
I may be wrong as I can't see your data, but it looks like this portion of your code always returns one row:
If @RId is not null and @ADt is not null and @PTp is not nullBegin
INSERT INTO @RPL(ReId,ADT, PTT, CDTB)
SELECT @RId,@ADt, @PTp, @CDT
End
Else
Begin
INSERT INTO @RPL(ReId,ADT, PTT, CDTB)
SELECT ReId,ADT, PTT, @CDT
FROM dbo.fn_CA_APL(@RKey)
End
If this is correct couldn't you try replacing the inner joins with a WHERE clause here?
FROM @RPL pINNER JOIN RXF xref
ON p.ReId = xref.PRID
INNER JOIN RGTN r
ON r.Id= xref.ReId
INNER JOIN Chsc
ON xref.ReId = c.ReId
LEFT OUTER JOIN vwCDB_CA cdmb
ON cdmb.CIC = c.CIC
AND r.ADT BETWEEN cdmb.StartDate AND cdmb.EndDate
AND cdmb.CDT= @CDT
Cheers
Leo
May be, but that portion runs perfectly fine. Issue is really not there, i am sure there is better way to write that code but the real issue what i am working on is in the last batch of sql as i have mentioned in my previous post
December 14, 2011 at 12:10 am
sqldba_newbie (12/13/2011)
May be, but that portion runs perfectly fine. Issue is really not there, i am sure there is better way to write that code but the real issue what i am working on is in the last batch of sql as i have mentioned in my previous post
I would fundemenatally and strongly disagree with that.
You have posted about a performance problem, right ?
When talking about performance, you cannot take a single SQL statement in isolation.
The much much larger gains come from reassesing your entire routine, it may be a trickier route . but it will be better is the long run.
December 14, 2011 at 9:23 am
Dave Ballantyne (12/14/2011)
sqldba_newbie (12/13/2011)
May be, but that portion runs perfectly fine. Issue is really not there, i am sure there is better way to write that code but the real issue what i am working on is in the last batch of sql as i have mentioned in my previous post
I would fundemenatally and strongly disagree with that.
You have posted about a performance problem, right ?
When talking about performance, you cannot take a single SQL statement in isolation.
The much much larger gains come from reassesing your entire routine, it may be a trickier route . but it will be better is the long run.
sure makes sense
December 14, 2011 at 1:27 pm
sqldba_newbie (12/12/2011)
I see huge performance difference on joining a #temp_table instead of @table_variable. Query runs way faster when i join on a #temp_table. Any idea what could make it so slow when i use @table_variable? I know i should provide more information but just wanted to know if there is any difference.
When a stored procedure references a table variable, SQL Server will attempt to keep a fixed execution plan, and to do this it will assume that the table variable contains a small record count. That's one reason why Microsoft provided a table variable as an alternative to temp tables, so it can be used in scenarios where it is considered beneficial to keep a fixed plan. However, a fixed plan doesn't always result in the best runtime performance, like when dealing with large resultsets.
Look at the exection plan of the table variable query versus the temp table query and see what they are doing differently. For example, are they using different join operators? You can attempt to compensate for this by using a query hints to force a certain JOIN operator (LOOP | MERGE | HASH), if you know one consistently performs better than the default that SQL Server wants to use with that table variable.
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
declare @mytable table ( order_id not null primary key);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 14, 2011 at 3:39 pm
Also, it is commonly believed that one can't create an index on a table variable, however, this can be achieved by declaring a primary key like in the example below. Try declaring a primary key on the column(s) used in the table joins.
The problem is the fact the OP's posted does a SELECT DISTINCT implies that there are non-unique valuse in the PRID column, and a primary key on a table variable must be unique. To allow for this her would need to create a PK over multiple columns, ensuring uniqueness, with the PRID column the left most column. This would probably improve performance on the join.
I wouldn't have coded the last section with a select from and join to the same table.
INSERT INTO @CHS(RType,ReId,PRID,ADT,PTT,CTT)
select 'IPL' as RType, r.Id as ReId, r.Id as PRID, r.ADT, r.PTT,
STUFF((SELECT '|' + ...
FROM @CHS c2
WHERE c2.PRID = r.Id FOR XML PATH('') ),1,1,'') AS CTT
from RGTN r
where r.Id in (select distinct PRID from @CHS)
option(recompile)
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply