Delete + Log file

A) When i run a delete against a table that has 50 million records based
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql

Degree of separation search

I have an application that lets users search based on degree of separation,
so for instance, a user can search for age, hobbies, etc. and limit the
search to just users who are 1 degree separate, 2 degrees separate, or 3
degrees separate.
However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this is
taking way over one minute to execute, causing timeouts in the browser.
Is there a better way to go about doing this type of search? Perhaps using
a scheduler to perform some calculations beforehand so the searches can use
it? Any feedback will be greatly appreciated.
Shabam
Read this article
http://www.sommarskog.se/dyn-search.html
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:_aCdnc14X62v2bnfRVn-iw@.adelphia.com...
> I have an application that lets users search based on degree of
separation,
> so for instance, a user can search for age, hobbies, etc. and limit the
> search to just users who are 1 degree separate, 2 degrees separate, or 3
> degrees separate.
> However as it stands now, searches are taking a long long time because
> according to the programmer, the degree of separation is calculated
> dynamically upon search, and with a system of about 50,000 users this is
> taking way over one minute to execute, causing timeouts in the browser.
> Is there a better way to go about doing this type of search? Perhaps
using
> a scheduler to perform some calculations beforehand so the searches can
use
> it? Any feedback will be greatly appreciated.
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?
|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Thanks for the article link. However the main problem with this search is
> the degree of separation search, not the other search criterias. It
> appears
> the application is going through calculating the degree of separation of
> each user, then taking the acceptable ones and doing a search on them.
> The
> degree of separation is stored in a function, and thus is being called
> hundres, perhaps thousands of times per search. This is why it's taking
> 1+
> minute to do a search. Do you have any ideas/suggestions on how to do
> this
> right?
>
There's not much concrete advice we can give without table DDL, sample data
and an explanation of the expected results.
David
|||Basically, this is a spacial problem. What I find most often is that the
developer wants to take the parameters as dynamic, then calculate a
"distance" function between the user with respect to the remaining users
based on the chosen metrics. That is time consuming and expensive.
However, coordinates in "space" are fixed, relativistic effects aside. So,
they are not dynamic and everyone's "position" is known for all metrics.
The problem is that many metrics have differing scales, but we will ignore
that for the moment. So, from beginning geometry, we have for each user,
there position is the set of coordinates, with respect to the origin:
User A: (x1, x2, ..., xn)
User B: (y1, y2, ..., yn)
Their "distance" from the origin is just the Pythagorean Theorem: a^2 + b^2
= c^2, but in N dimensions. The "distance" of all users from a specific one
is just a change of coordinates such that the specific user is put at the
origin:
User A, new coordinates: (x1 - x1, x2 - x2, ..., xn - xn), which is 0 and
what we wanted. For all other users, with respect to the specific user:
User B, new coordinates: (y1 - x1, y2 - x2, ..., yn - xn).
Now, the "distance" from the specific user to any other, in that reference
frame, is just the multi-dimensional, Pythagorean Theorem:
[(y1 - x1)^2 + (y2 - x2)^2 + ... + (yn - xn)^2]^1/2 = distance.
This outlines a multi-dimensional sphere, centered on the specific user.
The point is that everyone's position in space is know with respect to a
common origin and can be calculated beforehand and saved. Now, if you know
my position, you know my direction from the origin, then all users that are
a similar distance from the origin as I am, and in the general direction as
me, must be near me. This logic will produce a subset. Depending on how
restrictive you need to be, like top 100, top 10, top 5, etc., you could
create a general list of others that are near enough to calculate the
specific value without having to calculate it for everyone.
Say you need the 10 closest. Then with a set of, say 100, that where in my
general direction, you could quickly calculate the distance function above
for a mere 100 or so others and come up with the 10 closest, orders of
magnitude quicker than you could if you calculated the distance for
everyone.
Hope this helps.
Sincerely,
Anthony Thomas

"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?
|||However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this
is
taking way over one minute to execute, causing timeouts in the browser
Have you tried to increase the timeout value?
Madhivanan
|||See http://groups.google.co.uk/groups?q=nearestExamplar for
further discussion along the lines of what Anthony has said.
Steve Kass
Drew University
Shabam wrote:

