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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-11 : 08:50:56
|
| Robert Wafle writes "OK, I have lots of questions, and I really need a mentor to help me learn. I am a sponge and I really want to grow. Do I need to move to Seattle and paint on my belly will program SQL for food?? Or will writing sqlteam.com allow me to grow my skills?What is the most scalable approach to this problem? I want to return data fast, and I don't think I want to use the TEMPDB, because I am truly READING data and don't need to create extra disk I/O in the form of WRITES? no? Alright, I create a pivot table.. and I add columns to it using what is called a "derived query".. which is supposed to have much less write I/O than selecting into a temp table according to articles I've read.. so...e.g.SELECT a.u,a.v,a.w,a.x,a.y,a.z, a.x-a.y as c1, a.u-a.v as c2INTO #tmpSummaryFROM (SELECT u,v,w,x,y,z FROM tbWhatever WHERE <blah> ) as au,v,w,x,y,z are actually more complicated a PIVOT.. u,v,w are values from one row of the same table, and x,y,z are from the other row.. c1 and c2 are calculated values, or changes between the data sets. I put the data into #tmpSummary then run this query which summarizes the data. select (select count (*) from #tmpSummary) as total_shares_held_holders, (select sum (currentshares) from #tmpSummary) as total_shares_held_shares, (select count (*) from #tmpSummary where currentshares > 0 ) as new_positions_holders, (select sum (currentshares) from #tmpSummary where currentshares > 0 ) as new_positions_shares, (select count (*) from #tmpSummary where currentshares > lastshares ) as increased_positions_holders, (select sum (currentshares) from #tmpSummary where currentshares > lastshares) as increased_positions_shares, (select count (*) from #tmpSummary where currentshares < lastshares ) as decreased_positions_holders, (select sum (currentshares) from #tmpSummary where currentshares > lastshares) as decreased_positions_shares, (select count (*) from #tmpSummary where currentshares = 0 and lastshares > 0 ) as soldout_positions_holders, (select sum (currentshares) from #tmpSummary where currentshares = 0 and lastshares > 0 ) as soldout_positions_sharesOK, I noticed something kinda silly .. the query executes very fast, but why am I using "currentshares > lastshares" when I could just say "chgShares > 0".. but little stuff like this doesn't really matter to me.. It won't change anything.. My main goal is to READ data from the DB without WRITING to TEMPDB, because I don't want to deal with the TEMPDB becoming a bottleneck.. and I don't want to cause loads of WRITE I/O when I am READING my data.. ..actually I just want to enable the absolute highest amount of users on my server with the least amount of DISK IO.. Can TEMPDB safely run in RAM? I saw some articles about that.. wouldn’t I then just start swapping when I run out of RAM? anyways...When I am finished building the table I am querying will have aprox. 4.8 Million rows and take up about a 1GB of disk space, and probably require more space for indexes..(so my where clauses are effective, of ourse, of course!!) Is it time to define an OLAP cube? Can I define an OLAP cube to calculate the above? Should I start looking for angel investors so I can afford a disk subsystem dedicated to the TEMPDB? Can I just run lots of derived Queries? Should I use TEMPDB because I have a large result set? Could I use uncommitted reads for historical data that will not change? SQL server 2000 has indexed or materialized views.. can those help in some way? Can I horizontally partition data across disks? By Year? By Quarter?I guess I'll have to setup a test environment to test all these different methods of querying the database. How do I effectively measure the Disk I/O and CPU, and Memory and keep track of these things?Can I just dynamically create/execute say one very long query (hopefully less t |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-14 : 03:35:26
|
| Hi Robert,Cant really suggest anything . but it will be better if you post your table schema with some sample data and expected results. Team Members might be able to work out a optimized query together.--------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
 |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-02-14 : 08:47:59
|
| Robert,This is equivalent to the code in the middle of your post. I think you will find that you can hook this code up directly to you tables(or a derived table) without need for a temp table.select count (*) AS total_shares_held_holders, sum (currentshares) as total_shares_held_shares, SUM(CASE WHEN currentshares > 0 THEN 1 ELSE 0 END ) as new_positions_holders, SUM(CASE WHEN currentshares > 0 THEN currentshares ELSE 0 END ) as new_positions_shares, SUM(CASE WHEN currentshares > lastshares THEN 1 ELSE 0 END ) as increased_positions_holders, SUM(CASE WHEN currentshares > lastshares THEN currentshares ELSE 0 END ) as increased_positions_shares, SUM(CASE WHEN currentshares < lastshares THEN 1 ELSE 0 END ) as decreased_positions_holders, SUM(CASE WHEN currentshares < lastshares THEN currentshares ELSE 0 END ) as decreased_positions_shares, SUM(CASE WHEN currentshares = 0 and lastshares > 0 THEN 1 ELSE 0 END ) as soldout_positions_holders, SUM(CASE WHEN currentshares = 0 and lastshares > 0 THEN currentshares ELSE 0 END ) as soldout_positions_shares, FROM #TempACtivityEdited by - toddv on 02/14/2002 08:48:12 |
|
|
|
|
|
|
|
|
Summarize my data without using TEMPDB?