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 |
|
mrgr8avill
Starting Member
16 Posts |
 Posted - 2007-07-11 : 22:48:04
|
| Hello -- thank you for taking the time to read this.I have a very large table that is used both for archives and new information. To get the current information, the table is queried by many different users at various polling periods. The SELECT required includes about fifteen JOINS, and only returns about 200 rows at any given time.So I got to thinking if it might be faster to periodically run the big query as a SELECT INTO into a smaller table and letting the polling clients query the smaller table with SELECT *. Periodically, the smaller table would be DROPPED and refereshed with another SELECT INTO.Trouble is, the data would have to be updated once every 30 seconds, and there are inbound polls coming at the rate of about 200 per minute. It got me to thinking what might happen if a client attemtped to query the smaller table when it was in the process of being dropped and refilled.So my question is three-part:1) assuming a larger table of about 500,000 records and only 500 pertinent at any given time, is there any real potential of performance enhancement by switching to a SELECT INTO table?2) if so, is there a chance of a client failing a query if the inbound query somehow collides with the DROP/SELECT INTO procedure?3) if so, is there any way to prevent it or a better way of doing this?Thanks again for reading, and in advance for any help you can provide. I apologize if I sound like a dummy - it's hard to fake intelligence! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-11 : 23:31:57
|
| Its prbly more efficient to just populate the table and let clients query against it rather than drop it every 30 sec. You can truncate the table once every night. When the clients query the table use a datetime column or some such column that you can group by each set of 200 rows. So each client would query the latest 200 rows (order by datetime column desc). Make sure there is an index on the column.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
mrgr8avill
Starting Member
16 Posts |
Posted - 2007-07-11 : 23:52:36
|
| Thanks, Dinakar -- I appreciate your respponse.Would it be worth it in that case to even bother with the smaller table instead of running a stored procedure against the larger table and indexing the date column on which it would be sorted?Thank you,Shannon |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-12 : 00:07:22
|
| If you dont have the smaller table, you'd have to be using the big SELECT with 15 joins right? I thought you run a job to popupate a smaller table with the big SELECT and let clients query the smaller table? And the datecolumn I was talking about will be on the smaller table. So the datetime value changes for every 200 recors.. or did I not understand you correctly?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
|
|
|
|
DROP TABLE, SELECT INTO, Performance