>I have an application that lets users search based on degree of separation,
>so for instance, a user can search for age, hobbies, etc. and limit the
>search to just users who are 1 degree separate, 2 degrees separate, or 3
>degrees separate.
>However as it stands now, searches are taking a long long time because
>according to the programmer, the degree of separation is calculated
>dynamically upon search, and with a system of about 50,000 users this is
>taking way over one minute to execute, causing timeouts in the browser.
>Is there a better way to go about doing this type of search? Perhaps using
>a scheduler to perform some calculations beforehand so the searches can use
>it? Any feedback will be greatly appreciated.
>
>
|||Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.
|||Yes, I realize the answer I gave you was generic and mathematically based;
however, the logic is the same. For each user, you know there first level
acquantinces, etc., etc. This shouldn't change.
The only thing that is dynamic is which metrics to use for each search. If
you try to recompute it for each query, danamically, this becomes an M x N x
(N - 1) computation. As N or M gets large, this WILL NOT BE LINEAR; thus,
it does NOT scale well.
That, my friend, is a poorly written application and I wouldn't allow into
production. Add just 10% more users and it will bring your system to a
screetching halt!
Tell your "developer" to go back to school and learn what "good" code looks
like.
Sincerely,
Anthony Thomas

"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.
|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
> Thanks for the reply, but I think there's a misunderstanding here. When I
> say degree of separation, I don't mean separation by physical distance,
> but
> by friendship. For instance, A knows B, and B knows C. A doesn't know C.
> In this case the relationship would be:
> A <-> B <-> C
> B would be a first degree friend of A, and C would be a second degree
> friend
> of A, and so on.
> The search is limiting based on this type of degree of separation.
>
Ok. If you are just looking for a couple of "levels" you can do this pretty
quickly with a join.
There are several tricky problems with storing and sorting this kind of
relationship data, and you still didn't post DDL or sample data, so here's a
simple example:
drop table friend
drop table person
go
create table person
(
name varchar(20) primary key,
favorite_band varchar(50)
)
create table friend
(
friend1 varchar(20) not null references person,
friend2 varchar(20) not null references person,
constraint pk_friends primary key (friend1,friend2)
)
create index ix_friend2 on friend(friend2)
insert into person (name,favorite_band) values ('Joe','Def Leopard')
insert into person (name,favorite_band) values ('Alex','Wham')
insert into person (name,favorite_band) values ('Helmut','David Hasselhoff')
insert into person (name,favorite_band) values ('Dennis','Def Leopard')
insert into friend (friend1,friend2) values ('Joe','Alex')
insert into friend (friend1,friend2) values ('Alex','Joe')
insert into friend (friend1,friend2) values ('Joe','Helmut')
insert into friend (friend1,friend2) values ('Helmut','Joe')
insert into friend (friend1,friend2) values ('Helmut','Dennis')
insert into friend (friend1,friend2) values ('Dennis','Helmut')
insert into friend (friend1,friend2) values ('Dennis','Alex')
insert into friend (friend1,friend2) values ('Alex','Dennis')
'Joe has two tickes to the Def Leopard concert and needs someone to'
go
'with, but being shy he wants to go with a friend or a friend of a friend'
create view friends_and_friends_of_friends
as
select
p0.name,
p0.favorite_band,
p1.name friend_name,
p1.favorite_band friend_favorite_band,
p2.name friend_of_friend_name,
p2.favorite_band friend_of_friend_favorite_band
from
person p0
join friend f1 on p0.name = f1.friend1
join person p1 on f1.friend2 = p1.name
join friend f2 on p1.name = f2.friend1
join person p2 on p2.name = f2.friend2
where
p0.name <> p2.name
This query tells Joe that he can go with Dennis, and that he can get
introduced through either Alex or Helmut.
select *
from friends_and_friends_of_friends
where
name = 'Joe'
and
(
friend_favorite_band = 'Def Leopard'
or
friend_of_friend_favorite_band = 'Def Leopard'
)
David
sql

Degree of separation search

I have an application that lets users search based on degree of separation,
so for instance, a user can search for age, hobbies, etc. and limit the
search to just users who are 1 degree separate, 2 degrees separate, or 3
degrees separate.
However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this is
taking way over one minute to execute, causing timeouts in the browser.
Is there a better way to go about doing this type of search? Perhaps using
a scheduler to perform some calculations beforehand so the searches can use
it? Any feedback will be greatly appreciated.Shabam
Read this article
http://www.sommarskog.se/dyn-search.html
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:_aCdnc14X62v2bnfRVn-iw@.adelphia.com...
> I have an application that lets users search based on degree of
separation,
> so for instance, a user can search for age, hobbies, etc. and limit the
> search to just users who are 1 degree separate, 2 degrees separate, or 3
> degrees separate.
> However as it stands now, searches are taking a long long time because
> according to the programmer, the degree of separation is calculated
> dynamically upon search, and with a system of about 50,000 users this is
> taking way over one minute to execute, causing timeouts in the browser.
> Is there a better way to go about doing this type of search? Perhaps
using
> a scheduler to perform some calculations beforehand so the searches can
use
> it? Any feedback will be greatly appreciated.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Thanks for the article link. However the main problem with this search is
> the degree of separation search, not the other search criterias. It
> appears
> the application is going through calculating the degree of separation of
> each user, then taking the acceptable ones and doing a search on them.
> The
> degree of separation is stored in a function, and thus is being called
> hundres, perhaps thousands of times per search. This is why it's taking
> 1+
> minute to do a search. Do you have any ideas/suggestions on how to do
> this
> right?
>
There's not much concrete advice we can give without table DDL, sample data
and an explanation of the expected results.
David|||Basically, this is a spacial problem. What I find most often is that the
developer wants to take the parameters as dynamic, then calculate a
"distance" function between the user with respect to the remaining users
based on the chosen metrics. That is time consuming and expensive.
However, coordinates in "space" are fixed, relativistic effects aside. So,
they are not dynamic and everyone's "position" is known for all metrics.
The problem is that many metrics have differing scales, but we will ignore
that for the moment. So, from beginning geometry, we have for each user,
there position is the set of coordinates, with respect to the origin:
User A: (x1, x2, ..., xn)
User B: (y1, y2, ..., yn)
Their "distance" from the origin is just the Pythagorean Theorem: a^2 + b^2
= c^2, but in N dimensions. The "distance" of all users from a specific one
is just a change of coordinates such that the specific user is put at the
origin:
User A, new coordinates: (x1 - x1, x2 - x2, ..., xn - xn), which is 0 and
what we wanted. For all other users, with respect to the specific user:
User B, new coordinates: (y1 - x1, y2 - x2, ..., yn - xn).
Now, the "distance" from the specific user to any other, in that reference
frame, is just the multi-dimensional, Pythagorean Theorem:
[(y1 - x1)^2 + (y2 - x2)^2 + ... + (yn - xn)^2]^1/2 = distance.
This outlines a multi-dimensional sphere, centered on the specific user.
The point is that everyone's position in space is know with respect to a
common origin and can be calculated beforehand and saved. Now, if you know
my position, you know my direction from the origin, then all users that are
a similar distance from the origin as I am, and in the general direction as
me, must be near me. This logic will produce a subset. Depending on how
restrictive you need to be, like top 100, top 10, top 5, etc., you could
create a general list of others that are near enough to calculate the
specific value without having to calculate it for everyone.
Say you need the 10 closest. Then with a set of, say 100, that where in my
general direction, you could quickly calculate the distance function above
for a mere 100 or so others and come up with the 10 closest, orders of
magnitude quicker than you could if you calculated the distance for
everyone.
Hope this helps.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this
is
taking way over one minute to execute, causing timeouts in the browser
Have you tried to increase the timeout value?
Madhivanan|||See http://groups.google.co.uk/groups?q=nearestExamplar for
further discussion along the lines of what Anthony has said.
Steve Kass
Drew University
Shabam wrote:

