We have a table which contains about 10,000,000 rows and has the
following structure:
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TRANHEADERS](
[StoreNum] [varchar](20) NOT NULL,
[Location] [int] NOT NULL,
[TillID] [int] NOT NULL,
[TranID] [int] NOT NULL,
[DtStamp] [varchar](14) NOT NULL,
[StopTime] [varchar](14) NULL,
[Typet] [int] NULL,
[SubTypet] [int] NULL,
[Customer] [varchar](20) NULL,
[SecondaryCustomer] [varchar](20) NULL,
[HasHoldingTank] [tinyint] NULL
CONSTRAINT [PK_TRANHEADERS_1] PRIMARY KEY CLUSTERED
(
[StoreNum] ASC,
[Location] ASC,
[TillID] ASC,
[TranID] ASC,
[DtStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
And has an Index key for DtStamp:
CREATE NONCLUSTERED INDEX [Dates] ON [dbo].[TRANHEADERS]
(
[DtStamp] ASC,
[StoreNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
If we query the table using the following procedure we got time out message
in 3 minutes:
public DataSet LoadHoldingTankItems(string StartDate, string
EndDate, string StoreNum)
{
DataSet WorkDs;
DbCommand dbCommand;
StringBuilder strBuilder = new StringBuilder("");
strBuilder.Append(" set rowcount 1 ");
strBuilder.Append( " Select * from tranheaders with (noLock) where DtStamp
>= @StartDate And DtStamp <= @EndDate And hasholdingtank = 1 ");if (string.Compare(StoreNum, "", true) != 0)
strBuilder.Append(" And StoreNum = @StoreNum");
strBuilder.Append(" set rowcount 0 ");
string sqlStr = strBuilder.ToString();
dbCommand = db.GetSqlStringCommand(sqlStr);
db.AddInParameter(dbCommand, "StartDate", DbType.String, StartDate);
db.AddInParameter(dbCommand, "EndDate", DbType.String, EndDate);
if (string.Compare(StoreNum, "", true) != 0)
db.AddInParameter(dbCommand, "StoreNum", DbType.String, StoreNum);
dbCommand = db.GetSqlStringCommand(sqlStr);
try
{
WorkDs = db.ExecuteDataSet(dbCommand);
}
catch (Exception ex)
{
StarLinkLibary.LogError("TranHeadersDataLayer", 0,
ex.ToString());
throw;
}
return WorkDs;
}
If we change the procedure to:
public DataSet LoadHoldingTankItems(string StartDate, string
EndDate, string StoreNum)
{
DataSet WorkDs;
DbCommand dbCommand;
//I don't know why the below query have problem with speed We
have to find out
//why before uncomment these section
/* StringBuilder strBuilder = new StringBuilder("");
strBuilder.Append(" set rowcount 1 go ");
strBuilder.Append( " Select * from tranheaders with (noLock)
where DtStamp >= @StartDate And DtStamp <= @EndDate And hasholdingtank = 1
");
if (string.Compare(StoreNum, "", true) != 0)
strBuilder.Append(" And StoreNum = @StoreNum");
strBuilder.Append(" set rowcount 0 go ");
string sqlStr = strBuilder.ToString();
dbCommand = db.GetSqlStringCommand(sqlStr);
db.AddInParameter(dbCommand, "StartDate", DbType.String,
StartDate);
db.AddInParameter(dbCommand, "EndDate", DbType.String, EndDate);
if (string.Compare(StoreNum, "", true) != 0)
db.AddInParameter(dbCommand, "StoreNum", DbType.String,
StoreNum);*/
string sqlStr = "set rowcount 1 ";
sqlStr = sqlStr + " Select * from tranheaders with (noLock)
where DtStamp >= '" + StartDate + "' ";
sqlStr = sqlStr + " And DtStamp <= '" + EndDate + "' And
hasholdingtank = 1 ";
if (string.Compare(StoreNum, "", true) != 0)
sqlStr = sqlStr + " And StoreNum = '" + StoreNum + "'";
sqlStr = sqlStr + " set rowcount 0";
dbCommand = db.GetSqlStringCommand(sqlStr);
try
{
WorkDs = db.ExecuteDataSet(dbCommand);
}
catch (Exception ex)
{
StarLinkLibary.LogError("TranHeadersDataLayer", 0,
ex.ToString());
throw;
}
return WorkDs;
}
The above procedure will return a data set in one second.
I can not see the different between 2 procedures any one run into the same
problem before please help.
