Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <= Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.
> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.
> 3) Is the BETWEEN operator slower than >= and <=> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> >
> > 1) Is it reasonable to expect improved performance by putting the
> > clustered index on the field you most use for RANGE searches.
> Most likely, yes.
> > 2) Is there any reason you wouldn't create a clustered index on a
> > DateTime field, if the data in that field was autogenerated by
> > the server clock and moved only forward chronologically?
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
> > 3) Is the BETWEEN operator slower than >= and <=> > Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> > faster or slower than this ...
> > CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>sql

Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <=
Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.

> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.

> 3) Is the BETWEEN operator slower than >= and <=
> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> Most likely, yes.
>
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
>
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>

Operator is not valid for type 'Date' and type 'Date'

I was wondering if someone could assist me in writing an experession that
would subtract 2 DateTime's. I have 2 DateTime fields and want to display
both in a report and in a third column the difference between the 2 values.
However, I get an error:
"Operator is not valid for type 'Date' and type 'Date'"
Many thanks,
SimonYou can get the difference from SQL by trying something like this in your
store proc or query
SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
hope it helps.
- David
"Simon Dingley" wrote:
> I was wondering if someone could assist me in writing an experession that
> would subtract 2 DateTime's. I have 2 DateTime fields and want to display
> both in a report and in a third column the difference between the 2 values.
> However, I get an error:
> "Operator is not valid for type 'Date' and type 'Date'"
> Many thanks,
> Simon
>
>|||Assuming you have two DateTime fields and you want to determine the
difference in total seconds in the report rather than the query, you can use
an expression like this:
=Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Nevarez" <DavidNevarez@.discussions.microsoft.com> wrote in message
news:33B5D244-F9D5-4ECB-99CB-F9B1B7D9E445@.microsoft.com...
> You can get the difference from SQL by trying something like this in your
> store proc or query
> SELECT date1, date2, DATEDIFF(d, date1, date2) AS date3
> hope it helps.
> - David
> "Simon Dingley" wrote:
> > I was wondering if someone could assist me in writing an experession
that
> > would subtract 2 DateTime's. I have 2 DateTime fields and want to
display
> > both in a report and in a third column the difference between the 2
values.
> > However, I get an error:
> >
> > "Operator is not valid for type 'Date' and type 'Date'"
> >
> > Many thanks,
> >
> > Simon
> >
> >
> >|||Thank You for the replies. I opted for the in-report solution as opposed to
the SQL solution but thanks for both. What I want to return is the
difference in days and hours but can seem to do it I changed the format
string to "d" instead of "s" to return the number of days but when I tried
to use "d h" i get "#error".
Thanks for the help.
Simon
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eVK4OnNsEHA.832@.TK2MSFTNGP10.phx.gbl...
> Assuming you have two DateTime fields and you want to determine the
> difference in total seconds in the report rather than the query, you can
use
> an expression like this:
> =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.

operation not allowed when object is closed...

I have this stored procedure on SQL 2005:

USE [Eventlog]

GO

/****** Object: StoredProcedure [dbo].[SelectCustomerSoftwareLicenses] Script Date: 08/07/2007 16:56:32 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]

(

@.CustomerID char(8)

)

AS

BEGIN

DECLARE @.Temp TABLE (SoftwareID int)

INSERT INTO @.Temp

SELECT SoftwareID FROM Workstations

JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID

WHERE Workstations.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Notebooks

JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID

WHERE Notebooks.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Machines

JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID

WHERE Machines.CustomerID = @.CustomerID

DECLARE @.SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)

INSERT INTO @.SoftwareInstalls

SELECT SoftwareID, COUNT(*) AS Installs FROM @.Temp

GROUP BY SoftwareID

DECLARE @.rowid int

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

WHILE @.rowid > 0 BEGIN

UPDATE SoftwareLicenses

SET Installs = (SELECT Installs FROM @.SoftwareInstalls WHERE rowid = @.rowid)

WHERE SoftwareID = (SELECT SoftwareID FROM @.SoftwareInstalls WHERE rowid = @.rowid)

DELETE FROM @.SoftwareInstalls

WHERE rowid = @.rowid

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

END

SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses

JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID

WHERE SoftwareLicenses.CustomerID = @.CustomerID

ORDER BY Software.Software

END

When i execute it in a Query in SQL Studio it works fine, but when i execute it from an ASP page, i get following error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/administration/licenses_edit.asp, line 56

Here the conection:

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.ConnectionTimeout = Session("ConnectionTimeout")
OBJdbConnection.CommandTimeout = Session("CommandTimeout")
OBJdbConnection.Open Session("ConnectionString")
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")

SQLStmt.CommandText = "EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
RS.Close

Can anyone help please?

It this because of the variable tables?

If I recall correctly, an ADODB recordset is not a disconnected object.

You must do your actions between the OPEN and CLOSE.

Are you using VB v6, or Access?

(.NET allows the use of disconnected data using a dataset -NOT a recordset.)

|||

I'm using VB v6 and SQL Server 2005

I am going through my recordset between the open and close.

I think the problem lies in the scope of the variable table in stored procedure, because if i remove that whole chunk with the variable tables, there are no problems.

I have made the script work in totally different way, so i haven't solved the problem, just worked around it

But it would still be nice to know if it is the scope of the varible tables that is being exceeded, and how, if possible to avoid this...?

|||

Just add "set nocount on" as the first statement in your sproc and your problem should go away.

Code Snippet

ALTER PROCEDURE [dbo].[SelectCustomerSoftwareLicenses]

(

@.CustomerID char(8)

)

AS

set nocount on

BEGIN

DECLARE @.Temp TABLE (SoftwareID int)

INSERT INTO @.Temp

SELECT SoftwareID FROM Workstations

JOIN WorkstationSoftware ON Workstations.WorkstationID = WorkstationSoftware.WorkstationID

WHERE Workstations.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Notebooks

JOIN NotebookSoftware ON Notebooks.NotebookID = NotebookSoftware.NotebookID

WHERE Notebooks.CustomerID = @.CustomerID

UNION ALL

SELECT SoftwareID FROM Machines

JOIN MachinesSoftware ON Machines.MachineID = MachinesSoftware.MachineID

WHERE Machines.CustomerID = @.CustomerID

DECLARE @.SoftwareInstalls TABLE (rowid int identity(1,1), SoftwareID int, Installs int)

INSERT INTO @.SoftwareInstalls

SELECT SoftwareID, COUNT(*) AS Installs FROM @.Temp

GROUP BY SoftwareID

DECLARE @.rowid int

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

WHILE @.rowid > 0 BEGIN

UPDATE SoftwareLicenses

SET Installs = (SELECT Installs FROM @.SoftwareInstalls WHERE rowid = @.rowid)

WHERE SoftwareID = (SELECT SoftwareID FROM @.SoftwareInstalls WHERE rowid = @.rowid)

DELETE FROM @.SoftwareInstalls

WHERE rowid = @.rowid

SET @.rowid = (SELECT COUNT(*) FROM @.SoftwareInstalls)

END

SELECT SoftwareLicenses.SoftwareID, Software.Software, SoftwareLicenses.Licenses, SoftwareLicenses.Installs FROM SoftwareLicenses

JOIN Software ON SoftwareLicenses.SoftwareID = Software.SoftwareID

WHERE SoftwareLicenses.CustomerID = @.CustomerID

ORDER BY Software.Software

END

|||add the following code before "RS.Open SQLStmt"

"Set RS.ActiveConnection = OBJdbConnection"|||

Yes. "SET NOCOUNT ON" will fix your issue. The recordset will not get the resultset from the procedures. Instead of the resultset, the Insert statement's feedback will go.

You can add the "SET NOCOUNT ON" on your sp at first line or you can use the bellow command text,

SQLStmt.CommandText = "SET NOCOUNT ON;EXECUTE SelectCustomerSoftwareLicenses '" & Request("CustomerID") & "'"