>I have an application that lets users search based on degree of separation,
>so for instance, a user can search for age, hobbies, etc. and limit the
>search to just users who are 1 degree separate, 2 degrees separate, or 3
>degrees separate.
>However as it stands now, searches are taking a long long time because
>according to the programmer, the degree of separation is calculated
>dynamically upon search, and with a system of about 50,000 users this is
>taking way over one minute to execute, causing timeouts in the browser.
>Is there a better way to go about doing this type of search? Perhaps using
>a scheduler to perform some calculations beforehand so the searches can use
>it? Any feedback will be greatly appreciated.
>
>|||Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||Yes, I realize the answer I gave you was generic and mathematically based;
however, the logic is the same. For each user, you know there first level
acquantinces, etc., etc. This shouldn't change.
The only thing that is dynamic is which metrics to use for each search. If
you try to recompute it for each query, danamically, this becomes an M x N x
(N - 1) computation. As N or M gets large, this WILL NOT BE LINEAR; thus,
it does NOT scale well.
That, my friend, is a poorly written application and I wouldn't allow into
production. Add just 10% more users and it will bring your system to a
screetching halt!
Tell your "developer" to go back to school and learn what "good" code looks
like.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
> Thanks for the reply, but I think there's a misunderstanding here. When I
> say degree of separation, I don't mean separation by physical distance,
> but
> by friendship. For instance, A knows B, and B knows C. A doesn't know C.
> In this case the relationship would be:
> A <-> B <-> C
> B would be a first degree friend of A, and C would be a second degree
> friend
> of A, and so on.
> The search is limiting based on this type of degree of separation.
>
Ok. If you are just looking for a couple of "levels" you can do this pretty
quickly with a join.
There are several tricky problems with storing and sorting this kind of
relationship data, and you still didn't post DDL or sample data, so here's a
simple example:
drop table friend
drop table person
go
create table person
(
name varchar(20) primary key,
favorite_band varchar(50)
)
create table friend
(
friend1 varchar(20) not null references person,
friend2 varchar(20) not null references person,
constraint pk_friends primary key (friend1,friend2)
)
create index ix_friend2 on friend(friend2)
insert into person (name,favorite_band) values ('Joe','Def Leopard')
insert into person (name,favorite_band) values ('Alex','Wham')
insert into person (name,favorite_band) values ('Helmut','David Hasselhoff')
insert into person (name,favorite_band) values ('Dennis','Def Leopard')
insert into friend (friend1,friend2) values ('Joe','Alex')
insert into friend (friend1,friend2) values ('Alex','Joe')
insert into friend (friend1,friend2) values ('Joe','Helmut')
insert into friend (friend1,friend2) values ('Helmut','Joe')
insert into friend (friend1,friend2) values ('Helmut','Dennis')
insert into friend (friend1,friend2) values ('Dennis','Helmut')
insert into friend (friend1,friend2) values ('Dennis','Alex')
insert into friend (friend1,friend2) values ('Alex','Dennis')
'Joe has two tickes to the Def Leopard concert and needs someone to'
go
'with, but being shy he wants to go with a friend or a friend of a friend'
create view friends_and_friends_of_friends
as
select
p0.name,
p0.favorite_band,
p1.name friend_name,
p1.favorite_band friend_favorite_band,
p2.name friend_of_friend_name,
p2.favorite_band friend_of_friend_favorite_band
from
person p0
join friend f1 on p0.name = f1.friend1
join person p1 on f1.friend2 = p1.name
join friend f2 on p1.name = f2.friend1
join person p2 on p2.name = f2.friend2
where
p0.name <> p2.name
This query tells Joe that he can go with Dennis, and that he can get
introduced through either Alex or Helmut.
select *
from friends_and_friends_of_friends
where
name = 'Joe'
and
(
friend_favorite_band = 'Def Leopard'
or
friend_of_friend_favorite_band = 'Def Leopard'
)
David

Degree of separation search

