CODEWITHSUNDEEP
asp.netsqlsql-server
opticon
opticon

Reputation: 3614

SQL Search running slowly, only in browser

I've got a small search function on a page of mine. 

IF LEN(@firstName) < 1 OR @firstName IS NULL
set @firstName = '%'    


IF LEN(@lastName) < 1 OR @lastName IS NULL
set @lastName = '%'

IF LEN(@carrierId) < 1 OR @carrierId IS NULL
set @carrierId = '%'


IF LEN(@worksheetId) < 1 OR @worksheetId IS NULL
set @worksheetId = '%'  

SELECT TOP 1000 m.[Guid], m.WorksheetID, d.FirstName, d.LastName, m.VersionNum FROM Main m
INNER JOIN utl_Driver d ON m.DriverID = d.DriverID
    WHERE d.FirstName LIKE @firstName
    AND d.LastName LIKE @lastName
    AND d.CarrierID LIKE @carrierId
    AND m.WorksheetID LIKE @worksheetId
    AND m.Deleted = 0
    AND d.VersionNum = (SELECT MAX(VersionNum)
                            FROM Main B WHERE b.WorksheetID = m.WorksheetID)
    ORDER BY m.DateTimeStart DESC;

It's lightning quick in SQL Server Management Studio, but performs oddly when the stored procedure is called by my ASP.Net application. Searching via LastName is usually quick, but FirstName or WorksheetID times out. Moreover, erasing the 'm.Deleted = 0' check makes all queries execute very quickly, but I obviously need that check. Any advice?

Upvotes: 0

Views: 89

Answers (2)

Elias Hossain
Elias Hossain

Reputation: 4469

Would you please try as below: thanks for your time.

DECLARE @whereCondition NVARCHAR(1000)
SET @whereCondition = ' WHERE 1 = 1 '

IF @firstName <> '' AND @firstName IS NOT NULL
    SET @whereCondition = @whereCondition + ' AND d.FirstName LIKE  ''' + @firstName + ''''

IF @lastName <> '' AND @lastName IS NOT NULL
    SET @whereCondition = @whereCondition + ' AND  d.LastName LIKE  ''' + @lastName + ''''

IF @carrierId <> '' AND @carrierId<> 0 AND @carrierId IS NOT NULL
    SET @whereCondition = @whereCondition + ' AND  d.CarrierID LIKE  ''' + @carrierId + ''''

IF @worksheetId <> '' AND @worksheetId<> 0  AND @worksheetId IS NOT NULL
    SET @whereCondition = @whereCondition + ' AND  m.WorksheetID LIKE  ''' + @worksheetId + ''''

SET @whereCondition = @whereCondition + ' AND  m.Deleted = 0'
SET @whereCondition = @whereCondition + ' AND  d.VersionNum = (SELECT MAX(VersionNum)
                            FROM Main B WHERE b.WorksheetID = m.WorksheetID) '

EXEC('SELECT TOP 1000 m.[Guid], m.WorksheetID, d.FirstName, d.LastName, m.VersionNum FROM Main m
       INNER JOIN utl_Driver d ON m.DriverID = d.DriverID '
       + @whereCondition +
       ' ORDER BY m.DateTimeStart DESC; ' )

Upvotes: 1

Jason Meckley
Jason Meckley

Reputation: 7591

this query will never preform well. you are doing table scans across 4 different fields. I also don't see any paging in the query which means all results are returned. this will be a problem with large set of data.

introduce webforms and now you have viewstate. a grid/list view will store the entire result set in viewstate (even if you enable client paging).

these are 3 main bottlenecks I see. 1. poor query performance due to "like" operator 2. result set may be too large 3. webforms view state

Upvotes: 1

Related Questions