Frequency Query

on a SQL 7.0 database is it possible to do a query to analyze frequency of the entries in the database?in other words I want to know which entries have the most instances.

Hi,

You can use the count method in combination with a group by clause.
For example: SELECT ProductName, COUNT(*) FROM Orders GROUP BY ProductName

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

thanks for your reply, the column name in the table is Tagname, would it be possible to count all entries for a specific time period? alarmlog is the table name, eventstamp is the time stamp of the entry.

like this:

select *, count(*)

from alarmlog

where eventstamp > '02/01/2007 00:00:00' and

eventstamp < '02/02/2007 00:00:00

thanks

Gary

|||

Yes but you should do it like this:

select TagName, count(*)
from alarmlog
where eventstamp > '02/01/2007 00:00:00' and eventstamp < '02/02/2007 00:00:00'
group by TagName

FYI: This is a good site with basic SQL syntax: http://www.w3schools.com/sql/default.asp

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

thanks again.

Gary

|||

Sorry one more quick question, for the results can I add another column to appear? there is a comment column for each entry that has the description in it that would be useful in the resluts.

Gary

|||

You can add as much columns as you want but the column must be included in the group by clause OR included in an aggregate function like (SUM, AVG, MIN, MAX, COUNT, ...)

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Frequency Of SnapShot Agent

How often should the snapshot agent job run with merge replication? My
schema won't be changing anytime soon but I will have new subscribers coming
on everyday until we get everyone on board.
Thanks
Tina
Hello Tina,
The snapshot agent is required in case there are schema changes, but not
only. For example, cleaning up meta data using procedure
sp_mergecleanupmetadata requires the snapshot agent to be run (more in BOL).
It also creates the latest bcp files so if you apply the snapshot to new
subscribers they start off with the latest data.
I also think having read somewhere (but I am not too sure about it) whereby
the automatic cleanup of merge tracking meta data (normally stored in
Msmerge_Contents and Msmerge_Tombstone tables) depends on periodically
running the Snapshot agent. If someone can shed some more light on this...
In your case you might want to run the agent once a day, because you would
want each subscriber to be updated using the latest snapshot data. However,
you MUST be conscious that during the running of the snapshot agent,
conflict tables ARE DROPPED and RECREATED (I am told by Microsoft Support
this is by design). So, if you have concerns with conflicts, make sure you
resolve them before running the snapshot agent.
Raj Moloye
|||Raj,
Thanks for all your input.
Tina
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:uCZy0DkHEHA.1220@.tk2msftngp13.phx.gbl...
> Hello Tina,
> The snapshot agent is required in case there are schema changes, but not
> only. For example, cleaning up meta data using procedure
> sp_mergecleanupmetadata requires the snapshot agent to be run (more in
BOL).
> It also creates the latest bcp files so if you apply the snapshot to new
> subscribers they start off with the latest data.
> I also think having read somewhere (but I am not too sure about it)
whereby
> the automatic cleanup of merge tracking meta data (normally stored in
> Msmerge_Contents and Msmerge_Tombstone tables) depends on periodically
> running the Snapshot agent. If someone can shed some more light on this...
> In your case you might want to run the agent once a day, because you would
> want each subscriber to be updated using the latest snapshot data.
However,
> you MUST be conscious that during the running of the snapshot agent,
> conflict tables ARE DROPPED and RECREATED (I am told by Microsoft Support
> this is by design). So, if you have concerns with conflicts, make sure you
> resolve them before running the snapshot agent.
> Raj Moloye
>

Frequency of joins between two tables in a database.

Hi All,
Is there anyway to tell the frequency of joins between two tables in a
database?. I need help on this one.
Thanks.
Message posted via http://www.webservertalk.comI don't know of any way to profile this, but what you could do
reasonably quickly is build up a quick structure mapping queries to the
tables they join, then run a profiler, log all the queries run into a
table, and thus be able to interrogate this. However that will assume
that your queries always join the same tables.|||Hi
Can you say what you need this for? If you are looking at de-normalisation
then you may want to base that on actual performance.
If you script your code you may get some idea of how often a table is joined
by using test searches.
John
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef609f8fb256@.uwe...
> Hi All,
> Is there anyway to tell the frequency of joins between two tables in a
> database?. I need help on this one.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||The reason to get this information is, I'm trying to split a large DB( 700gb
)
to group the join tables on one drive for performance purposes.
Thanks.
John Bell wrote:
>Hi
>Can you say what you need this for? If you are looking at de-normalisation
>then you may want to base that on actual performance.
>If you script your code you may get some idea of how often a table is joine
d
>by using test searches.
>John
>
Message posted via http://www.webservertalk.com|||I could be wrong, but won't it be better to have the joined tables on
different drives, so multiple IO paths can be used at the same time? Or are
we talking about moving certain data to faster drives and leaving other data
on slower drives?
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef800aece75b@.uwe...
> The reason to get this information is, I'm trying to split a large DB(
700gb)
> to group the join tables on one drive for performance purposes.
> Thanks.
> John Bell wrote:
de-normalisation
joined
> --
> Message posted via http://www.webservertalk.com|||Hi
You can look profile and look at high reads/writes as well as duration, that
will hopefully give some indication where you can split things up. You may
also want to split indexes onto different spindles, possibly looking at ITW
to show you some indication of what indexes are frequently used.
Also you can try creating multiple files on different drives that belong to
the same filegroup. The other option is to add extra discs to an existing
array so splitting I/O over more spindles.
John
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef800aece75b@.uwe...
> The reason to get this information is, I'm trying to split a large DB(
> 700gb)
> to group the join tables on one drive for performance purposes.
> Thanks.
> John Bell wrote:
> --
> Message posted via http://www.webservertalk.com|||And... don't forget move tempdb onto it's own spindles, possibly splitting
it into multiple files as well as moving the system databases into their own
discs.
John
"Naana via webservertalk.com" <u14055@.uwe> wrote in message
news:5ef800aece75b@.uwe...
> The reason to get this information is, I'm trying to split a large DB(
> 700gb)
> to group the join tables on one drive for performance purposes.
> Thanks.
> John Bell wrote:
> --
> Message posted via http://www.webservertalk.com|||Hi Jim
I had mis-read this... yes you would want to split I/O. I prefer a more
quantative approach i.e.target what is bad!
John
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uz60VkwYGHA.3684@.TK2MSFTNGP05.phx.gbl...
>I could be wrong, but won't it be better to have the joined tables on
> different drives, so multiple IO paths can be used at the same time? Or
> are
> we talking about moving certain data to faster drives and leaving other
> data
> on slower drives?
>
> "Naana via webservertalk.com" <u14055@.uwe> wrote in message
> news:5ef800aece75b@.uwe...
> 700gb)
> de-normalisation
> joined
>|||We are thinking of moving certain large tables that are frequently access to
faster drives and leaving other data on slower drives.
Jim Underwood wrote:
>I could be wrong, but won't it be better to have the joined tables on
>different drives, so multiple IO paths can be used at the same time? Or ar
e
>we talking about moving certain data to faster drives and leaving other dat
a
>on slower drives?
>
>[quoted text clipped - 17 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1