I have an application that lets users search based on degree of separation,
so for instance, a user can search for age, hobbies, etc. and limit the
search to just users who are 1 degree separate, 2 degrees separate, or 3
degrees separate.
However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this is
taking way over one minute to execute, causing timeouts in the browser.
Is there a better way to go about doing this type of search? Perhaps using
a scheduler to perform some calculations beforehand so the searches can use
it? Any feedback will be greatly appreciated.Shabam
Read this article
http://www.sommarskog.se/dyn-search.html
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:_aCdnc14X62v2bnfRVn-iw@.adelphia.com...
> I have an application that lets users search based on degree of
separation,
> so for instance, a user can search for age, hobbies, etc. and limit the
> search to just users who are 1 degree separate, 2 degrees separate, or 3
> degrees separate.
> However as it stands now, searches are taking a long long time because
> according to the programmer, the degree of separation is calculated
> dynamically upon search, and with a system of about 50,000 users this is
> taking way over one minute to execute, causing timeouts in the browser.
> Is there a better way to go about doing this type of search? Perhaps
using
> a scheduler to perform some calculations beforehand so the searches can
use
> it? Any feedback will be greatly appreciated.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
>> Shabam
>> Read this article
>> http://www.sommarskog.se/dyn-search.html
> Thanks for the article link. However the main problem with this search is
> the degree of separation search, not the other search criterias. It
> appears
> the application is going through calculating the degree of separation of
> each user, then taking the acceptable ones and doing a search on them.
> The
> degree of separation is stored in a function, and thus is being called
> hundres, perhaps thousands of times per search. This is why it's taking
> 1+
> minute to do a search. Do you have any ideas/suggestions on how to do
> this
> right?
>
There's not much concrete advice we can give without table DDL, sample data
and an explanation of the expected results.
David|||Basically, this is a spacial problem. What I find most often is that the
developer wants to take the parameters as dynamic, then calculate a
"distance" function between the user with respect to the remaining users
based on the chosen metrics. That is time consuming and expensive.
However, coordinates in "space" are fixed, relativistic effects aside. So,
they are not dynamic and everyone's "position" is known for all metrics.
The problem is that many metrics have differing scales, but we will ignore
that for the moment. So, from beginning geometry, we have for each user,
there position is the set of coordinates, with respect to the origin:
User A: (x1, x2, ..., xn)
User B: (y1, y2, ..., yn)
Their "distance" from the origin is just the Pythagorean Theorem: a^2 + b^2
= c^2, but in N dimensions. The "distance" of all users from a specific one
is just a change of coordinates such that the specific user is put at the
origin:
User A, new coordinates: (x1 - x1, x2 - x2, ..., xn - xn), which is 0 and
what we wanted. For all other users, with respect to the specific user:
User B, new coordinates: (y1 - x1, y2 - x2, ..., yn - xn).
Now, the "distance" from the specific user to any other, in that reference
frame, is just the multi-dimensional, Pythagorean Theorem:
[(y1 - x1)^2 + (y2 - x2)^2 + ... + (yn - xn)^2]^1/2 = distance.
This outlines a multi-dimensional sphere, centered on the specific user.
The point is that everyone's position in space is know with respect to a
common origin and can be calculated beforehand and saved. Now, if you know
my position, you know my direction from the origin, then all users that are
a similar distance from the origin as I am, and in the general direction as
me, must be near me. This logic will produce a subset. Depending on how
restrictive you need to be, like top 100, top 10, top 5, etc., you could
create a general list of others that are near enough to calculate the
specific value without having to calculate it for everyone.
Say you need the 10 closest. Then with a set of, say 100, that where in my
general direction, you could quickly calculate the distance function above
for a mere 100 or so others and come up with the 10 closest, orders of
magnitude quicker than you could if you calculated the distance for
everyone.
Hope this helps.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:LNednSboiO-g2rnfRVn-iQ@.adelphia.com...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zTvJokHFHA.3624@.tk2msftngp13.phx.gbl...
> Shabam
> Read this article
> http://www.sommarskog.se/dyn-search.html
Thanks for the article link. However the main problem with this search is
the degree of separation search, not the other search criterias. It appears
the application is going through calculating the degree of separation of
each user, then taking the acceptable ones and doing a search on them. The
degree of separation is stored in a function, and thus is being called
hundres, perhaps thousands of times per search. This is why it's taking 1+
minute to do a search. Do you have any ideas/suggestions on how to do this
right?|||However as it stands now, searches are taking a long long time because
according to the programmer, the degree of separation is calculated
dynamically upon search, and with a system of about 50,000 users this
is
taking way over one minute to execute, causing timeouts in the browser
Have you tried to increase the timeout value?
Madhivanan|||See http://groups.google.co.uk/groups?q=nearestExamplar for
further discussion along the lines of what Anthony has said.
Steve Kass
Drew University
Shabam wrote:
>I have an application that lets users search based on degree of separation,
>so for instance, a user can search for age, hobbies, etc. and limit the
>search to just users who are 1 degree separate, 2 degrees separate, or 3
>degrees separate.
>However as it stands now, searches are taking a long long time because
>according to the programmer, the degree of separation is calculated
>dynamically upon search, and with a system of about 50,000 users this is
>taking way over one minute to execute, causing timeouts in the browser.
>Is there a better way to go about doing this type of search? Perhaps using
>a scheduler to perform some calculations beforehand so the searches can use
>it? Any feedback will be greatly appreciated.
>
>|||Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||Yes, I realize the answer I gave you was generic and mathematically based;
however, the logic is the same. For each user, you know there first level
acquantinces, etc., etc. This shouldn't change.
The only thing that is dynamic is which metrics to use for each search. If
you try to recompute it for each query, danamically, this becomes an M x N x
(N - 1) computation. As N or M gets large, this WILL NOT BE LINEAR; thus,
it does NOT scale well.
That, my friend, is a poorly written application and I wouldn't allow into
production. Add just 10% more users and it will bring your system to a
screetching halt!
Tell your "developer" to go back to school and learn what "good" code looks
like.
Sincerely,
Anthony Thomas
"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
Thanks for the reply, but I think there's a misunderstanding here. When I
say degree of separation, I don't mean separation by physical distance, but
by friendship. For instance, A knows B, and B knows C. A doesn't know C.
In this case the relationship would be:
A <-> B <-> C
B would be a first degree friend of A, and C would be a second degree friend
of A, and so on.
The search is limiting based on this type of degree of separation.|||"Shabam" <chalupa@.yomama-nospam.com> wrote in message
news:O-idnU8g8Imw4bjfRVn-1w@.adelphia.com...
> Thanks for the reply, but I think there's a misunderstanding here. When I
> say degree of separation, I don't mean separation by physical distance,
> but
> by friendship. For instance, A knows B, and B knows C. A doesn't know C.
> In this case the relationship would be:
> A <-> B <-> C
> B would be a first degree friend of A, and C would be a second degree
> friend
> of A, and so on.
> The search is limiting based on this type of degree of separation.
>
Ok. If you are just looking for a couple of "levels" you can do this pretty
quickly with a join.
There are several tricky problems with storing and sorting this kind of
relationship data, and you still didn't post DDL or sample data, so here's a
simple example:
drop table friend
drop table person
go
create table person
(
name varchar(20) primary key,
favorite_band varchar(50)
)
create table friend
(
friend1 varchar(20) not null references person,
friend2 varchar(20) not null references person,
constraint pk_friends primary key (friend1,friend2)
)
create index ix_friend2 on friend(friend2)
insert into person (name,favorite_band) values ('Joe','Def Leopard')
insert into person (name,favorite_band) values ('Alex','Wham')
insert into person (name,favorite_band) values ('Helmut','David Hasselhoff')
insert into person (name,favorite_band) values ('Dennis','Def Leopard')
insert into friend (friend1,friend2) values ('Joe','Alex')
insert into friend (friend1,friend2) values ('Alex','Joe')
insert into friend (friend1,friend2) values ('Joe','Helmut')
insert into friend (friend1,friend2) values ('Helmut','Joe')
insert into friend (friend1,friend2) values ('Helmut','Dennis')
insert into friend (friend1,friend2) values ('Dennis','Helmut')
insert into friend (friend1,friend2) values ('Dennis','Alex')
insert into friend (friend1,friend2) values ('Alex','Dennis')
'Joe has two tickes to the Def Leopard concert and needs someone to'
go
'with, but being shy he wants to go with a friend or a friend of a friend'
create view friends_and_friends_of_friends
as
select
p0.name,
p0.favorite_band,
p1.name friend_name,
p1.favorite_band friend_favorite_band,
p2.name friend_of_friend_name,
p2.favorite_band friend_of_friend_favorite_band
from
person p0
join friend f1 on p0.name = f1.friend1
join person p1 on f1.friend2 = p1.name
join friend f2 on p1.name = f2.friend1
join person p2 on p2.name = f2.friend2
where
p0.name <> p2.name
This query tells Joe that he can go with Dennis, and that he can get
introduced through either Alex or Helmut.
select *
from friends_and_friends_of_friends
where
name = 'Joe'
and
(
friend_favorite_band = 'Def Leopard'
or
friend_of_friend_favorite_band = 'Def Leopard'
)
David

