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.
| Author |
Topic |
|
JeffT
Posting Yak Master
111 Posts |
 Posted - 2006-03-30 : 10:05:20
|
| Hi,In a stored procedure I'm processing, via a cursor, a table of, potentially, 100,000 rows on a daily basis. The only column in each row is a 12-byte transaction control number. I know that using cursors can cause performance issues. Is there an alternative to using a cursor that has less of a performance impact ? Thanks,Jeff |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-30 : 10:07:09
|
What is the stored procedure doing ?
KHChoice is an illusion, created between those with power, and those without.Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant |
 |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-03-30 : 10:50:58
|
| The SP creates 18 temporary tables, extracting the data for those tables from 18 much larger tables, based on a match on the transaction control number, to get a smaller subset to query. The 18 temporary tables are then queried using the tcn cursor, with an average of 2 to 10 columns being extracted from each temp table. The extracted data is then written to another table which is, after some more massaging, forwarded to the mainframe computer. Critiques of this process are more than welcome. I will clarify further if I'm not giving you enough info. Thanks,Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-03-30 : 11:44:14
|
| Thanks very much ! I'll check this out and see if I can modify mine to mimic the example.Jeff |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-03-31 : 10:19:53
|
| As long as the result of 1 part of the current process doesn't affect the result of a later executed process then there shouldn't be a great) problem moving to a SET-BASED solution....ie the order in which the main transactions is processed should not influence the result later on. |
|
|
|
|
|
|
|
|
Cursor alternative ?