I have a web site that uses ASP. I want to know if a record exists for
a specific date. I will be doing this 30 times for each refresh of the
page.
What I've setup is a simple SP that takes a date as an input and
returns a count.
CREATE PROCEDURE spGetNumber
(
@.Date smalldatetime
)
AS
SET NOCOUNT ON
DECLARE @.Count tinyint
SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
RETURN @.Count
--
In my ASP, I'm calling a command object.
It seems pretty fast, but I only have 100 records in the table, 1 user
at a time. In the future I expect 100's of users and 10,000's of
records.
Is this the best method?
thx,
Bodihi bodi
u can continue using the SP. it is advisible to contact the database only
once from the ASP page instead of contacting 30 times
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"BodiKlamph@.gmail.com" wrote:
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||On 7 Aug 2005 11:18:46 -0700, BodiKlamph@.gmail.com wrote:
>Hi,
>I have a web site that uses ASP. I want to know if a record exists for
>a specific date. I will be doing this 30 times for each refresh of the
>page.
Hi Bodi,
Issuing 30 consecutive calls is not a good idea. If the sole purpose is
to find the dates for which no row exists, rewrite the stored procedure
to take two parameters (starting and ending date) and return a resultset
of all dates for which no rows exist.
>What I've setup is a simple SP that takes a date as an input and
>returns a count.
Another thing: if you only need to verify existence, use EXISTS, not
COUNT. A query with COUNT will always continue until all matching rows
are found. A query with EXISTS will stop after the first matching row.
>It seems pretty fast, but I only have 100 records in the table, 1 user
>at a time. In the future I expect 100's of users and 10,000's of
>records.
10,000's of rows is not very much for SQL Server, but still - why incur
extra overhead that can easily be avoided.
>Is this the best method?
The best method involves a calendar table. If you don't have one yet in
your database, go ahead and create it now. Instructions for making a
calendar table and many examples of how you can use one are at this
site: http://www.aspfaq.com/show.asp?id=2519.
Remember that you only need to create the calendar table once! Just
don't forget to add some new rows every year or two.
Once you have a calendar table, you can find the dates without row in
your Race table with the following stored procedure:
CREATE PROC FindDatesWithoutRowInRace
(@.StartDate datetime,
@.EndDate datetime)
AS
SELECT c.dt
FROM dbo.Calendar AS c
WHERE c.dt >= @.StartDate
AND c.dt <= @.EndDate
AND NOT EXISTS
(SELECT *
FROM Race AS r
WHERE e.[Date] = c.dt)
go
The final step would be to change the calling code to issue one call
with two parameters, then fetch and process the rows returned by the
stored procedure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||A couple things in addition to Chandra's and Hugo's posts...
The return value from an SP is intended to be a success/failure indicator,
like a windows app - returning non-zero indicates failure, so when you get a
count > 0 and return it you're indicating that the SP failed.
If you want a count of matching records you should use an output parameter;
if you simply want to test for existance you should use EXISTS like Hugo
said, and set the output BIT to 1...
Another note, with COUNT - generally you want to use COUNT(*) not
COUNT([field_name]) because then SQL can optimize to use the best index
rather than have to use an index or constraint (assuming one exists) with th
e
specified field.
I'm going to go out on a limb and guess that you're trying to display a
calendar, hence the 30 calls per request. For this scenario you should retur
n
records that fall in a specific date range:
SELECT DISTINCT [date]
FROM [table_owner].Race
WHERE [date] BETWEEN @.lo_date AND @.hi_date
Then populate the calendar from the recordset in your ASP. Rememebr that
datetime includes time, so if you want records thru the end of the last date
specified then you'll have to be sure either all dates in the table have
00:00:00.000 for the time portion or @.lo_date has time 00:00:00.000 and
@.hi_date has time 23:59:59.997.
Be sure your command objkect's type is set to stored procedure, not text
query, for fastest execution.
Good luck, - KH
"BodiKlamph@.gmail.com" wrote:
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||As above...
But think in terms of SETS of data.
What is the ideal SET of data that the web application could get - KH has
already suggested "the set of dates where there are no races". This may not
be ideal for your real needs, but is definitely a substantial improvement on
making multiple calls that s
out many times singular Yes / No answers -which aren't sets at all, they are simple questions.
- Tim
<BodiKlamph@.gmail.com> wrote in message
news:1123438726.697862.102120@.g43g2000cwa.googlegroups.com...
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||In addition to the already mentioned ideas:
If you are always making exactly 30 calls, do the different values of date
have a relationship to one another?
For example, using a resultset instead of the return output:
SELECT sum (case when date = @.date then 1 else 0 end) as first,
sum(case when date = dateadd(minute,-1,@.date) then 1 else 0 end)
as second
...
sum(case when date = dateadd(minute,N-1,@.date) then 1 else 0 end)
as Nth
FROM race
WHERE date between @.date and dateadd(minute,N-1,@.date)
or something along these lines and it will probably be faster. Even doing
thirty parms and then putting the thirty sums would likely be better because
of networkIO type stuff.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
<BodiKlamph@.gmail.com> wrote in message
news:1123438726.697862.102120@.g43g2000cwa.googlegroups.com...
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||thx Hugo, your answer seems to best suite my needs.
Yes, 30 calls is quite bad. Returning a recordset with just hte valid
dates is a much better idea. then i can loop in my asp code and add
only the valid calendar days.
question? wut's up with the calendar table you mentioned; why would I
need that?
Can't I just do:
SELECT DISTINCT [Date] FROM myTable WHERE [Date] >= startDate and
[Date] <= endDate
that would return only the valid dates|||K, I finished making the changes.
2 questions.
I have a loop that writes out my calendar days (part of an ASP calendar
class). For each day, i want to see if there is a corresponding record
in the resultset I do this by an inefficeint (tha'ts why i'm here)
recordset.find method. I have to search from record 1 each time tho
This is probaly the wrong group now, since it's more ADO than SQL, but
I'll give it a host since you're already familiar with my question.
I was using set rs = cmd.execute, but it compalined that my recordset
was forward only. So I cahnged it form a SP to a simple recordset.open
(using dbopendynamic so i could use the .find method).
I was thinking it may be easier to use GetRows() then loop through the
much less bulky array. Not sure how I'd do this yet, but I can think
of something...unless there is already a set way of doing this?
thx,
Bodi|||lol
at this point I'm just talking to myself.
I used the GetString method, with a row deliminator of comma
then, instead of .find, I did:
if instr(rsDays, "," & DAY(DateString) & ",")
works like a charm, and i imagine it's way faster to do a string check
then it is a .find
thx everybody|||On 8 Aug 2005 21:13:48 -0700, BodiKlamph@.gmail.com wrote:
>thx Hugo, your answer seems to best suite my needs.
>Yes, 30 calls is quite bad. Returning a recordset with just hte valid
>dates is a much better idea. then i can loop in my asp code and add
>only the valid calendar days.
>question? wut's up with the calendar table you mentioned; why would I
>need that?
>Can't I just do:
>SELECT DISTINCT [Date] FROM myTable WHERE [Date] >= startDate and
>[Date] <= endDate
>that would return only the valid dates
Hi Bodi,
I must have misunderstood you earlier. I thought you wanted to find the
dates that are not yet in your table. The query above will do the
reverse: find the dates that are (at least once) in the table.
But do remember that a datetime column includes a time part as well. If
you don't ensure that the time part is the same for all entries, the
query above might return the same date several times, since the DISTINCT
will look at the time part as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
No comments:
Post a Comment