define query parameters ?

when I'm defining a dataset based on stored procedure I type in the name of
my stored proc and hit the !
then define query parameters comes up. My stored proc has default values
defined for the parameter. Why not have the define query parameters fill in
those default values? It's really anoying to type in 15 parameters almost
everytime I need to re-run the query.HI,
"letuce dance" <letucedance@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C5B2E705-D5B4-47F7-8595-FDC9266B917F@.microsoft.com...
> when I'm defining a dataset based on stored procedure I type in the name
> of
> my stored proc and hit the !
> then define query parameters comes up. My stored proc has default values
> defined for the parameter. Why not have the define query parameters fill
> in
> those default values? It's really anoying to type in 15 parameters almost
> everytime I need to re-run the query.
you can set the default values in the report itself.
witch to layout, click on the upper left corner (report properties), context
menu "report parameters" and there you can set the default settings
"not-queried" and store all your default-parameter values.
hth, Tony

Defaulting Parameters based on time of day?

I need to set up some parameters which are based on the time of day. For instance, night shift vs/ day shift. If I run the report during the day, default to the day shift criteria (5 am to 5 pm), and likewise for the night shift (5 pm to 5 am). On the night shift, I also need to accomodate for the change in dates as well. Is this even possible in a single report? HELP!!

Thanks!

Does it really need to be parameters, or could you just check the time in your SQL or report code?