Time out

L

Le Hung

Please help!
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.
 
A

Alberto Poblacion

Le Hung said:
[...]
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.

The only difference that I see between both querys is that the "slow" one
is parameterized while the second one is not. The difference in speed could
be in Sql Server: The parameterized query is optimized once and then stored
in the procedure cache, so that every time you send the same query, even
though the values of the parameters are different, it follows the same
strategy. On the other hand, the non-parameterized version is optimized
every time, which will be a little less efficient, but will always choose
the best index according to the current index statistics.
If your database statistics are not up-to-date, or you first executed the
parameterized query with a set of parameters that happened to provide
optimal performance with a full scan (according to the existing statistics
at that time), the parameterized query might have been optimized for a full
clustered index scan.
I recommend that you UPDATE STATISTICS and then clear the procedure cache
(DBCC FREEPROCCACHE) so that your query will be recompiled.

By the way, your indexing strategy is probably suboptimal, at least for
this query: You have a very wide clustered index, which needs to be appended
to every other index, making those querys slower. You would probably benefit
from changing the primary key to non-clustered.
 
I

Ignacio Machin ( .NET/ C# MVP )

have you tried the query resulting in both cases?
I mean directly from sql enterprise manager and see the results?

also I remember seeing in MSDN magazine an article about how to detect
missing indices, expensive queriers, etc in a number this year, might
be worth to take a look into it
 
L

Le Hung

Ignacio Machin ( .NET/ C# MVP ) said:
have you tried the query resulting in both cases?
I mean directly from sql enterprise manager and see the results?

also I remember seeing in MSDN magazine an article about how to detect
missing indices, expensive queriers, etc in a number this year, might
be worth to take a look into it
Thanks Ignacio:
Both work ok from SQL enterprise. When I removed Clustered Index as
Alberto suggested then both procedures work now(same response time). I am
not sure why the clustered index can slow down the query that much.
Thanks
 
L

Le Hung

Alberto Poblacion said:
Le Hung said:
[...]
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.

The only difference that I see between both querys is that the "slow" one
is parameterized while the second one is not. The difference in speed could
be in Sql Server: The parameterized query is optimized once and then stored
in the procedure cache, so that every time you send the same query, even
though the values of the parameters are different, it follows the same
strategy. On the other hand, the non-parameterized version is optimized
every time, which will be a little less efficient, but will always choose
the best index according to the current index statistics.
If your database statistics are not up-to-date, or you first executed the
parameterized query with a set of parameters that happened to provide
optimal performance with a full scan (according to the existing statistics
at that time), the parameterized query might have been optimized for a full
clustered index scan.
I recommend that you UPDATE STATISTICS and then clear the procedure cache
(DBCC FREEPROCCACHE) so that your query will be recompiled.

By the way, your indexing strategy is probably suboptimal, at least for
this query: You have a very wide clustered index, which needs to be appended
to every other index, making those querys slower. You would probably benefit
from changing the primary key to non-clustered.

Thanks Alberto,
I tried UPDATE STATISTICS and DBCC FREEPROCCACHE but the program still got
time out. When I drop the clustered index then the program works ok.

Thanks again

Le Hung
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top