Execute multiple statements at once...

Execute multiple statements at once...

• mom2blondies

  It sounds like you're just trying to concatenate all of your rows in the child table into a single row in a third table. If so, the following scenario may help.

  http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

  You'll likely not get much parallelism in looping code that you desribed so if you can find a set-based method of processing then you'll be much better off.

  
  

• intrepid

  My apologies - I forgot you're on SQL Server 2000. The XML method of processing won't work in this situation. You still want a process where you can build the comma-separated list in a SET operation rather than one row at a time.

  There's a method whereby you can assign a value to a variable in one row and use it in subsequent rows of an UPDATE statement. AFAIK, it's undocumented but it works in SQL 2005 as well so you're safe for a little while.

  The pseudo-code would look something like this:

  -- Place all of the data into a work table

  CREATE TABLE #WorkTable (ID_NUM char(10), data varchar(6000), importance smallint, IdentCol INT IDENTITY, PRIMARY KEY(ID_NUM, IdentCol)

  INSERT INTO #WorkTable (ID_NUM, data, importance)

  SELECT ID_Num, data, Importance

  FROM SourceTable

  ORDER BY ID_NUM, Importance

  -- Update work table. Each row has the data of itself + the row preceding it when there is a matching ID_NUM

  DECLARE @ID_NUM CHAR(10)

  DECLARE @data VARCHAR(6000)

  UPDATE #WorkTable

  SET @data = data = CASE WHEN ID_NUM <> @ID_NUM THEN data

  ELSE @data + data

  END

  ,@ID_NUM = ID_NUM

  INSERT INTO FinalTable(ID_NUM, Data)

  SELECT ID_NUM, MAX(DATA)

  FROM #WorkTable

  GROUP BY ID_NUM

  In case the pseudo-code doesn't work, here's a working sample for Adventureworks database:

  CREATE TABLE #JobTitles (job_id INT, job_desc VARCHAR(100), employees VARCHAR(1000), EmployeeName VARCHAR(100), PRIMARY KEY (job_id,

  EmployeeName))

  DECLARE @job_id INT, @EmployeeNames VARCHAR(1000)

  INSERT INTO #JobTitles(job_id, job_desc, EmployeeName) SELECT J.job_id, J.job_desc, E.lname + ', ' + E.fname AS EmployeeName FROM jobs J INNER JOIN employee E ON J.job_id = E.job_id

  SET @job_id = -1 -- Initialize this to a number that does not exist

  -- in the list of values.

  UPDATE #JobTitles

  SET @EmployeeNames = employees = CASE

  WHEN @job_id <> job_id THEN EmployeeName

  ELSE @EmployeeNames + '; ' + EmployeeName

  END

  , @job_id = job_id

  -- Grab the largest row of each grouping.

  SELECT job_id, job_desc, MAX(employees) AS EmployeeList FROM #JobTitles GROUP BY job_id, job_desc ORDER BY job_id

  DROP TABLE #JobTitles

  
  

• Paul Verrone

  I will give that method a try...

  I have been trying all kinds or things including DTS with multiple connections

  all have been unsuccessful in speedy results... my last attempt took about 10 hours

  
  

• Larrybird02744

  Yes that is what I mean...

  sorry... working on a million things a once and don't always proof read..

  
  

• Asday

  Great you solved it.

  Would you tell me which method you used (I know that not the one I said:). I'm asking because I'm not SQL server oriented and for the last few days seriously diving into SQL server. Before I was using it "superficially". Though I was impressed with T-SQL in 2000, 2005 made me an addict:)

  
  

• Battlekiller

  THAT WORKED GREAT!!!!!

  My job now completes in 1 min 43 sec.... HUGE improvement...

  Thanks.

  
  

• TGirgenti

  It is all within a single tsql SP.... It is extremely slow and I can't seem to get it to use more processor or memory...

  I am thinking using a DTS package and working with multiple temp tables. Then combining all the temp tables in the end... it should be quicker that way and I might be able to push my server (which is enterprise with 12GB of ram and 2 processors)...

  current schema is :

  Final Table = ID_NUM (Key, char(10)), data (varchar(6000))

  Working table is ID_NUM, date varchar(100), importance (smallint) - key is ID_NUM and DATA

  Working temp table data varchar(100), importance smallint -- no key, always less than 200 rows.
  Plus one variable @data that is a varchar(6000)

  Loop 1

  Grab an ID_NUM

  get list of data for ID_NUM into working table

  Loop 2

  Loop through till all rows have been added together or it goes over 6000 characters

  @data = Data + data + data

  end loop 2

  insert ID_NUM, @DATA into final table

  end loop 1

  That is what I am going... no cursor, just while loops...

  
  

