Showing posts with label application. Show all posts
Showing posts with label application. Show all posts

Tuesday, March 27, 2012

delete all sql database user objects

Good afternoon,

I have a little trouble with sql server 2005 express database:

customer need install new web application to hosting, but at hosting is currently exist other web application and it's using DB what I must use. DB contains a big number of tables, views, functions, etc.

I need delete all user objects from this DB, it must be as new created one.

Is any query whitch can do this?

PS: I know, best way for this is delete DB and create new one, but i haven't permissions for these.

Thank's for reply.

so u want to drop all objects created by u...u can generate delete scripts(for all user objects in that DB) for all the objects using the management studio...select all the items u need to delete and run the script...|||Hi,

use this script to first drop the constraints of the tables then afterwards drop the tables. If you have any further schemabound objects like views, procedures or functions you will also have to drop them in the right order.

Select

'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME +

' DROP CONSTRAINT ' + Constraint_Schema + '.' + Constraint_Name

from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

WHERE OBJECTPROPERTY(OBJECT_ID(Constraint_Schema + '.' + Constraint_Name), 'IsMSShipped') = 0

Select

'DROP TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME

from INFORMATION_SCHEMA.TABLES

WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Delete All Rows

Is there a way to programatically delete all rows from a number of tables?
I am developing an application and am doing a lot of testing with dummy
data. As a result, I am regularly deleting the contents of tables. If I
could automate this down to a simple piece of code that would be great.
Thanks
Keith,
This is from Uri's post sometime back.
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>
|||Create a cursor which reads off of INFORMATION_SCHEMA.TABLES or sysobjects and for each table, fire TRUNCATE
TABLE or DELETE statement. Just be aware of foreign keys. If you have such, I suggest you start with "bottom
most" tables and do TRUNCATE for all tables that no other refers to and for those that are referenced, do
DELETE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith" <@..> wrote in message news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>
|||If this is just for use in a development environment then it's maybe
acceptable to use the undocumented funcion sp_msforeachtable:
EXEC SP_MSFOREACHTABLE '-- TRUNCATE TABLE ?'
or
EXEC SP_MSFOREACHTABLE '-- DELETE FROM ?'
(remove the -- comment from these statements to run them, but only when
you're sure you want to do this!)
TRUNCATE TABLE will fail on tables with Foreign Key dependencies so you may
need to use the DELETE version.
David Portas
SQL Server MVP
|||Just be aware that if you use delete, the log will grow significantly. Be
sure to backup the log (perhaps even between each delete ) if the tables are
large, to keep the log from growing growing growing..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>

Sunday, March 25, 2012

Delete All Rows

Is there a way to programatically delete all rows from a number of tables?
I am developing an application and am doing a lot of testing with dummy
data. As a result, I am regularly deleting the contents of tables. If I
could automate this down to a simple piece of code that would be great.
ThanksKeith,
This is from Uri's post sometime back.
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||Create a cursor which reads off of INFORMATION_SCHEMA.TABLES or sysobjects and for each table, fire TRUNCATE
TABLE or DELETE statement. Just be aware of foreign keys. If you have such, I suggest you start with "bottom
most" tables and do TRUNCATE for all tables that no other refers to and for those that are referenced, do
DELETE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith" <@..> wrote in message news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||If this is just for use in a development environment then it's maybe
acceptable to use the undocumented funcion sp_msforeachtable:
EXEC SP_MSFOREACHTABLE '-- TRUNCATE TABLE ?'
or
EXEC SP_MSFOREACHTABLE '-- DELETE FROM ?'
(remove the -- comment from these statements to run them, but only when
you're sure you want to do this!)
TRUNCATE TABLE will fail on tables with Foreign Key dependencies so you may
need to use the DELETE version.
--
David Portas
SQL Server MVP
--|||Just be aware that if you use delete, the log will grow significantly. Be
sure to backup the log (perhaps even between each delete ) if the tables are
large, to keep the log from growing growing growing..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>

Delete All Rows

Is there a way to programatically delete all rows from a number of tables?
I am developing an application and am doing a lot of testing with dummy
data. As a result, I am regularly deleting the contents of tables. If I
could automate this down to a simple piece of code that would be great.
ThanksKeith,
This is from Uri's post sometime back.
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||Create a cursor which reads off of INFORMATION_SCHEMA.TABLES or sysobjects a
nd for each table, fire TRUNCATE
TABLE or DELETE statement. Just be aware of foreign keys. If you have such,
I suggest you start with "bottom
most" tables and do TRUNCATE for all tables that no other refers to and for
those that are referenced, do
DELETE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith" <@..> wrote in message news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...">
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||If this is just for use in a development environment then it's maybe
acceptable to use the undocumented funcion sp_msforeachtable:
EXEC SP_MSFOREACHTABLE '-- TRUNCATE TABLE ?'
or
EXEC SP_MSFOREACHTABLE '-- DELETE FROM ?'
(remove the -- comment from these statements to run them, but only when
you're sure you want to do this!)
TRUNCATE TABLE will fail on tables with Foreign Key dependencies so you may
need to use the DELETE version.
David Portas
SQL Server MVP
--|||Just be aware that if you use delete, the log will grow significantly. Be
sure to backup the log (perhaps even between each delete ) if the tables are
large, to keep the log from growing growing growing..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>

Thursday, March 22, 2012

Delayed stored procedure

We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
interface/application provided by a third party. We're recruiters and this i
s
the “Applicant Tracking System”/database we use to track our
candidates/resumes and clients.
This software was installed on my machine by others and I do not have the
technical expertise to diagnose problems when they occur.
There is a button within the application we click to search the (indexed)
resumes based on keywords.
Apparently this button runs a stored command thru SQL Server:
sp_help_fultext_columns 'consultants"
It used to be that when we clicked on the button a window (GUI) would open
instantly with fields where we enter our key words and then execute the
search.
However… for unknown reasons it now takes approximately 40 seconds after w
e
click for the search window to open. Our software provider does not know why
.
They logged on to my machine today and used Enterprise Manager and I think
Query Analyzer to run the query above. It also took 40 seconds to open and
they said this indicated we have a problem with SQL Server... not their
application... and sent us off to find our own solution.
We live in the country and there are no SQL Server experts within miles.
Does this sound like a problem that could be diagnosed/fixed by someone who
accesses my machine remotely?
Or better yet... does anyone have a clue how to fix the problem simply based
on my question above?Jeff,
If somebody can remote desktop in , they should be able to identify
the issue.
I don't want to sound presumptous but I somehow find it hard to believe that
just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
stored proc only brings back a list of columns that have full text index on
them in a particular table(or table-column),
If you can run trace and make sure that is the only thing running in the
backend when you click that button, that might help you narrow it down a
little more.
To run a trace just click sql profiler from the Sql server program menu and
click File-New Trace & Hit run. go back and click the Button on the
Application. and see the list of sql commands being executed as a result.
HTH,
RA
"Jeff Ingman" wrote:

> We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> interface/application provided by a third party. We're recruiters and this
is
> the “Applicant Tracking System”/database we use to track our
> candidates/resumes and clients.
> This software was installed on my machine by others and I do not have the
> technical expertise to diagnose problems when they occur.
> There is a button within the application we click to search the (indexed)
> resumes based on keywords.
> Apparently this button runs a stored command thru SQL Server:
> sp_help_fultext_columns 'consultants"
> It used to be that when we clicked on the button a window (GUI) would open
> instantly with fields where we enter our key words and then execute the
> search.
> However… for unknown reasons it now takes approximately 40 seconds after
we
> click for the search window to open. Our software provider does not know w
hy.
> They logged on to my machine today and used Enterprise Manager and I think
> Query Analyzer to run the query above. It also took 40 seconds to open and
> they said this indicated we have a problem with SQL Server... not their
> application... and sent us off to find our own solution.
> We live in the country and there are no SQL Server experts within miles.
> Does this sound like a problem that could be diagnosed/fixed by someone wh
o
> accesses my machine remotely?
> Or better yet... does anyone have a clue how to fix the problem simply bas
ed
> on my question above?|||Rocky...
Thanks for your comments. But I'm not confident enough in my SQL expertise
to understand/follow thru on your recommendations.
(One small point... I mispelled the query in my original post... it should
be "fulltext" not "fultext").
I think I'll need to find a DBA who can logon to my machine remotely to
diagnose this.
I'd have done this by now except we only access this command a couple times
daily and I haven't wanted to hire an expensive consultant.
Would Microsoft paid incident support be a good route to go?
........................
"Rocky A" wrote:
[vbcol=seagreen]
> Jeff,
> If somebody can remote desktop in , they should be able to identify
> the issue.
> I don't want to sound presumptous but I somehow find it hard to believe th
at
> just sp_help_fultext_columns 'consultants' is taking 40 seconds. This syst
em
> stored proc only brings back a list of columns that have full text index o
n
> them in a particular table(or table-column),
> If you can run trace and make sure that is the only thing running in the
> backend when you click that button, that might help you narrow it down a
> little more.
> To run a trace just click sql profiler from the Sql server program menu an
d
> click File-New Trace & Hit run. go back and click the Button on the
> Application. and see the list of sql commands being executed as a result.
> HTH,
> RA
> "Jeff Ingman" wrote:
>|||Jeff,
I would suggest going through a consultant rather than a MS paid
incident, I am guessing this is not an issue with Sql server as a product, i
t
is more of a day-to-day technical issue, i would go the DBA route. I can
take a look at it if you are interested.
thanks,
RA
"Jeff Ingman" wrote:
[vbcol=seagreen]
> Rocky...
> Thanks for your comments. But I'm not confident enough in my SQL expertise
> to understand/follow thru on your recommendations.
> (One small point... I mispelled the query in my original post... it should
> be "fulltext" not "fultext").
> I think I'll need to find a DBA who can logon to my machine remotely to
> diagnose this.
> I'd have done this by now except we only access this command a couple time
s
> daily and I haven't wanted to hire an expensive consultant.
> Would Microsoft paid incident support be a good route to go?
> ........................
> "Rocky A" wrote:
>|||I would like help Rocky... how do we arrange? You can find my email address
at www.ingmancompany.com.
jeff
"Rocky A" wrote:
[vbcol=seagreen]
> Jeff,
> I would suggest going through a consultant rather than a MS paid
> incident, I am guessing this is not an issue with Sql server as a product,
it
> is more of a day-to-day technical issue, i would go the DBA route. I can
> take a look at it if you are interested.
> thanks,
> RA
> "Jeff Ingman" wrote:
>

Delayed stored procedure

We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
interface/application provided by a third party. We're recruiters and this is
the â'Applicant Tracking Systemâ'/database we use to track our
candidates/resumes and clients.
This software was installed on my machine by others and I do not have the
technical expertise to diagnose problems when they occur.
There is a button within the application we click to search the (indexed)
resumes based on keywords.
Apparently this button runs a stored command thru SQL Server:
sp_help_fultext_columns 'consultants"
It used to be that when we clicked on the button a window (GUI) would open
instantly with fields where we enter our key words and then execute the
search.
Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
click for the search window to open. Our software provider does not know why.
They logged on to my machine today and used Enterprise Manager and I think
Query Analyzer to run the query above. It also took 40 seconds to open and
they said this indicated we have a problem with SQL Server... not their
application... and sent us off to find our own solution.
We live in the country and there are no SQL Server experts within miles.
Does this sound like a problem that could be diagnosed/fixed by someone who
accesses my machine remotely?
Or better yet... does anyone have a clue how to fix the problem simply based
on my question above?Jeff,
If somebody can remote desktop in , they should be able to identify
the issue.
I don't want to sound presumptous but I somehow find it hard to believe that
just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
stored proc only brings back a list of columns that have full text index on
them in a particular table(or table-column),
If you can run trace and make sure that is the only thing running in the
backend when you click that button, that might help you narrow it down a
little more.
To run a trace just click sql profiler from the Sql server program menu and
click File-New Trace & Hit run. go back and click the Button on the
Application. and see the list of sql commands being executed as a result.
HTH,
RA
"Jeff Ingman" wrote:
> We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> interface/application provided by a third party. We're recruiters and this is
> the â'Applicant Tracking Systemâ'/database we use to track our
> candidates/resumes and clients.
> This software was installed on my machine by others and I do not have the
> technical expertise to diagnose problems when they occur.
> There is a button within the application we click to search the (indexed)
> resumes based on keywords.
> Apparently this button runs a stored command thru SQL Server:
> sp_help_fultext_columns 'consultants"
> It used to be that when we clicked on the button a window (GUI) would open
> instantly with fields where we enter our key words and then execute the
> search.
> Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> click for the search window to open. Our software provider does not know why.
> They logged on to my machine today and used Enterprise Manager and I think
> Query Analyzer to run the query above. It also took 40 seconds to open and
> they said this indicated we have a problem with SQL Server... not their
> application... and sent us off to find our own solution.
> We live in the country and there are no SQL Server experts within miles.
> Does this sound like a problem that could be diagnosed/fixed by someone who
> accesses my machine remotely?
> Or better yet... does anyone have a clue how to fix the problem simply based
> on my question above?|||Rocky...
Thanks for your comments. But I'm not confident enough in my SQL expertise
to understand/follow thru on your recommendations.
(One small point... I mispelled the query in my original post... it should
be "fulltext" not "fultext").
I think I'll need to find a DBA who can logon to my machine remotely to
diagnose this.
I'd have done this by now except we only access this command a couple times
daily and I haven't wanted to hire an expensive consultant.
Would Microsoft paid incident support be a good route to go?
........................
"Rocky A" wrote:
> Jeff,
> If somebody can remote desktop in , they should be able to identify
> the issue.
> I don't want to sound presumptous but I somehow find it hard to believe that
> just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
> stored proc only brings back a list of columns that have full text index on
> them in a particular table(or table-column),
> If you can run trace and make sure that is the only thing running in the
> backend when you click that button, that might help you narrow it down a
> little more.
> To run a trace just click sql profiler from the Sql server program menu and
> click File-New Trace & Hit run. go back and click the Button on the
> Application. and see the list of sql commands being executed as a result.
> HTH,
> RA
> "Jeff Ingman" wrote:
> > We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> > interface/application provided by a third party. We're recruiters and this is
> > the â'Applicant Tracking Systemâ'/database we use to track our
> > candidates/resumes and clients.
> >
> > This software was installed on my machine by others and I do not have the
> > technical expertise to diagnose problems when they occur.
> >
> > There is a button within the application we click to search the (indexed)
> > resumes based on keywords.
> >
> > Apparently this button runs a stored command thru SQL Server:
> >
> > sp_help_fultext_columns 'consultants"
> >
> > It used to be that when we clicked on the button a window (GUI) would open
> > instantly with fields where we enter our key words and then execute the
> > search.
> >
> > Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> > click for the search window to open. Our software provider does not know why.
> > They logged on to my machine today and used Enterprise Manager and I think
> > Query Analyzer to run the query above. It also took 40 seconds to open and
> > they said this indicated we have a problem with SQL Server... not their
> > application... and sent us off to find our own solution.
> >
> > We live in the country and there are no SQL Server experts within miles.
> > Does this sound like a problem that could be diagnosed/fixed by someone who
> > accesses my machine remotely?
> >
> > Or better yet... does anyone have a clue how to fix the problem simply based
> > on my question above?|||Jeff,
I would suggest going through a consultant rather than a MS paid
incident, I am guessing this is not an issue with Sql server as a product, it
is more of a day-to-day technical issue, i would go the DBA route. I can
take a look at it if you are interested.
thanks,
RA
"Jeff Ingman" wrote:
> Rocky...
> Thanks for your comments. But I'm not confident enough in my SQL expertise
> to understand/follow thru on your recommendations.
> (One small point... I mispelled the query in my original post... it should
> be "fulltext" not "fultext").
> I think I'll need to find a DBA who can logon to my machine remotely to
> diagnose this.
> I'd have done this by now except we only access this command a couple times
> daily and I haven't wanted to hire an expensive consultant.
> Would Microsoft paid incident support be a good route to go?
> ........................
> "Rocky A" wrote:
> > Jeff,
> > If somebody can remote desktop in , they should be able to identify
> > the issue.
> >
> > I don't want to sound presumptous but I somehow find it hard to believe that
> > just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
> > stored proc only brings back a list of columns that have full text index on
> > them in a particular table(or table-column),
> >
> > If you can run trace and make sure that is the only thing running in the
> > backend when you click that button, that might help you narrow it down a
> > little more.
> >
> > To run a trace just click sql profiler from the Sql server program menu and
> > click File-New Trace & Hit run. go back and click the Button on the
> > Application. and see the list of sql commands being executed as a result.
> >
> > HTH,
> > RA
> >
> > "Jeff Ingman" wrote:
> >
> > > We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> > > interface/application provided by a third party. We're recruiters and this is
> > > the â'Applicant Tracking Systemâ'/database we use to track our
> > > candidates/resumes and clients.
> > >
> > > This software was installed on my machine by others and I do not have the
> > > technical expertise to diagnose problems when they occur.
> > >
> > > There is a button within the application we click to search the (indexed)
> > > resumes based on keywords.
> > >
> > > Apparently this button runs a stored command thru SQL Server:
> > >
> > > sp_help_fultext_columns 'consultants"
> > >
> > > It used to be that when we clicked on the button a window (GUI) would open
> > > instantly with fields where we enter our key words and then execute the
> > > search.
> > >
> > > Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> > > click for the search window to open. Our software provider does not know why.
> > > They logged on to my machine today and used Enterprise Manager and I think
> > > Query Analyzer to run the query above. It also took 40 seconds to open and
> > > they said this indicated we have a problem with SQL Server... not their
> > > application... and sent us off to find our own solution.
> > >
> > > We live in the country and there are no SQL Server experts within miles.
> > > Does this sound like a problem that could be diagnosed/fixed by someone who
> > > accesses my machine remotely?
> > >
> > > Or better yet... does anyone have a clue how to fix the problem simply based
> > > on my question above?|||I would like help Rocky... how do we arrange? You can find my email address
at www.ingmancompany.com.
jeff
"Rocky A" wrote:
> Jeff,
> I would suggest going through a consultant rather than a MS paid
> incident, I am guessing this is not an issue with Sql server as a product, it
> is more of a day-to-day technical issue, i would go the DBA route. I can
> take a look at it if you are interested.
> thanks,
> RA
> "Jeff Ingman" wrote:
> > Rocky...
> >
> > Thanks for your comments. But I'm not confident enough in my SQL expertise
> > to understand/follow thru on your recommendations.
> >
> > (One small point... I mispelled the query in my original post... it should
> > be "fulltext" not "fultext").
> >
> > I think I'll need to find a DBA who can logon to my machine remotely to
> > diagnose this.
> >
> > I'd have done this by now except we only access this command a couple times
> > daily and I haven't wanted to hire an expensive consultant.
> >
> > Would Microsoft paid incident support be a good route to go?
> > ........................
> > "Rocky A" wrote:
> >
> > > Jeff,
> > > If somebody can remote desktop in , they should be able to identify
> > > the issue.
> > >
> > > I don't want to sound presumptous but I somehow find it hard to believe that
> > > just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
> > > stored proc only brings back a list of columns that have full text index on
> > > them in a particular table(or table-column),
> > >
> > > If you can run trace and make sure that is the only thing running in the
> > > backend when you click that button, that might help you narrow it down a
> > > little more.
> > >
> > > To run a trace just click sql profiler from the Sql server program menu and
> > > click File-New Trace & Hit run. go back and click the Button on the
> > > Application. and see the list of sql commands being executed as a result.
> > >
> > > HTH,
> > > RA
> > >
> > > "Jeff Ingman" wrote:
> > >
> > > > We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> > > > interface/application provided by a third party. We're recruiters and this is
> > > > the â'Applicant Tracking Systemâ'/database we use to track our
> > > > candidates/resumes and clients.
> > > >
> > > > This software was installed on my machine by others and I do not have the
> > > > technical expertise to diagnose problems when they occur.
> > > >
> > > > There is a button within the application we click to search the (indexed)
> > > > resumes based on keywords.
> > > >
> > > > Apparently this button runs a stored command thru SQL Server:
> > > >
> > > > sp_help_fultext_columns 'consultants"
> > > >
> > > > It used to be that when we clicked on the button a window (GUI) would open
> > > > instantly with fields where we enter our key words and then execute the
> > > > search.
> > > >
> > > > Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> > > > click for the search window to open. Our software provider does not know why.
> > > > They logged on to my machine today and used Enterprise Manager and I think
> > > > Query Analyzer to run the query above. It also took 40 seconds to open and
> > > > they said this indicated we have a problem with SQL Server... not their
> > > > application... and sent us off to find our own solution.
> > > >
> > > > We live in the country and there are no SQL Server experts within miles.
> > > > Does this sound like a problem that could be diagnosed/fixed by someone who
> > > > accesses my machine remotely?
> > > >
> > > > Or better yet... does anyone have a clue how to fix the problem simply based
> > > > on my question above?sql

Delayed Send

Hi

My application sends notifications by creating a once-off subcription, then raising an event in Notification Services. This causes the notification to be issued immediately.

I want to be able to create a notification that is not sent until some time in the future. I'm not sure if I should be looking at ScheduledRules or EventRules.

Has anyone done anything similar?

Thanks

Robert.

Event driven rules produce notifications as events come into the notification application. Scheduled rules create notifications according to the schedule defined in the subscription. Take a look at the ScheduleRecurrence and ScheduleStart properties of the Subscription class.

HTH...

Joe

Wednesday, March 21, 2012

Delay in running queries

I have a problem in running queries.

I developed an application uses sqlserver 2005 express edition

I create all queries in storedprocedures.every things work perfect but some times I get long delay in running queries. but after some minutes it comes regular . I coudlnt find any relation between delay and time of work.it comes by chance . also I set timeout for running query for 30 sec. but some times it took more than minutes.

what should I do?

It sounds like you are experience 'blocking' and 'locking' behavior. If you post the complete stored procedure code, we 'may' be able to help you.

And you could also use Profiler to determine what procedures and/or statements in the procedures are causing the blocks.

|||

my data base has so many procudures and this problem may happend in running each one . one simple of them is :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[STPListStock]

AS

SELECT GoodSyscode, Title, Code, BarCode, Type, FstUnit, SecUnit, UnitRate, OrderPoint, SalePrice1, SalePrice2, SalePrice3, SalePrice4, SalePrice5,

VisitorPer, Comment, DiscontPer, UserPrice, GroupName1, GroupName2, Term, Weight, SerialNo, TypeId, GroupId1, GroupId2

FROM VWCompleteGood

|||

try this

EXEC sp_dboption '<db_name>', 'autoclose', 'false';

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
This is probably for the simple reason that it takes a while to create the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegr oups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>
|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason
|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegr oups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>
|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the d
atabase.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the
database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As so
on as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a l
ittle while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google a
nd you should find some
info, possibly also in Books Online. So it is possible that you can query sy
s.databases (state_desc
column) to see what state the database is in and based on that connect. Stil
l a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google
and you should find some
> info, possibly also in Books Online. So it is possible that you can query
sys.databases (state_desc
> column) to see what state the database is in and based on that connect. St
ill a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/This is probably for the simple reason that it takes a while to create the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegroups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegroups.com...
> Tibor Karaszi wrote:
>> This is probably for the simple reason that it takes a while to create the database.
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason

Delay between CREATE DATABASE and ability to connect to that database

I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.

I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:

open new connection
create database
create tables
populate tables
close connection

// open new connection /* can't do this yet, as it would break */

for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}

Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?

Thanks,

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/On 6 Oct 2006 17:36:01 -0700, Jason Kester wrote:

(snip)

Hi Jason,

You posted the same question and youy already received some answers
there.

In the future, please post to a single group only. It prevents people
wasting time on a problm that has already been solved.

--
Hugo Kornelis, SQL Server MVP

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

Degradation of performance with time

I have OLTP database, 24x5. The database used by application. The application
have new version like every half a year. During deployment of new version,
old database renamed, creating new database with same structure and move
data from old database to new with DTS. After deployment of new version I see
performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
return back to not so good state. I guess it may be caused by fragmentation?
Every week runs dbcc dbreindex, to defragment database. Database file and
log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
any other files on same drive where database files are located. Most of
tables have clustered indices, but not all tables have it - our benchmark
tests showed what for some tables better to not have clustered index. SQL2k
SP3.
Why is such performance degradation? What can be done to fix it?
What about statistics, are those kept up to date?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Hi
Your description seems to follow the sort of pattern associated with
fragmented indexes, but as you rebuild them this should not happen and you
should see an improvement after the rebuild that decays over time. Try using
DBCC SHOWCONTIG to monitor the index fragmentation, you may also want to
update the statistics more often. Check the query plans to see if they differ
when the performance is slower.
You will need to know how your LUNs map to physical discs to determine if
there is any contention, but this should not follow the performance patterns
you have described. Check out physical disc fragmentation and defragment if
necessary.
You may want to also look at the blocker script
http://support.microsoft.com/kb/271509 and monitor performance with perfmon
and compare the results when it is working well against when it isn't.
You don't say if your system databases or tempdb are located on the same
discs, if they are you may want to consider moving them. That may not solve
your problem but it would be good practices.
John
"andsm" wrote:

> I have OLTP database, 24x5. The database used by application. The application
> have new version like every half a year. During deployment of new version,
> old database renamed, creating new database with same structure and move
> data from old database to new with DTS. After deployment of new version I see
> performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
> return back to not so good state. I guess it may be caused by fragmentation?
> Every week runs dbcc dbreindex, to defragment database. Database file and
> log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
> any other files on same drive where database files are located. Most of
> tables have clustered indices, but not all tables have it - our benchmark
> tests showed what for some tables better to not have clustered index. SQL2k
> SP3.
> Why is such performance degradation? What can be done to fix it?
|||I'd look into specificallhy what is going slow. Are there specific
tranactions that you run frequently that become slow?
Can you reduce the number of indexes on the biggest tables?