asked on

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

Anuj

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,

thedeal56

ASKER

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?

Nasir Razzaq

What happens if you comment out the code in SelectedIndexChanged event?

Anuj

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)

thedeal56

ASKER

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.

Nasir Razzaq

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

thedeal56

ASKER

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

thedeal56

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thedeal56

ASKER

That looks really promising. Thanks!

Amandeep Singh Bhullar

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.