• Larrybird02744

  William:

  Here is yet another example. I mocked this up with 2.1 million rows. I was able to get this to run in about 3 minutes and 20 seconds. This problem is definitely easier to solve with 2005 than 2000 and there has to be a better way of doing this, but this example might also help you see some direction; I hope it is helpful.

  Dave

  I used these two tables and faked the data:

  create table dbo.final
  ( [key] char (10) not null,
  [data] varchar (6000) not null
  )

  create table workingTable
  ( ID_Num varchar (10) not null,
  [data] varchar (100) not null,
  importance smallint not null,

  constraint pk_mockInput primary key (ID_Num, [data])
  )

  
  insert into workingTable
  select convert (varchar (5), a.iter),
  '{ ' + convert (varchar (5), b.iter) + ' }'
  + replicate ('-', 1 + b.iter % 29 ) + '----------------->',
  b.iter
  from small_iterator a (nolock)
  inner join small_iterator b (nolock)
  on a.iter <= 21300
  and b.iter <= 40 + a.iter % 119
  and b.iter <= 200
  order by a.iter,
  b.iter

  update statistics workingTable
  exec sp_recompile workingTable

  I would prefer not to use the function because there is a fair amount of overhead associated with functions; nonetheless, I implemented the method using the following function:

  alter function dbo.assembleData
  ( @arg_key varchar (10),
  @arg_minData varchar (100),
  @arg_maxData varchar (100)
  )
  returns varchar (6000)
  as

  begin

  declare @retValue varchar (6000)
  set @retValue = ''

  select @retValue = @retValue + [data]
  from workingTable
  where [id_num] = @arg_key
  and [data] >= @arg_minData
  and [data] <= @arg_maxData

  return ( @retValue )

  end

  I then tested out the overall procedure with this query:

  --truncate table dbo.final

  -- The whole thing takes about 3 minutes and 20 seconds
  create table #sequencer
  ( [key] varchar (10) not null,
  [data] varchar (100) not null,
  importance smallint not null,
  seq integer not null,
  siz integer not null,

  constraint pk_#sequencer primary key ([key], [data])
  )

  insert into #sequencer
  select a.id_num,
  a.[data],
  a.importance,
  count(*) as seq,
  sum (datalength (b.[data])) as siz
  from workingTable a
  inner join workingTable b
  on a.id_num = b.id_num
  and b.[data] <= a.[data]
  group by a.id_num, a.[data], a.importance
  order by a.id_num, a.[data]

  insert into dbo.final
  select [key],
  dbo.assembleData ( [key], min_data, max_data )
  from ( select [key],
  min ([data]) as min_data,
  max ([data]) as max_data,
  min (seq) as min_seq,
  max (seq) as max_seq,
  case when siz = 0 then 0
  else siz - 1
  end / 6000 as segment
  from #sequencer
  group by [key],
  case when siz = 0 then 0
  else siz - 1
  end / 6000
  ) x

  drop table #sequencer

  select top 20 *
  from dbo.final
  

  
  

• bt8114

  You could open multiple connections from the client, but something tells me that there may be a better way to process your data.

  Could you post the relevent schema and code

  
  

• Markish

  AFAIK within SQL server 2000 you do not have such a chance. On a single CPU, I doubt the case would be different under 2005 (but not an MSSQL expert).

  However "24 hours" and even "hours" for 2.1 million rows processing to create a 204K data sounded to be very slow to me. Maybe if you select the data outside of SQL server with an isolation like uncommitted, prepare outside and do a single bulk load, it'd be much faster.

  How do you do this All in a single T-SQL batch

  
  

• Godspeed 2.0

  William:

  Where you say, "... date varchar (100), ... " for your "Working table" do you really mean "... data varchar (100) ... "

  Dave

  
  

• Bob Pokorny

  I used the solution posted by Jared Ko
  

• &amp;#91;MSP&amp;#93;Daniel Oliveira

  That actually looks good... the problem will be hitting the varchar limit.

  I will have to see if I hit it or not...

  What fun... lol...

  Thanks for all the help.

  
  
• Execute multiple statements at once...