October 9, 2003 at 8:49 am
Ok bear with me you experts out there. I need to know which method is faster and why. I have a table for all intents and purposes can be considered a history table. At half hour intervals my VB program is required to save current data and subsequently return the most recent data (records from the previous half hour) from this table. Because this table grows exponentially it will become very large. Would it be best (faster) to use a view on this (indexed) table to return the most recent data or create an intermediary table with the exact structure and save data to both intermediary table and history table and return data from the intermediary table. I would appreciate any other useful methods, however I would still like to know which of the above retrieval methods is best.
ThanX for the help in advance
October 9, 2003 at 3:01 pm
If you're "saving" the local data every half-hour, don't you already have it locally and therefore do not need to "return" it from the server?
I wouldn't use a view or an intermediate table, but instead just a stored procedure. Your index will need to be on the datetime column, so be careful not to use a function on the column value in the predicate.
--Jonathan
--Jonathan
October 10, 2003 at 2:31 am
If you are talking about a huge amount of data in your history table, the additional table will give you a performance edge.
The view won't help a lot, I would think.
Obviously, adding the logic to keep a second table up-to-date isn't all that easy. You might run into other issues.
October 10, 2003 at 7:47 am
Assuming you have the "datetime inserted" for each row, why not do one of these:
A) Stored procedure, pass in the data to be loaded, but before inserting do a
SELECT <data>
from <table>
where <Datetimecolumn> =
(select max(<Datetimecolumn>)
from <table>)
and once that's read, insert the data? With the index, that will be about as fast as it gets.
B) If you have to insert the data before reading the data, it merely gets messy:
SELECT <data>
from <table>
where <Datetimecolumn> =
(select max(<Datetimecolumn>)
from <table>
where <Datetimecolumn> <
(select max(<Datetimecolumn>)
from <table>
)
)
This could probably be written more cleanly, particularly if you "know" the datetime value you just inserted.
Philip
October 10, 2003 at 7:50 am
Oops, forgot to add to the assumption the fact that you create an index on the datetime created column. (Sounds like that's the primary key, anyway.)
Philip
October 10, 2003 at 9:16 am
Jonathan, I have already considered the option of not going to the server for the data. However ever so often, new records are added to the original data source at scheduled times. As a result, a view was created to return both current and new records to the client.
But your idea of using a stored procedure (SP) has lead me to alternative idea. Have the SP do the following: create the new records (in lieu of the view), simultaneously insert these records into the history table, insert same records into a temp table. The SP then selects the records from the #temp and returns it to the client. At the end of the half hour the client updates the relative records of the history table. (I am using a temp table in order to avoid selecting from the huge history table.) Thanks again guys.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply