Database Design Advice

Hello all,
I am looking for some advice on the design of a database for a
particular application I am tasked with. The database is part of an
equipment monitoring application that records numerous equipment data
readings and stores them to a database. The readings are taken every
few seconds and are stored for performance analysis and trending.
Needless to say the data table can get very large over time.
There is a web component that will allow users to save custom filtered
views of the data on particular types of data or specific date ranges.
I want to avoid dynamic SQL at all costs, so I was thinking of
generating a new SQL Server View for each user's custom view.
Here is the tough part - I need to have a summary page of all the user
defined views that shows the number of data points in each view. I
don't know how to do this without using dynamic SQL. For example, if
users create View1, View2, View3, and View4 - how can I have a single
stored proc that returns the count of each of them? The only thing I
can think of is having a cursor that goes through a "UserViews" table
and for each View, runs a dynamic SQL query like SELECT Count(DataID)
from "viewname", on each view. That seems like it would be horribly
inefficient.
Is there a better way getting this summary data? I hope I have
explained my problem well enough. Your advice is greatly appreciated."Yofnik" <yofnik@.comcast.net> wrote in message
news:1127420944.523072.102240@.f14g2000cwb.googlegroups.com...
> Hello all,
> I am looking for some advice on the design of a database for a
> particular application I am tasked with. The database is part of an
> equipment monitoring application that records numerous equipment data
> readings and stores them to a database. The readings are taken every
> few seconds and are stored for performance analysis and trending.
> Needless to say the data table can get very large over time.
> There is a web component that will allow users to save custom filtered
> views of the data on particular types of data or specific date ranges.
> I want to avoid dynamic SQL at all costs,
You shouldn't. There's really no such thing as dynamic SQL. All SQL is
treated the same by the server. It's compiled, optimized and cached. If
the same SQL is issued again, the cached plan is used.
See Generally
http://www.sommarskog.se/dynamic_sql.html
And you should understand how the query processor works.
Microsoft SQL Server Query Processor Internals and Architecture
http://msdn.microsoft.com/library/d... />
erproc.asp
Especially when you need to hard-code parameter values and when you need to
use parameter markers.

>so I was thinking of
> generating a new SQL Server View for each user's custom view.
>
Don't do that. Just create a table to store the SQL of the custom queries.
It's user data, not metadata.

> Here is the tough part - I need to have a summary page of all the user
> defined views that shows the number of data points in each view. I
> don't know how to do this without using dynamic SQL. For example, if
> users create View1, View2, View3, and View4 - how can I have a single
> stored proc that returns the count of each of them? The only thing I
> can think of is having a cursor that goes through a "UserViews" table
> and for each View, runs a dynamic SQL query like SELECT Count(DataID)
> from "viewname", on each view. That seems like it would be horribly
> inefficient.
>
Yes it would, but how else would you know how many rows a query returns. If
you must have this functionality, then you could run the registered queries
periodically and update a Rows column in the queries table.
David|||David,
Thanks for your reply. So, if I understand you correctly, you are
saying that in this case dynamic SQL is the right choice? If I store
the SQL and dynamically run it, won't it get recomiled each time? Then
if I want to do the summary, will each one be compiled every time the
summary is requested?
Thanks again|||"Yofnik" <yofnik@.comcast.net> wrote in message
news:1127423579.533176.322160@.g44g2000cwa.googlegroups.com...
> David,
> Thanks for your reply. So, if I understand you correctly, you are
> saying that in this case dynamic SQL is the right choice? If I store
> the SQL and dynamically run it, won't it get recomiled each time?
No. It won't. This is one of the most common misconceptions about dynamic
SQL. The details are in the link about query processor internals.

>Then
> if I want to do the summary, will each one be compiled every time the
> summary is requested?
You probably don't want to run every query each time the summary is
requested. Either drop that feature, or run
'select count(*) from (' + sql + ') dt'
for each stored query once a day or something.
David