Any way to avoid a loop here? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Any way to avoid a loop here?

Author

Topic

matty1stop
Starting Member

37 Posts

Posted - 2007-04-24 : 16:13:02

I've got a 76 million record table. The data is taken from a DB2 database so I have two fields that represent a timestamp to the microsecond (e.g. 2007-04-24-15:56:78.123456)

Here is a simplified version of the table we'll call Transactions
TransactionTime char(26)
HistoryTime char(26)
HistoryType char(1)
updatedHistoryType char(1)

There can be several records with the same TransactionTime but the combination of TransactionTime and HistoryTime would be the Primary Key.
I need to update the updatedHistoryType field based on the HistoryType field and its chronological order of the HistoryTime and the TransactionTime. So currently I am ordering the table in my query like this
order by left(transactiontime, 11) asc,
right(transactiontime, 15) asc,
left(historytime, 11) asc, right(historytime, 15) asc

Once I have the resultset I loop through updating the table as I go.

I am actually using java to run through the resultset but the performance is very poor.

Would performing the loop in sql improve performance? If so any suggestions as to the best way of doing this?

Thanks for reading and let me know if I've left out any information you need to answer this. I've been working on it for a while and my head is not hurting.

Thanks,

Matt

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-24 : 16:16:59

moving the logic into the backend *might* help, to start with. post some sample data. Also, why are all columns char?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-24 : 16:23:58

using proper data types (i.e, dateTime) and indexes will greatly help your sql performance. right now you basically have a flat file stored in SQL Server, which is a complete waste of its abilities as a relational database.

- Jeff
http://weblogs.sqlteam.com/JeffS

matty1stop
Starting Member

37 Posts

Posted - 2007-04-24 : 16:38:52

The time columns are char because they hold a timestamp from a db2 table that carries a precision greater than sql datetime can handle. (e.g. 2007-04-24-15:56:78.123456)
Would you suggest a different datatype for any of the collumns?

In the sample below the first 2 records the updatedhistorytype field would be the same as the historytype field. The subsequent records have the same transaction time and represent different history records for one transaction. Only the first P and Q Touchtype are retained while any P or Q touches after the first are converted to W.

HistoryTime TransactionTime HistoryType UpdatedHistoryType
2007-03-05 14:46:56.466072 2006-10-16 15:30:58.853840 C C
2007-03-05 13:48:27.537680 2006-11-02 12:17:14.673952 C C
2007-03-05 09:00:20.481688 2006-11-27 12:10:46.379728 C C
2007-03-05 09:04:46.387672 2006-11-27 12:10:46.379728 W W
2007-03-05 09:05:04.901696 2006-11-27 12:10:46.379728 W W
2007-03-05 10:00:24.667552 2006-11-27 12:10:46.379728 P P
2007-03-05 10:05:08.942760 2006-11-27 12:10:46.379728 P W
2007-03-05 11:00:26.752688 2006-11-27 12:10:46.379728 Q Q
2007-03-05 11:01:59.115160 2006-11-27 12:10:46.379728 Q W

matty1stop
Starting Member

37 Posts

Posted - 2007-04-24 : 16:43:12

I wish I could use a datetime data type but sql server does not carry precision to the microsecond.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 19:27:13

Is this what you want ?


declare @table table
(
	HistoryTime 		char(26),
	TransactionTime 	char(26),
	HistoryType 		char(1),
	UpdatedHistoryType	char(1)
)
insert into @table
select	'2007-03-05 14:46:56.466072', '2006-10-16 15:30:58.853840', 'C', NULL	union all
select	'2007-03-05 13:48:27.537680', '2006-11-02 12:17:14.673952', 'C', NULL	union all
select	'2007-03-05 09:00:20.481688', '2006-11-27 12:10:46.379728', 'C', NULL	union all
select	'2007-03-05 09:04:46.387672', '2006-11-27 12:10:46.379728', 'W', NULL	union all
select	'2007-03-05 09:05:04.901696', '2006-11-27 12:10:46.379728', 'W', NULL	union all
select	'2007-03-05 10:00:24.667552', '2006-11-27 12:10:46.379728', 'P', NULL	union all
select	'2007-03-05 10:05:08.942760', '2006-11-27 12:10:46.379728', 'P', NULL	union all
select	'2007-03-05 11:00:26.752688', '2006-11-27 12:10:46.379728', 'Q', NULL	union all
select	'2007-03-05 11:01:59.115160', '2006-11-27 12:10:46.379728', 'Q', NULL

update	t
set	UpdatedHistoryType = case when t.HistoryType = 'C' then t.HistoryType
				  when t.HistoryType in ('P', 'Q') 
				  and  n.HistoryTime is not null then t.HistoryType
				  else 'W'
				  end
from	@table t
left join
	(
		select	HistoryTime = min(HistoryTime),
			TransactionTime
		from	@table
		group by TransactionTime
	) m
	on	t.HistoryTime		= m.HistoryTime
	and	t.TransactionTime	= m.TransactionTime
left join
	(
		select	HistoryTime = min(HistoryTime),
			TransactionTime, HistoryType
		from	@table
		where	HistoryType	in ('P', 'Q')
		group by TransactionTime, HistoryType
	) n
	on	t.HistoryTime		= n.HistoryTime
	and	t.TransactionTime	= n.TransactionTime

select	*
from	@table

/*
HistoryTime                TransactionTime            HistoryType UpdatedHistoryType 
-------------------------- -------------------------- ----------- ------------------ 
2007-03-05 14:46:56.466072 2006-10-16 15:30:58.853840 C           C
2007-03-05 13:48:27.537680 2006-11-02 12:17:14.673952 C           C
2007-03-05 09:00:20.481688 2006-11-27 12:10:46.379728 C           C
2007-03-05 09:04:46.387672 2006-11-27 12:10:46.379728 W           W
2007-03-05 09:05:04.901696 2006-11-27 12:10:46.379728 W           W
2007-03-05 10:00:24.667552 2006-11-27 12:10:46.379728 P           P
2007-03-05 10:05:08.942760 2006-11-27 12:10:46.379728 P           W
2007-03-05 11:00:26.752688 2006-11-27 12:10:46.379728 Q           Q
2007-03-05 11:01:59.115160 2006-11-27 12:10:46.379728 Q           W
*/

matty1stop
Starting Member

37 Posts

Posted - 2007-04-25 : 09:03:02

khtan, thanks for the tip. The actual logic is a little more complicated than the example that I laid out but I think I will be able to tweak your solution to fit my needs.

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources