By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,982 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,982 IT Pros & Developers. It's quick & easy.

Table not optimized or what ?

P: n/a
MAB
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
MAB (IV**************@yahoo.com) writes:
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


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
MAB

Erland Sommarskog <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...
MAB (IV**************@yahoo.com) writes:
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


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.


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

Jul 20 '05 #3

P: n/a
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:

Erland Sommarskog <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...
MAB (IV**************@yahoo.com) writes:
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


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.


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

Jul 20 '05 #4

P: n/a
MAB (IV**************@yahoo.com) writes:

CREATE TABLE dbo.Aug2003Calls (
...
)
GO

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

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


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.