February 13, 2011 at 8:33 pm
Hi All,
In an unavoidable situation, I'm using ##Table in my stored procedure.
How will it behave in a concurrent environment? when many users access the same stored procedure will it delete the previous users table or will it delay the connection.
I'm expecting a detailed explanation- any blogs, White papers are highly appreciate.
Thanks,
Ami.
February 13, 2011 at 9:04 pm
Anamika (2/13/2011)
Hi All,In an unavoidable situation, I'm using ##Table in my stored procedure.
How will it behave in a concurrent environment? when many users access the same stored procedure will it delete the previous users table or will it delay the connection.
I'm expecting a detailed explanation- any blogs, White papers are highly appreciate.
Thanks,
Ami.
If it is created in the stored procedure, it will be dropped when that stored procedure exits, AND no statements in other sessions are actively using it. As soon as the statement(s) in the other sessions complete, then the table will be dropped.
Can you expand upon why you have to use a ##Table instead of a #Table?
This article might be useful to you:
Comparing Table Variables to Temporary Tables[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 13, 2011 at 9:57 pm
On the basis of the value is not null I'm adding those in to where conditions. while doing this i want to find the IDs from a comma separated values. for that I'm using fn_split
As I'm not able to use the function inside the dynamic query, I put it in a table. But to use a temporary table inside a dynamic query we have to go for ##tables I guess.
That's how I end up in ##Temp tables.
Thanks,
Regards,
Ami
February 13, 2011 at 10:02 pm
Anamika (2/13/2011)
On the basis of the value is not null I'm adding those in to where conditions. while doing this i want to find the IDs from a comma separated values. for that I'm using fn_splitAs I'm not able to use the function inside the dynamic query, I put it in a table. But to use a temporary table inside a dynamic query we have to go for ##tables I guess.
That's how I end up in ##Temp tables.
Thanks,
Regards,
Ami
There are workarounds to the problem you're facing, such as using fn_split to return a table for joining against instead of an IN clause.
Can you post the code to your proc and fn_split? We can probably help you find a way that will have no concurrency issues.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 13, 2011 at 11:46 pm
Anamika (2/13/2011)
On the basis of the value is not null I'm adding those in to where conditions. while doing this i want to find the IDs from a comma separated values. for that I'm using fn_splitAs I'm not able to use the function inside the dynamic query, I put it in a table. But to use a temporary table inside a dynamic query we have to go for ##tables I guess.
That's how I end up in ##Temp tables.
Thanks,
Regards,
Ami
I've run into fn_Split before and if you say the first two letters of its name real fast, it pretty much describes what I think of it (if it's the one I'm thinking of). 😉 My recommendation, at this point, would be to post your code including that of fn_Split so we can help you avoid the use of the Global Temp table and provide a replacement for fn_Split.
And, no... you don't have to use Global Temp Tables inside dynamic SQL. Just declare the table outside of the dynamic SQL and you'll be all set.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 12:10 am
Hi,
As I couldn't share the full code I've shared only the part where i've used the fn_split and the script for fn_split. If there are some more round abouts, I would like to implement.
In this method the records in the application are not refreshing immediately.
Thanks
Ami
February 14, 2011 at 12:18 am
Yeah... that's the function I was thinking it was. It's going to be really slow because of the While Loop. And, yeah, I know... you don't have a lot of rows so performance of the function may not be a concern of yours but it should be.
I'll be back in a couple of minutes.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 12:35 am
Ok... the following function will pretty much blow the doors off of other splitter functions including some of my old ones. I'll be using this new function in a modernization of my Tally Table article.
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Created by Jeff Moden
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4
)
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY t.N),
ItemValue = SUBSTRING(@pString,t.N+1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,t.N+1),0),DATALENGTH(@pString)+1)-t.N-1)
FROM cteTally t
WHERE t.N BETWEEN 0 AND DATALENGTH(@pString)
AND (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
;
Give me a few more minutes on the Temp Table thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 12:55 am
This should be proof enough that you [font="Arial Black"]can [/font]access a Local Temp Table in dynamic SQL...
DECLARE @ParamDefinition NVARCHAR(MAX)
--, @TmpDefinition NVARCHAR(MAX)
,@SQL VARCHAR(MAX)
,@GroupId VARCHAR(100)
SET @GroupId ='36,78,34,56'
IF OBJECT_ID('tempdb..#TblGroupID') IS NOT NULL
BEGIN
DROP TABLE #TblGroupID
END
CREATE TABLE #TblGroupID (Id INT)
INSERT INTO #TblGroupID
SELECT CONVERT(INT, ItemValue) AS groupid FROM dbo.DelimitedSplit8K(@GroupId, ',')
SELECT @sql = 'SELECT * FROM #TblGroupID'
EXEC (@SQL)
Just change your code to use the new function and forget about using Global Temp Tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 3:35 am
Thanks,
It is working fine. I wonder on what scenario I've gone for Global Temporary tables. 🙁
Thanks,
Ami
February 14, 2011 at 6:42 am
Anamika (2/14/2011)
Thanks,It is working fine. I wonder on what scenario I've gone for Global Temporary tables. 🙁
Thanks,
Ami
It happens to the best of us. 😀 Thanks for the feedback, Ami.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 1:46 pm
Jeff Moden (2/14/2011)
Ok... the following function will pretty much blow the doors off of other splitter functions including some of my old ones.
WOW!!! That crash you just heard was the sound of the doors being blown off the splitter functions we currently use. This is going into production TODAY! Thanks, Jeff!
I'm looking forward to an updated tally table article - it took me a while to grasp the workings of this function, but I'm very curious about how I could use tally table logic in other ways, too.
Jason
Jason Wolfkill
February 14, 2011 at 3:58 pm
wolfkillj (2/14/2011)
Jeff Moden (2/14/2011)
Ok... the following function will pretty much blow the doors off of other splitter functions including some of my old ones.WOW!!! That crash you just heard was the sound of the doors being blown off the splitter functions we currently use. This is going into production TODAY! Thanks, Jeff!
I'm looking forward to an updated tally table article - it took me a while to grasp the workings of this function, but I'm very curious about how I could use tally table logic in other ways, too.
Jason
Very cool... "immediate implementation" is the best compliment anyone in our line of work could hope for. Thanks for taking the time to post the feedback, Jason.
As a side bar, the reason for the speed of the function is primarily because there is no concatenization of delimiters to the original string.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 4:02 pm
Jeff Moden (2/14/2011)
wolfkillj (2/14/2011)
Jeff Moden (2/14/2011)
Ok... the following function will pretty much blow the doors off of other splitter functions including some of my old ones.WOW!!! That crash you just heard was the sound of the doors being blown off the splitter functions we currently use. This is going into production TODAY! Thanks, Jeff!
I'm looking forward to an updated tally table article - it took me a while to grasp the workings of this function, but I'm very curious about how I could use tally table logic in other ways, too.
Jason
Very cool... "immediate implementation" is the best compliment anyone in our line of work could hope for. Thanks for taking the time to post the feedback, Jason.
As a side bar, the reason for the speed of the function is primarily because there is no concatenization of delimiters to the original string.
Jeff, I noticed you're using DATALENGTH() in this function. While the original parameter is VARCHAR(), I'd wondered if you've already tried this with nVARCHAR and what your findings were?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 14, 2011 at 4:21 pm
The reason for DATALENGTH in the function posted on this thread is so that you can use a space as a delimiter. It's only important if you have trailing delimiters with nothing inbetween but I wanted to make sure it was handled.
The code also works fine (with some modification for DATALENGTH()/2) and fast for NVARCHAR(4000). I've only started on testing against any of the MAX datatypes. Typically, just using one of the MAX datatypes in a JOIN, as is usually done with splitters of this nature, immediately causes the code to run twice as slow allowing even (ugh!) recursive CTE's to outstripe joined splitters. However, this runs so bloody fast now (thanks to there being NO concatenation), I'm hoping it'll keep up with a rock solid well written While loop on the MAX datatypes. I just haven't had the time to do that testing, yet.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply