We help IT Professionals succeed at work.

MSSQL Query Time Out

Hello.  Please follow these steps to see an example of my issue.

Go to this site:
http://www.murfreesborotn.gov/search.aspx

Enter "Evergreen Farms" in the "Owner Name" field, and click search

A list of addresses and owners will come up.  Click on the second page of this list, and you will see the query time out.  (It will redirect you to the home page of the site)

I'm attaching the query, aspx page, and a screen shot of my indexes on the table.

I would prefer not to increase the query timeout value if it's possible to avoid that.  It seems strange to me that the first page of data loads fine, but the subsequent pages are causing a timeout error.  How can I correct this issue? Thanks for reading.
USE [proptax]
GO
/****** Object:  StoredProcedure [dbo].[sp_TaxRec]    Script Date: 12/05/2011 16:15:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_TaxRec]
@name1 varchar(26),
@property_add varchar(26),
@ctlmap varchar(5),
@bill_group char(2),
@bill_parcel varchar(5),
@propertyid char(5),
@special char(3),
@bill_year int,
@bill_no int,
@Account varchar(20)
AS
 
 
SELECT  distinct replace(rtrim(ltrim(name1)),'  ',' ') AS Name,
  replace(rtrim(ltrim(property_add)),'  ',' ') AS Address,
  RTRIM(map) + '-' + RTRIM(bill_group) + '-' + RTRIM(ctlmap) + '-' + RTRIM(bill_parcel) + '-' + RTRIM(propertyid) + '-' + special AS PIN,
  bill_group,
  ctlmap,
  bill_parcel,
  propertyid,
  special,
  Account
 
FROM MUR_bill_master
 
WHERE (name1 LIKE  isnull(@name1,'') + '%')
and (property_add like  isnull(@property_add,'') +'%' )

and ctlmap = (case when isnull(@ctlmap,'')='' then ctlmap else @ctlmap end)
and bill_group = (case when isnull(@bill_group,'')='' then bill_group else @bill_group end)
and bill_parcel = (case when isnull(@bill_parcel,'')='' then bill_parcel else @bill_parcel end)
and propertyid = (case when isnull(@propertyid,'')='' then propertyid else @propertyid end)
and special = (case when isnull(@special,'')='' then special else @special end)
and bill_no = (case when isnull(@bill_no,0)=0 then bill_no else @bill_no end)
and Account = (case when isnull(@Account,'')='' then Account else @Account end)

and bill_year = (case when isnull(@bill_year,0)=0 
                      then (select max(bill_year) FROM MUR_bill_master B where /*B.property_add = MUR_bill_master.property_add and*/ B.map = MUR_bill_master.map and  B.bill_group = MUR_bill_master.bill_group and  B.ctlmap = MUR_bill_master.ctlmap and  B.bill_parcel = MUR_bill_master.bill_parcel and  B.propertyid = MUR_bill_master.propertyid  )
                      else @bill_year end)

Open in new window

taxrecords.aspx
index.JPG
Comment
Watch Question

AnujSQL Server DBA
Top Expert 2011

Commented:
First make sure that the table has clustered index,

Looks like the query optimizer is doing a scan if the values for the variables are null, so replace your where clause with the following

WHERE (name1 LIKE  isnull(@name1,'') + '%')
and (property_add like  isnull(@property_add,'') +'%' )

AND CASE WHEN  @ctlmap IS NULL THEN '0' ELSE   ctlmap END  = ISNULL(@ctlmap,'0' )
AND CASE WHEN  @bill_group IS NULL THEN '0' ELSE   bill_group END  = ISNULL(@bill_group,'0' )
AND CASE WHEN  @bill_parcel IS NULL THEN '0' ELSE   bill_parcel END  = ISNULL(@bill_parcel,'0' )
AND CASE WHEN  @propertyid IS NULL THEN '0' ELSE   propertyid END  = ISNULL(@propertyid,'0' )
AND CASE WHEN  @special IS NULL THEN '0' ELSE   special END  = ISNULL(@special,'0' )
AND CASE WHEN  @bill_no IS NULL THEN '0' ELSE   bill_no END  = ISNULL(@bill_no,'0' )
AND CASE WHEN  @Account IS NULL THEN '0' ELSE   Account END  = ISNULL(Account,'0' )
and bill_year = (case when isnull(@bill_year,0)=0
                      then (select max(bill_year) FROM MUR_bill_master B where /*B.property_add = MUR_bill_master.property_add and*/ B.map = MUR_bill_master.map and  B.bill_group = MUR_bill_master.bill_group and  B.ctlmap = MUR_bill_master.ctlmap and  B.bill_parcel = MUR_bill_master.bill_parcel and  B.propertyid = MUR_bill_master.propertyid  )
                      else @bill_year end)

Also make sure that the columns in the where clause is indexed,

Author

Commented:
Thanks for the fast reply! I changed my indexes to clustered.  I tried to implement the clause you suggested, but after I switched to it, the search would not produce any results. I'm starting to think that this problem may not be in the query itself.  The first page of results loading fine seems to suggest that the query is fine, and that maybe is wrong elsewhere.   Am I totally off-base on that?
Most Valuable Expert 2012
Top Expert 2014

Commented:
What happens if you comment out the code in SelectedIndexChanged event?
AnujSQL Server DBA
Top Expert 2011

Commented:
can you check with this

WHERE (name1 LIKE  isnull(@name1,'') + '%')
and (property_add like  isnull(@property_add,'') +'%' )

AND (ctlmap = @ctlmap OR @ctlmap  IS NULL)
AND (bill_group = @bill_group OR @bill_group IS NULL)
AND (bill_parcel = @bill_parcel OR @bill_parcel IS NULL )
AND (propertyid = @propertyid OR  @propertyid  IS NULL)
AND (special = @special  OR @special  IS NULL)
AND (bill_no = @bill_no  OR @bill_no  IS NULL)
AND (Account = @Account OR @Account  IS NULL )
and bill_year = (case when isnull(@bill_year,0)=0
                      then (select max(bill_year) FROM MUR_bill_master B where /*B.property_add = MUR_bill_master.property_add and*/ B.map = MUR_bill_master.map and  B.bill_group = MUR_bill_master.bill_group and  B.ctlmap = MUR_bill_master.ctlmap and  B.bill_parcel = MUR_bill_master.bill_parcel and  B.propertyid = MUR_bill_master.propertyid  )
                      else @bill_year end)

Author

Commented:
I commented everything in the event, and still the same result.  As a test, I removed the paging just to see if all the records would load ok without it, and they did.  
Most Valuable Expert 2012
Top Expert 2014

Commented:
So nothing wrong with query or sql server. Problem seems to be in your grid configuration.

Author

Commented:
No luck with that clause either.  The results returned, but same problem with paging.

Author

Commented:
That's what I'm thinking.  I thought maybe it was because I was using post data for my select parameters, so I switched it over to session data.  Still no luck.  
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
please check this out: parameter sniffing:
http://www.simple-talk.com/content/article.aspx?article=1142

Author

Commented:
That looks really promising.  Thanks!  
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.