Re: would a Stored Procedure help

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: David Browne (meat_at_hotmail.com)
Date: 03/01/05 

Date: Mon, 28 Feb 2005 19:44:42 -0600

"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

Relevant Pages

  • Re: Non-existent A record being returned...
    ... ; Query 1 ... These DNS servers are set up to forward queries to two other DNS ... Nslookup has it's own resolver service, and doesn't rely or use Windows resolver service, or the local cache, but rather directly queries DNS, where I'm assuming you're referring to clearing the DNS server cache? ...
    (microsoft.public.windows.server.dns)
  • Re: MailMerge hangs and crashes with Access on Server
    ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... I have mapped out the path to the final query that is used to get to the letters - and have found that it is quite tortuous indeed - utilizes 8 queries and multiple tables to get there, does about 20 calculations along the way. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Do I understand form optimization correctly?
    ... queries on an Access database. ... Access is file based, not server based. ... If you run any query on the table, the entire table is brought down to ...
    (comp.databases.ms-access)
  • Re: Basic design question for a distributed application - How to access applications data
    ... The main data the application manipulates is a list of Queries. ... query is a custom object containing an SQL statement and some ... My server is where my list of queries will reside. ... My client will display that list of queries and enable the user to ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Passing values
    ... Use the comma as the separator list (excerpt if you have set the Windows' ... menu for the Queries Window. ... You can also use a view and set the Server Filter but that's another story. ... > figure out how to pass a value from a form to the query. ...
    (microsoft.public.access.adp.sqlserver)