I am not even sure if this is possible but I want to be able to excute several statements (or SPs) at once from inside a SPs (or any other method).
What I am doing is a I am taking data from a single column, multiple rows and making it into one row (i.e. data1 + data2 + data3....) But I am doing this to a total of 2.1 million individual rows and the result will be about 204k rows.... what I have written is basically a nested loop and it works fine but very slow... slow as in it has been running for 24 hours now and it is about 60% done... I need this to finish in under 36 hours preferably...
If I could handle more than one set of data at once (there will be no duplicates) I could speed up the process by how ever many I feel like working with...
So is there a way to execute a statement (sp or function) and go to the next statement without waiting for the first to finish?
Thanks Big Time... I hope this is possible.
OH.... SQL 2000 SP4 on Windows 2003.
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?
|||
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?
|||
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...
|||
William:
Where you say, "... date varchar (100), ... " for your "Working table" do you really mean "... data varchar (100) ... "?
Dave
|||
Yes that is what I mean...
sorry... working on a million things a once and don't always proof read..
|||
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.
|||
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.
|||
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
|||
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
|||
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 
|||
THAT WORKED GREAT!!!!!
My job now completes in 1 min 43 sec.... HUGE improvement...
Thanks.
|||
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:)
|||I used the solution posted by
Jared Ko
