Generally Question

Hey gang.

Question here:

If someone registering for the site, enters their GUID. Now a check is made
on that GUID across a DB Table to see if the GUID is present. There are
approx 57,000 GUIDS in said table. It is msSQL DB on windows server 2000
server, how long would that check take?

I know there are several variables that come into play here, but I am
looking for a ball park figure on how long it would and should take.

Any ideas??


Thanks in advance

Bam

Bam wrote:
> Hey gang.
>
> Question here:
>
> If someone registering for the site, enters their GUID. Now a check
> is made on that GUID across a DB Table to see if the GUID is present.
> There are approx 57,000 GUIDS in said table. It is msSQL DB on
> windows server 2000 server, how long would that check take?
>
> I know there are several variables that come into play here, but I am
> looking for a ball park figure on how long it would and should take.
>
> Any ideas??
>
>
Given that the column is indexed, I would expect a result in less than a
second. But why ask us? Test it for yourself.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bam wrote:
> Hey gang.
>
> Question here:
>
> If someone registering for the site, enters their GUID. Now a check
> is made on that GUID across a DB Table to see if the GUID is present.
> There are approx 57,000 GUIDS in said table. It is msSQL DB on
> windows server 2000 server, how long would that check take?
>
> I know there are several variables that come into play here, but I am
> looking for a ball park figure on how long it would and should take.
>
>

Here is a script to allow you to test this on your own server:

USE [test]
GO
/****** Object:  Table [dbo].[guidtest]    Script Date: 04/13/2008 11:40:01
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[guidtest](
[Guidcol] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT
[DF_guidtest_Guidcol]  DEFAULT (newid()),
[Datacol] [int] NOT NULL,
CONSTRAINT [PK_guidtest] PRIMARY KEY CLUSTERED
(
[Guidcol] ASC
) ON [PRIMARY]
) ON [PRIMARY]
go
declare @val int
set @val = 0
while @val < 57001
begin
set @val = @val + 1
INSERT INTO [test].[dbo].[guidtest]
([Guidcol]
,[Datacol])
VALUES
(default
,@val)
end
declare @guid uniqueidentifier
set @guid = (select guidcol from guidtest where datacol = 46598)
select GETDATE()
select guidcol from guidtest where guidcol=@guid
select GETDATE()


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23m4IxlXnIHA.3572@TK2MSFTNGP02.phx.gbl...
> Bam wrote: 
> Given that the column is indexed, I would expect a result in less than a
> second. But why ask us? Test it for yourself.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

I wanted to see the time it would take, prior to putting the guids in a
table. I want to use this upon registeration, to see if a player is banned
or not

I will do the test below, and check the results.

thanks Bob

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ekuIf2XnIHA.4536@TK2MSFTNGP06.phx.gbl...
> Bam wrote: 
>
> Here is a script to allow you to test this on your own server:
>
> USE [test]
> GO
> /****** Object:  Table [dbo].[guidtest]    Script Date: 04/13/2008
> 11:40:01 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[guidtest](
> [Guidcol] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT
> [DF_guidtest_Guidcol]  DEFAULT (newid()),
> [Datacol] [int] NOT NULL,
> CONSTRAINT [PK_guidtest] PRIMARY KEY CLUSTERED
> (
> [Guidcol] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> go
> declare @val int
> set @val = 0
> while @val < 57001
> begin
> set @val = @val + 1
> INSERT INTO [test].[dbo].[guidtest]
>           ([Guidcol]
>           ,[Datacol])
>     VALUES
>           (default
>           ,@val)
> end
> declare @guid uniqueidentifier
> set @guid = (select guidcol from guidtest where datacol = 46598)
> select GETDATE()
> select guidcol from guidtest where guidcol=@guid
> select GETDATE()
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
I loaded this into a query, then made an asp page to check for one of the
guid's. I am getting an average of 3.5 seconds for this query.

if you would, try this:

first time is time the query started, second time is time it finished. then
it shows the difference.
I am trying to see if where I am located, in respect to the server, makes
that much of a difference.
http://tournaments.acitourneys.info/TIME_TEST1.ASP

Bam wrote:
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:ekuIf2XnIHA.4536@TK2MSFTNGP06.phx.gbl... 
<snip>
> I loaded this into a query, then made an asp page to check for one of
> the guid's. I am getting an average of 3.5 seconds for this query.
>
> if you would, try this:
>
> first time is time the query started, second time is time it
> finished. then it shows the difference.
> I am trying to see if where I am located, in respect to the server,
> makes that much of a difference.
> http://tournaments.acitourneys.info/TIME_TEST1.ASP

Why would that make a difference? I assume you are timing the query
execution in your server-side code. The location of the client is totally
irrelevant.

I strongly suspect that the extra 3+ seconds is due to the time it takes to
send the query to the server and the the time taken to receive the results.
When I tested that script on my machine (using SSMS) I got results
instantaneously - zero time. Were your results similar when you tested it
without asp involved?





--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:et6eXFanIHA.2396@TK2MSFTNGP02.phx.gbl...
> Bam wrote: 
> <snip> 
>
> Why would that make a difference? I assume you are timing the query
> execution in your server-side code. The location of the client is totally
> irrelevant.
>
> I strongly suspect that the extra 3+ seconds is due to the time it takes
> to send the query to the server and the the time taken to receive the
> results. When I tested that script on my machine (using SSMS) I got
> results instantaneously - zero time. Were your results similar when you
> tested it without asp involved?
>
>
yes, when directly on the server, it was real fast. I am doing this as part
of a registration, so I needed to know the time of the delay for an actual
user upon registering.

You have helped me find the answer, and I thank you for that.

Just out of couriosity, when you ran the script you just gave me, how long
did it take to create the table, and post the data to the DB from the
query??

Bam wrote:
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message 
> yes, when directly on the server, it was real fast. I am doing this
> as part of a registration, so I needed to know the time of the delay
> for an actual user upon registering.
>
> You have helped me find the answer, and I thank you for that.
>
> Just out of couriosity, when you ran the script you just gave me, how
> long did it take to create the table, and post the data to the DB
> from the query??

About a minute, maybe a few seconds more.

Err, that's not the part of the query you should be running from ASP ... but
if you are running it and getting the results after inserting 57K records in
3.5 sec. you whould be extremely happy with your server's performance.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23WePI2hnIHA.5692@TK2MSFTNGP03.phx.gbl...
> Bam wrote: 
>
> About a minute, maybe a few seconds more.
>
> Err, that's not the part of the query you should be running from ASP ...
> but if you are running it and getting the results after inserting 57K
> records in 3.5 sec. you whould be extremely happy with your server's
> performance.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
No, the query was done in the DB itself. it took 28 seconds to populate the
fields when I ran it. the 3.5 seconds is based on the asp script finding a
specific GUID from a webpage.