Someone Please Help Me!!!!!

  • Thread starter Thread starter Sonya
  • Start date Start date
S

Sonya

I have a SP Problem. When I query this SP in Query analyzer, it completes
in less than 6 seconds with almost 70000 rows in my resultset. When I run
it in VB.NET, it times out and fails.

The connection itself is fine, as it works for other queries that run before
this one in the execution of the application... it only seems to be a
problem with this particular SP.

---------------------------------
SP Code:
---------------------------------

ALTER PROCEDURE LokiServer.sp_GetFilteredData
(@Start DATETIME, @End DATETIME, @FilteredGroups VARCHAR(7998))
AS
/* This procedure will return all rows of information within the
* indicated start and end times if no parameter is passed to
* the procedure.
*/
IF @FilteredGroups = ' '
BEGIN
SELECT * FROM EntityGroup
WHERE grpTime BETWEEN @Start AND @End
ORDER BY grpTime, grpID
SELECT * FROM Entity
WHERE nttTime BETWEEN @Start AND @End
ORDER BY nttTime, nttID
SELECT * FROM PosStat
WHERE posTime BETWEEN @Start AND @End
ORDER BY posTime, nttID
END
ELSE
/* However, if a comma delimited list of group ID's are passed in
* then we will separate them out, and put them into a temporary
* table for use when filtering (by JOIN).
*/
BEGIN
/* Declare the Group ID Table */
DECLARE @GrpIDs TABLE
(grpID TINYINT)

/* Create the table of IDs from the delimited list passed in. */
INSERT INTO @GrpIDs
SELECT substring(',' + @FilteredGroups + ',', Number + 1,
charindex(',', ',' + @FilteredGroups + ',',
Number + 1) - Number - 1)
AS grpID
FROM Numbers
WHERE Number <= len(',' + @FilteredGroups + ',') - 1
AND substring(',' + @FilteredGroups + ',', Number, 1) = ','

/* Retrieve desired information */
SELECT * FROM EntityGroup A
JOIN @GrpIDs B
ON A.grpID = B.grpID
AND A.grpTime BETWEEN @Start AND @End
SELECT * FROM Entity A
JOIN @GrpIDs B
ON A.nttGrpID = B.grpID
AND A.nttTime BETWEEN @Start AND @End
SELECT * FROM PosStat
WHERE posTime BETWEEN @Start AND @End
AND nttID IN (
SELECT A.nttID FROM Entity A
JOIN @GrpIDs B
ON A.nttGrpID = B.grpID)
END
/* Here we can retrieve Status, Flag and Region
* information without any concerns regarding
* filtering
*/
SELECT * FROM Status
WHERE stsTime BETWEEN @Start AND @End
ORDER BY stsTime, stsIdx
SELECT * FROM Flag
WHERE flgTime BETWEEN @Start AND @End
ORDER BY flgTime, flgIdx
SELECT * FROM Region
WHERE rgnTime BETWEEN @Start AND @End
ORDER BY rgnTime, rgnID

--------------------------------------------------
VB.NET Code:
--------------------------------------------------
With _sqlDA
..SelectCommand = Nothing
..SelectCommand = New SqlCommand("LokiServer.sp_GetFilteredData")
..SelectCommand.Connection = _sqlConn
..SelectCommand.CommandType = CommandType.StoredProcedure
..SelectCommand.Parameters.Add("@Start", SqlDbType.DateTime, 8)
..SelectCommand.Parameters("@Start").Direction = ParameterDirection.Input
..SelectCommand.Parameters("@Start").Value = datBeginDate
..SelectCommand.Parameters.Add("@End", SqlDbType.DateTime, 8)
..SelectCommand.Parameters("@End").Direction = ParameterDirection.Input
..SelectCommand.Parameters("@End").Value = datStart.AddSeconds(1)
..SelectCommand.Parameters.Add("@FilteredGroups", SqlDbType.VarChar, 7998)
..SelectCommand.Parameters("@FilteredGroups").Direction =
ParameterDirection.Input
..SelectCommand.Parameters("@FilteredGroups").Value = grpFilter
..MissingSchemaAction = MissingSchemaAction.AddWithKey
..Fill(dsData)
End With
 
Hi Sonya,

As a first step try to run
..SelectCommand.ExecuteReader()
method to see if there is a problem with fetching data.
The other advice might be to look with SqlProfiler what parameters are
passed.
 
Thanks to both of you. I have now succeeded in making it work with a small
adjustment to the timeout. I guess that's all that was needed. It's still
slower in .NET, but it's acceptable.

Thank you again,
Sonya
 
Back
Top