I have two tables in SQL 6.5 database with identical fields and indexes. One
contains the data of August 2003 and other July 2003. Now the august table
is larger ( about 40000 more rows ) than the july table but i've noticed
that the same queries perform much faster on the august table than the july
table. Ive tried this with many different queries so i'm wondering whats the
reason behind this. Is there a way to optimize a table? Remember , I'm using
SQL 6.5


thx

MAB (IVZXKLCJFSFLSDFA (AT) yahoo (DOT) com) writes:
Without seeing the CREATE TABLE and CREATE INDEX statemetns for the tables
it is difficult to tell. But one possibilty is that the July table suffers
more from fragmentation. DBCC SHOWCONTIG can give some information. With
DBCC DBREINDEX you can rebuild the indexes to reduce fragmentation.


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog <sommar (AT) algonet (DOT) se> wrote

CREATE TABLE dbo.Aug2003Calls (
NASIdentifier varchar (16) NOT NULL ,
NASPort int NOT NULL ,
AcctSessionID varchar (10) NOT NULL ,
AcctStatusType tinyint NOT NULL ,
CallDate smalldatetime NOT NULL ,
UserName varchar (32) NOT NULL ,
UserService tinyint NULL ,
AcctDelayTime int NULL ,
AcctSessionTime int NULL ,
FramedProtocol int NULL ,
FramedAddress varchar (16) NULL ,
AcctInputOctets int NULL ,
AcctOutputOctets int NULL ,
AcctTerminateCause tinyint NULL ,
NASPortType tinyint NULL ,
NASPortDNIS varchar (10) NULL ,
CallerId varchar (16) NULL ,
FramedCompression int NULL ,
FramedNetmask int NULL ,
ModemSlotNo int NULL ,
ModemPortNo int NULL ,
XmitRate int NULL ,
AscendDataRate int NULL ,
AscendDisconnectCause int NULL ,
AscendConnectProgress int NULL ,
AcctLinkCount int NULL ,
AscendModemShelfNo int NULL
)
GO

CREATE INDEX idxAug2003Calls ON dbo.Aug2003Calls(CallDate, UserName)
GO


CREATE TABLE dbo.Jul2003Calls (
NASIdentifier varchar (16) NOT NULL ,
NASPort int NOT NULL ,
AcctSessionID varchar (10) NOT NULL ,
AcctStatusType tinyint NOT NULL ,
CallDate smalldatetime NOT NULL ,
UserName varchar (32) NOT NULL ,
UserService tinyint NULL ,
AcctDelayTime int NULL ,
AcctSessionTime int NULL ,
FramedProtocol int NULL ,
FramedAddress varchar (16) NULL ,
AcctInputOctets int NULL ,
AcctOutputOctets int NULL ,
AcctTerminateCause tinyint NULL ,
NASPortType tinyint NULL ,
NASPortDNIS varchar (10) NULL ,
CallerId varchar (16) NULL ,
FramedCompression int NULL ,
FramedNetmask int NULL ,
ModemSlotNo int NULL ,
ModemPortNo int NULL ,
XmitRate int NULL ,
AscendDataRate int NULL ,
AscendDisconnectCause int NULL ,
AscendConnectProgress int NULL ,
AcctLinkCount int NULL ,
AscendModemShelfNo int NULL
)
GO

CREATE INDEX idxJul2003Calls ON dbo.Jul2003Calls(CallDate, UserName)
GO

With SQL 6.5, it is very important that you create a clustered index on
each table. In this case, you could change your CREATE INDEX ... into
CREATE CLUSTERED INDEX ...

Without a clustered index, running DBCC DBREINDEX will not eliminate the
(possible) fragmentation.

Hope this helps,
Gert-Jan


MAB wrote:

MAB (IVZXKLCJFSFLSDFA (AT) yahoo (DOT) com) writes:
I can only echo Gert-Jan's suggestion to add a clustered index. Overall
a single non-clustered on a table which appears to be used for statistical
purposes appears to me overly modest.


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp