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 |
|
jansen
Starting Member
5 Posts |
 Posted - 2002-09-26 : 04:46:58
|
| Hello all !I have a stored proc that is doing some calculation. In this SP there is some temporary tables and some variable tables because I need to do some array like operations.The problem is, that both of theese table types uses tempdb, which requires some harddisk operations. I can see in a profiler window that 2 million reads is commited in the tempdb when the calculation is finished, and because of that my Xeon is only working 25% because it is waiting on the harddisk.Does anyone know how to keep theese tables in memory only? and, perhaps, without transaction logging.Would it be possible to make tempdb secondary filegroup on a ramdisk to get the speed?Best regardsJansen |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-26 : 08:03:06
|
quote:
Does anyone know how to keep theese tables in memory only? and, perhaps, without transaction logging.
If you have SQL Server 2000 you can use table variables instead. However, with the volume of rows you seem to have, they may use up memory and require SQL Server to page RAM to disk. Still faster, but maybe not as much.quote:
Would it be possible to make tempdb secondary filegroup on a ramdisk to get the speed?
It would be a poor use of RAM. SQL Server would perform better if that RAM was available for cache instead of being used as a disk file. If you increase the size of tembdb you might improve performance.Can you post some code, and your table structures, and some more detail on what you're trying to do? There might be another way to accomplish it without using your resources so heavily. |
 |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-09-26 : 08:19:10
|
| By ramdisk, do you mean a solid state accelerator? If so, then yes obviously you would get a great performance increase, but these kinds of devices are super super expensive and hard to maintain.*************************Someone done told you wrong! |
|
|
|
jansen
Starting Member
5 Posts |
Posted - 2002-09-28 : 16:31:45
|
| I am using variable tables, but even theese are using tempdb, and I have to scan them in each loop.Here is the case.A table holds halfhour values:dtSampled,relValue'2002-1-1 00:00' , 0.123'2002-1-1 00:30' , 0.321'2002-1-1 01:00' , 0.213'2002-1-1 01:30' , 0.132.....and so on.A select must be made that (worst case one year) will return:timestamp, relValue, rel48HourAverage, rel95PctFractilerel48HourAverage is the running average 96 rows back... that's pretty easy. But the 95% fractile means that you have to query the table from the the current row and back to 1 jan. 00:00 ( must always go back to start of calendar year.Doing selects for each row can solve this but that will take about one hour to make this result set ( one year is 17520 halfhour values), so that doesn't work. I solved it by looping through each row, by a cursor, and storing the last 96 values (48 hours) in a variable table and in each loop selecting the AVG() of this table. The fractile problem is solved by keeping the higest 876 values ( in another variable table) through the loop. I then know, for each loop count, at what position the 95% fractile is, i.e. at loop 100 the 95% fractile is at position 5 among the highest values so far.To make things even woorse, there is actualy 3 relValues witch all have to have a 48 hour average and a running fractile (95%, 96% and 97%) so that is totaly: one variable table for 48 hour average and 3 variable tables for the fractiles. All of theese have to be updated and queryied each loop.And that is why I get so may queryes to the tempdb.Since Your replyes, I have made an COM object instead of theese variable tables. Two classes, one for 48 hour average and one for fractiles. Theese classes uses arrays and is therefore not logged to tempdb, but using COM in t-sql (sp_OAMethod...) I don't think is very stabile/solid.robvolk: I welcome any other way to make this query.royv: solid state accelerator....? I mean a ramdisk as a piece of ram declared as a device at boot. Take a look at: http://dslnuts.com/ramdisk.shtml Best regardsJansenEdited by - jansen on 09/28/2002 16:45:13 |
|
|
|
|
|
|
|
|
Temporary tables and variable tables