"Brian Higgins" <brian@-NO-accentconsulting-SPAM-.com> wrote in message
news:Oz51rSfHFHA.2976@TK2MSFTNGP15.phx.gbl...
>I have a large and ever growing database with over 15 million records, all
>timestamped, and increasing by roughly 30,000 records per day so efficient
>queries are essential.
>
> I need to pull a list of records that match certain criteria, that have
> timestamps in the last hour. and I need to run it 45 times in a row, and I
> need the results in the shortest amount of time as possible.
>
> the only thing I need at this point in the program is a boolean value
> telling me that either something has been logged recently, or it has not.
>
> the basic logic of the program is currently as follows:
>

"Brian Higgins" <brian@-NO-accentconsulting-SPAM-.com> wrote in message
news:Oz51rSfHFHA.2976@TK2MSFTNGP15.phx.gbl...
>I have a large and ever growing database with over 15 million records, all
>timestamped, and increasing by roughly 30,000 records per day so efficient
>queries are essential.
>
> I need to pull a list of records that match certain criteria, that have
> timestamps in the last hour. and I need to run it 45 times in a row, and I
> need the results in the shortest amount of time as possible.
>
> the only thing I need at this point in the program is a boolean value
> telling me that either something has been logged recently, or it has not.
>
> the basic logic of the program is currently as follows:
>
> ---------------------------------------------------------------------------
> For ServerID = 1 to 45
> open Recordset
> SELECT ServerInfo.ServerID
> FROM ServerInfo INNER JOIN (PInfo INNER JOIN PDetails ON
> PInfo.ID = PDetails.ID) ON ServerInfo.ID = PInfo.ID
> WHERE (((PDetails.Status)<>2 And (PDetails.Status)<>5) AND
> ((DateDiff(hh,[TmeStamp],now()))<1))
> GROUP BY ServerInfo.ServerID
> HAVING (((ServerInfo.ServerID)=%SERVERID 1 thru 45%));
>
> If Recordset.EOF = False Then
> 'good
> DoNothing = True
> Else
> 'Bad
> DoNothing = False
> End If
>
> Close Recordset
>
> Next ServerID
> ---------------------------------------------------------------------------
>
> as you can see this is a very very very in-efficient way of doing things
> (loop takes over 5 min to run this way, I want it to complete in less then
> 5 seconds)
> in this manor the query is searching through tens of millions of fields
> each time it runs
>
>
> This is how I would like the program logic to flow:
> ---------------------------------------------------------------------------
> Open Recordset1
> SELECT PID
> FROM PDetails
> WHERE (PStatus <> 2) AND (PStatus <> 5) AND (DATEDIFF(hh, TmeStamp,
> { fn NOW() }) < 1)
> GROUP BY PDetails.PID;
>
> If Recordset1.EOF = False Then
>
> For ServerID = 1 to 45
> open Recordset2 Select * From Recordset1 Where PID = 1 OR PID = 2
> or PID = 3
>
> If Recordset2.EOF = False Then
> 'Good
> DoNothing = True
> Else
> 'Bad
> DoNothing = False
> End If
>
> Close Recordset2
>
> Next ServerID
>
> Else
> 'stop there because nothing has happened in the last hour
> End If
>
> Close Recordset1
> ---------------------------------------------------------------------------
>
> In this manor the large brunt of the records are only processed when
> opening Recordset1
> and the subsequent 45 queries performed by recordset2 are only going to
> parse through a few records at most, if any at all
>
> all of this is called from within (vbscript) ASP, what is the best way to
> achieve the desired end result (fast page load, near realtime results),
> with the least ammount of processing time / bandwidth required (the web
> server only has a 100Mb connection to the SQL server, and the database is
> nearly 1Gb...)?
>
>
> Thanks in Advance...
>

>
> "Brian Higgins" <brian@-NO-accentconsulting-SPAM-.com> wrote in message
> news:Oz51rSfHFHA.2976@TK2MSFTNGP15.phx.gbl...
>>I have a large and ever growing database with over 15 million records, all
>>timestamped, and increasing by roughly 30,000 records per day so efficient
>>queries are essential.
>>
>> I need to pull a list of records that match certain criteria, that have
>> timestamps in the last hour. and I need to run it 45 times in a row, and
>> I need the results in the shortest amount of time as possible.
>>
>> the only thing I need at this point in the program is a boolean value
>> telling me that either something has been logged recently, or it has not.
>>
>> the basic logic of the program is currently as follows:
>>
>
> You don't need a stored procedure, you just need to change the SQL. You
> need to end up with a query of the form
>
> select * from t where DateCol > '2005-03-01 16:00:00'
>
> Yes, you want to hard-code the date in the query. This is one of the
> exceptions to the rule of using parameter markers for your queries. This
> will give SQL Server the maximum amount of statistical information about
> how to process your query.
>
> First, ditch the query builder, and write the SQL by hand. All those
> useless parentheses just confuse the issue:
>
> Also ditch the HAVING clause and return all the ServerID's then you can
> iterate them on the client side and process all 45 servers with one query.
> Just ORDER BY ServerId so you know that if the first row has ServerID=2
> then there was no row with ServerID=1, etc.
>
> SELECT ServerInfo.ServerID
> FROM ServerInfo
> INNER JOIN PInfo
> ON ServerInfo.ID = PInfo.ID
> INNER JOIN PDetails
> ON PInfo.ID = PDetails.ID
> WHERE PDetails.Status<>2
> And PDetails.Status)<>5
> AND TmeStamp >= '2005-03-01 16:00:00'
> GROUP BY ServerInfo.ServerID
> ORDER BY ServerInfo.ServerID
>
> With any kind of index on TmeStamp this query should be reasonably cheap.
> With a clustered index on TmeStamp it should be downright fast.
>
> David
>
>