March 29, 2012 at 5:06 pm
Hi and Thanks in advance,
What I need to do is parse a column, over multiple records but not sure how to do it without a cursor.
The column is [TroubleShoot Activity] and is a text field, the variable is @TA.
Once the field is parsed I have a stored procedure to put the data in a table, the SP works fine, just not sure how to get the data from a query without a cursor.
Here is the cursor code I have written
declare @ID varchar(10)
declare @Region varchar(50)
declare @Site varchar(50)
declare @TA varchar(5000)
truncate table FRACAS_Ticket_vs_Event
declare MyCursor cursor fast_forward for
select [Incident ID], Region, SiteReal, convert(varchar(4000),[TroubleShoot Activity])
from [O&M Active Month]
where [Troubleshoot Activity] like '%0x047e060d%'
and [analyzed date] >= '2012-3-1'
open MyCursor
fetch next from MyCursor
into @ID, @Region, @Site, @TA
while @@fetch_status = 0
begin
--so how do I get variables into the stored procedure without using a cursor??
exec dbo.FRACAS_TicketVsEvent @ID, @Region, @Site,@TA
fetch next from myCursor into @ID, @Region, @Site, @TA
end
close MyCursor
deallocate MyCursor
go
Any help would be greatly appreciated.
March 29, 2012 at 5:17 pm
depends on what the SP FRACAS_TicketVsEvent does
March 29, 2012 at 5:22 pm
Like I mentioned, it just parses the column/variable @TA and stores the data in another table.
Here is the logic for the SP.
procedure FRACAS_TicketVsEvent
(
@ID varchar(50),
@Region varchar(100),
@Site varchar(100),
@ta varchar(8000)
)
as
declare @test-2 varchar(8000)
declare @retval varchar(100)
declare @eventId varchar(10)
declare @alarmTime varchar(35)
set @test-2 = @ta
while (CharIndex('(event', @test-2) > 0)
begin
set @eventId = substring(@test, charindex('0x047e060d', @test-2),10)
set @alarmTime = substring(@test, charindex('(event [0x047e060d]) ALARM TIME', @test-2)+35 ,21)
insert into FRACAS_Ticket_vs_Event(ID, Region, Site, eventID, alarmtime)
values(@ID, @Region, @Site, @eventId, @alarmTime)
set @test-2 = substring(@test, charindex('(event [0x047e060d]) ALARM TIME', @test-2)+59, len(@test))
end
Thanks,
March 29, 2012 at 7:48 pm
I have some suggestions but they're pretty involved:
1. First you'll need a good split string function (http://www.sqlservercentral.com/articles/Tally+Table/72993/) like the one from Jeff Moden in the link.
2. Modify your stored procedure to avoid the loop by generating the rows you need for the INSERT using the split string (on delimiter '(event [0x047e060d]) ALARM TIME'). Then you can parse out the individual components on the rows of the table generated by the split string.
3. Assuming you're on SQL Server 2008, pass all the columns into the SP in a TABLE variable, thus avoiding the need for a loop around the EXEC SP statement.
If you post some sample data I could probably set this up for you.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 10:00 pm
jeffrey.farr (3/29/2012)
Like I mentioned, it just parses the column/variable @TA and stores the data in another table.
Please post an example of what @TA would contain. If it's large, attach it as a txt file. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2012 at 10:02 pm
dwain.c (3/29/2012)
3. Assuming you're on SQL Server 2008, pass all the columns into the SP in a TABLE variable, thus avoiding the need for a loop around the EXEC SP statement.
Hmmmm... I recently heard tell that passing data through table variables is a bit slow. Guess I'm finally going to have to setup a table variable and pass it to a stored procedure to find out.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 3:33 am
I recently heard tell that passing data through table variables is a bit slow. Guess I'm finally going to have to setup a table variable and pass it to a stored procedure to find out
Actually I'm working on a test harness now to compare how passing it through XML vs. a table variable ends up performance-wise.
Actually, in retrospect I think the entire problem (both loops) can be done with a single SQL statement and no need to call the SP. I visualized this solution after I posted the recommendation and may first try to accomplish that for the OPs case.
Assuming we ever get the test data and I find the time to get back to it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 30, 2012 at 6:24 am
I have a problem with using XML for such a thing simply because there's a comparatively large BYTE overhead thanks to all of the embedded tags. Of course, then you have to shred it. All of that can certainly be done in a relatively expeditious manner but I believe that passing the information as a TSV has it's advantages, as well. It's a lot more lightweight as to the BYTE overhead and a TSV splitter (like DelimitedSplit8K) is faster than what most people come with as an XML shredder.
Of course, I also have a problem with passing huge parameters that resemble tables. A lot of times, it's simply not necessary. I solved a problem fairly recently where a company was passing a quarter million rows to SQL Server and they tried many different methods all to no avail because it was pretty tough on the "pipe" especially if more than one person ran the same code. It turned out that the necessary data could defined by 3 short parameters and the cube product needed to be stored. Of course, SQL Server is much faster (about 1/4 second, in this case) at building cube products rather than trying to load a single parameter containing a quarter million rows.
Such simple solutions aren't always available but, as the folks at that company can attest, a lot of folks don't even think of such a thing. Too front end centric in that case and their DBA didn't even think of using cross joins to gen the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 6:25 am
Jeff Moden (3/29/2012)
jeffrey.farr (3/29/2012)
Like I mentioned, it just parses the column/variable @TA and stores the data in another table.Please post an example of what @TA would contain. If it's large, attach it as a txt file. Thanks.
There are several easy and high performance methods to do this. We just need to see what the contents of @TA look like before we can recommend the correct one.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 6:34 am
Here is a subset of the test data that would/could be in a row for 2TA:
<> 03/14/2012 12:24:37 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 19:24:27 - Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/14/2012 07:24:27 PM MODEL NAME: operator2 ALARM ID: 28866569
<> 03/14/2012 13:04:54 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:03:22 - Poller Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/14/2012 08:03:22 PM MODEL NAME: operator2 ALARM ID: 28969790
<> 03/14/2012 13:24:44 KB_SYMPTOM: CLEAR CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:03:22 - Poller Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/14/2012 08:24:13 PM MODEL NAME: operator2 ALARM ID: 28969790
<> 03/14/2012 13:39:53 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:39:12 - Poller Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/14/2012 08:39:12 PM MODEL NAME: operator2 ALARM ID: 29066879
<> 03/14/2012 14:09:28 KB_ACTION: RTVscan is running extremely high.
<> 03/14/2012 14:10:21 KB_ACTION: Restarted machine since it was locked up. Cleared logs. RTVscan is running really high. Advised operator to allow it to run and call us back in an hour if the issue persists.
<> 03/14/2012 14:10:58 KB_ACTION: FSE is schedualed to go on site Friday. He will take a WS with him then unless he needs to be dispatched earlier.
<> 03/14/2012 14:55:05 KB_SYMPTOM: CLEAR CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:39:12 - Poller Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/14/2012 09:53:33 PM MODEL NAME: operator2 ALARM ID: 29066879
<> 03/14/2012 15:33:47 KB_ACTION: FSR en route to Sector to possibly replace W/S #2. Will call when onsite.
<> 03/14/2012 15:34:58 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 22:32:32 - Poller Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/14/2012 10:32:32 PM MODEL NAME: operator2 ALARM ID: 29347848
<> 03/14/2012 17:32:50 KB_SYMPTOM: CLEAR CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 22:32:32 - Poller Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/15/2012 12:30:08 AM MODEL NAME: operator2 ALARM ID: 29347848
<> 03/14/2012 17:42:53 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Thu 15 Mar, 2012 00:42:22 - Poller Service at 'gateway' loss communication with 'operator2'. (event [0x047e060d]) ALARM TIME: 03/15/2012 12:42:22 AM MODEL NAME: operator2 ALARM ID: 29667051
March 30, 2012 at 6:43 am
Ok... thanks. How would you like to have it parsed?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2012 at 7:42 am
Since each of the '<>' is an event, that would be ideal.
Like my post indicated I just need the event and the Alarm Time.
And thank you for your assistance.
March 30, 2012 at 8:59 am
Erland has done deep analysis on this topic. From Arrays and Lists in SQL Server 2008: Using Table-Valued Parameters > Performance_Considerations (underlines added by me)
As you have understood from the fact that I devoted an article solely to table-valued parameters, this is the preferred method for passing a list of values. One important reason is simplicity: writing a stored procedure that accepts a table-valued parameter is straight-forward. Not that using a list-to-table function is a big deal, but using a table is the normal way to work in a relational database. And as you have seen, passing a value to a TVP from ADO .NET is a simple affair. TVPs also have the advantage that you can add constraints to the table type to disallow duplicates, or enforce some other type of contract. Not talking of the simple fact that with a comma-separated list, format errors can give you nasty surprises that just cannot happen with table parameters.
Does this also mean that this the method gives you the best performance? In general, yes, but in each and every case? No. When running the tests for my performance appendix, I did find situations where other methods outperformed TVPs. However, I believe that in the long run TVPs will you give you better performance than any other method.
If playing your hand blind, I would recommend TVPs. That said, testing on your hardware with your data and workload is the only way to know which method will perform best for your use-case. The article is a good read, and the links to other related articles in the beginning of the doc show you the amount of effort, over the course of years, the author has spent researching and developing these views.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 1, 2012 at 11:16 am
jeffrey.farr (3/30/2012)
Since each of the '<>' is an event, that would be ideal.Like my post indicated I just need the event and the Alarm Time.
And thank you for your assistance.
Sorry, lost track of this thread. What about rows that don't have an alarm time?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2012 at 6:57 pm
As I alluded to in my response above to Jeff, there is no reason to have either loop (see the INSERT at the end):
DECLARE @FRACAS TABLE
(ID varchar(50), Region varchar(100), Site varchar(100)
,TA VARCHAR(MAX))
DECLARE @FRACAS2 TABLE
(ID VARCHAR(50), Region VARCHAR(100), Site VARCHAR(100)
,eventID VARCHAR(10), alarmtime VARCHAR(35))
DECLARE @delim CHAR(2)
SET @delim = '<>'
INSERT INTO @FRACAS
SELECT '1234' AS ID, 'BCDEF' AS Region, 'XYZ' AS Site,
'<> 03/14/2012 12:24:37 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 19:24:27 - Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/14/2012 07:24:27 PM MODEL NAME: operator2 ALARM ID: 28866569' +
'<> 03/14/2012 13:04:54 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:03:22 - Poller Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/14/2012 08:03:22 PM MODEL NAME: operator2 ALARM ID: 28969790' +
'<> 03/14/2012 13:24:44 KB_SYMPTOM: CLEAR CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:03:22 - Poller Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/14/2012 08:24:13 PM MODEL NAME: operator2 ALARM ID: 28969790' +
'<> 03/14/2012 13:39:53 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:39:12 - Poller Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/14/2012 08:39:12 PM MODEL NAME: operator2 ALARM ID: 29066879 ' +
'<> 03/14/2012 14:09:28 KB_ACTION: RTVscan is running extremely high. ' +
'<> 03/14/2012 14:10:21 KB_ACTION: Restarted machine since it was locked up. Cleared logs. RTVscan is running really high. Advised operator to allow it to run and call us back in an hour if the issue persists.' +
'<> 03/14/2012 14:10:58 KB_ACTION: FSE is schedualed to go on site Friday. He will take a WS with him then unless he needs to be dispatched earlier.' +
'<> 03/14/2012 14:55:05 KB_SYMPTOM: CLEAR CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 20:39:12 - Poller Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/14/2012 09:53:33 PM MODEL NAME: operator2 ALARM ID: 29066879 ' +
'<> 03/14/2012 15:33:47 KB_ACTION: FSR en route to Sector to possibly replace W/S #2. Will call when onsite.' +
'<> 03/14/2012 15:34:58 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 22:32:32 - Poller Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/14/2012 10:32:32 PM MODEL NAME: operator2 ALARM ID: 29347848 ' +
'<> 03/14/2012 17:32:50 KB_SYMPTOM: CLEAR CRITICAL ALARM EVENT MESSAGE: Wed 14 Mar, 2012 22:32:32 - Poller Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/15/2012 12:30:08 AM MODEL NAME: operator2 ALARM ID: 29347848' +
'<> 03/14/2012 17:42:53 KB_SYMPTOM: CRITICAL ALARM EVENT MESSAGE: Thu 15 Mar, 2012 00:42:22 - Poller Service at ''gateway'' loss communication with ''operator2''. (event [0x047e060d]) ALARM TIME: 03/15/2012 12:42:22 AM MODEL NAME: operator2 ALARM ID: 29667051'
AS TA
INSERT INTO @FRACAS2 (ID, Region, Site, eventID, alarmtime)
OUTPUT INSERTED.*
SELECT ID, Region, Site
,substring(strcol, charindex('0x047e060d', strcol),10) AS EventID
,substring(strcol, charindex('(event [0x047e060d]) ALARM TIME', strcol)+33 ,22) AS AlarmTime
FROM @FRACAS
CROSS APPLY (SELECT strcol FROM SplitString(@delim, SUBSTRING(TA, 3, LEN(TA)))) ss
WHERE CHARINDEX('(event', strcol) > 0
The above uses a SplitString function that looks like the one below (you should use Jeff's from the link I posted, I'm sure its better but I was in a hurry). You may remove the OUTPUT statement as it is solely there to illustrate the records that were inserted into the @FRACAS2 table.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[SplitString] (@delimVARCHAR(10), @str VARCHAR(MAX))
RETURNS @list TABLE (strcolVARCHAR(MAX))
AS
BEGIN
WITH SS(start_char, end_char) AS (
SELECT start_char = 1, end_char = CAST(CHARINDEX(@delim, @str + @delim) AS INT)
UNION ALL
SELECT start_char = end_char + LEN(@delim), end_char = CAST(CHARINDEX(@delim, @str + @delim, end_char + LEN(@delim)) AS INT)
FROM SS
WHERE CHARINDEX(@delim ,@str + @delim, end_char + LEN(@delim)) <> 0 )
INSERT INTO @list (strcol)
SELECT SUBSTRING(@str, start_char, end_char - start_char) AS strcol
FROM SS
RETURN
END
Results:
IDRegionSiteeventIDalarmtime
1234BCDEFXYZ0x047e060d03/14/2012 07:24:27 PM
1234BCDEFXYZ0x047e060d03/14/2012 08:03:22 PM
1234BCDEFXYZ0x047e060d03/14/2012 08:24:13 PM
1234BCDEFXYZ0x047e060d03/14/2012 08:39:12 PM
1234BCDEFXYZ0x047e060d03/14/2012 09:53:33 PM
1234BCDEFXYZ0x047e060d03/14/2012 10:32:32 PM
1234BCDEFXYZ0x047e060d03/15/2012 12:30:08 AM
1234BCDEFXYZ0x047e060d03/15/2012 12:42:22 AM
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply