?
Solved

Perplexing SQL Server Query Performance from one server to another - counter intutive.  Pls help!

Posted on 2011-10-11
12
Medium Priority
?
397 Views
Last Modified: 2012-05-12
I am running the code below on three different servers, DEV and QA and Production, on an exact copy of the same database (backup of).  In DEV it runs under 30 seconds (see stats below the code).  In QA and Production it takes 4 to 5 minutes.

In DEV, it looks like this:
--SQL Server Execution Times: CPU time = 26202 ms,  elapsed time = 18614 ms.

However, in QA the following is the case:
SQL Server Execution Times: CPU time = 213608 ms,  elapsed time = 279405 ms.

In Prod the Execution time is similar to DEV except that the elapsed time is an order of magnitude higher (below)
SQL Server Execution Times: CPU time = 26359 ms,  elapsed time = 282784 ms.

What is more perplexing is that in Production, it also takes around 5 minutes to run!  The production server has 30 GB RAM, 8 processors, ...

I have spent a lot of time trying to figure out what the reason is ...
- run sp_configure and compared the running values, match
- both servers are VMware ... same amount of RAM, CPU, etc.
- before I start running the script perfmon shows CPU util at under 15%, Avg Disk Queue, 1 or 2

When I run the query, CPU util jumps to 60 70+ % , Avg Disk Queue Length goes up to 150 and the query takes up to 5 minutes to complete.



The only difference on the two servers is that the ProductVersion on DEV is 9.00.4060.00 and QA and the rest of the servers are 9.00.4053.00 All servers are SQL Server 2005 with SP3 on Windows Server 2003 64 bit SP2

QUESTION 1
Can the ProductVersion account for this kind of astronomical perf diff?  What does the 4060 vs 4053 mean?

QUESTION 2
What can possibly account for these differences

QUESTION 3
What can I do to optimize this code?  (after I find an explanation for this problem).

The code and the stats from the different runs on the different servers is below.

I will really appreciate your help.

Thanks,
Tes

--
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--
SET STATISTICS IO ON
SET STATISTICS TIME ON
--
CHECKPOINT
--
use mls_new
go
--
DECLARE @request xml
DECLARE @latitudeMax float
DECLARE @latitudeMin float
DECLARE @longitudeMax float
DECLARE @longitudeMin float
DECLARE @agentUsername varchar(30)
DECLARE @firmCode varchar(30)
DECLARE @areas table
(
AreaID int
, Name varchar(200)
)
--
--
select @Request = '<searchRequest><header><requestType>ListingSearch</requestType></header><zipCode>91342</zipCode><propertyType><type>0</type></propertyType><listingStatus><status>5</status></listingStatus></searchRequest>'
--
--INSERT INTO ListingSearchIndex
-- (RequestID, SearchKey)
SELECT 470 , svo.MlsNum
FROM
mls_unified_svo_tbl svo (nolock)
WHERE
svo.LoadOnInternet = 1
AND svo.std IS NOT NULL
AND svo.snd IS NOT NULL
AND (svo.Status IN (5, 30, 45)
OR
(svo.Status IN (10, 20) AND svo.StatusDateDiffToday < 365))
-- DATEDIFF(day, svo.StatusDate, GETDATE()) < 365
AND
CASE WHEN @request.exist('/searchRequest/mlsNumber') = 0
THEN 1
ELSE
CASE WHEN svo.MlsNum = @request.value('(/searchRequest/mlsNumber)[1]', 'varchar(20)') THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/zipCode') = 0
THEN 1
ELSE
CASE WHEN svo.zip = @request.value('(/searchRequest/zipCode)[1]', 'varchar(500)') THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/city') = 0
THEN 1
ELSE
CASE WHEN svo.city = @request.value('(/searchRequest/city)[1]', 'varchar(50)') THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/address') = 0
THEN 1
ELSE
CASE WHEN SUBSTRING(svo.address, 1, LEN(@request.value('(/searchRequest/address)[1]', 'varchar(100)'))) = @request.value('(/searchRequest/address)[1]', 'varchar(100)') THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/listingArea') = 0
THEN 1
ELSE
CASE WHEN svo.ar IN (SELECT AreaID FROM @areas) OR  svo.city IN (SELECT Name FROM @areas) THEN 1
ELSE 0
END
END = 1
AND
CASE
WHEN @latitudeMin IS NULL OR @latitudeMax IS NULL OR @longitudeMin IS NULL OR @longitudeMax IS NULL THEN 1
ELSE
CASE WHEN svo.Latitude BETWEEN @latitudeMin AND @latitudeMax
AND svo.Longitude BETWEEN @longitudeMin AND @longitudeMax THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/price/low') = 0
THEN 1
ELSE
CASE WHEN svo.lp >= @request.value('(/searchRequest/price/low)[1]', 'bigint') THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/price/high') = 0
THEN 1
ELSE
CASE WHEN svo.lp <= @request.value('(/searchRequest/price/high)[1]', 'bigint') THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/yearBuilt/low') = 0
THEN 1
ELSE
CASE WHEN svo.yb >= @request.value('(/searchRequest/yearBuilt/low)[1]', 'smallint') THEN 1
ELSE 0
END
END = 1
AND
CASE WHEN @request.exist('/searchRequest/yearBuilt/high') = 0
THEN 1
ELSE
CASE WHEN svo.yb <= @request.value('(/searchRequest/yearBuilt/high)[1]', 'smallint') THEN 1
ELSE 0
END
END = 1

AND

CASE WHEN @request.exist('/searchRequest/sqFootage/low') = 0

THEN 1

ELSE

CASE WHEN svo.sf >= @request.value('(/searchRequest/sqFootage/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/sqFootage/high') = 0

THEN 1

ELSE

CASE WHEN svo.sf <= @request.value('(/searchRequest/sqFootage/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bed/low') = 0

THEN 1

ELSE

CASE WHEN svo.br >= @request.value('(/searchRequest/bed/low)[1]', 'smallint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bed/high') = 0

THEN 1

ELSE

CASE WHEN svo.br <= @request.value('(/searchRequest/bed/high)[1]', 'smallint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bath/low') = 0

THEN 1

ELSE

CASE WHEN svo.ba >= @request.value('(/searchRequest/bath/low)[1]', 'decimal') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bath/high') = 0

THEN 1

ELSE

CASE WHEN svo.ba <= @request.value('(/searchRequest/bath/high)[1]', 'decimal') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/parking/low') = 0

THEN 1

ELSE

CASE WHEN svo.pkg_num >= @request.value('(/searchRequest/parking/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/parking/high') = 0

THEN 1

ELSE

CASE WHEN svo.pkg_num <= @request.value('(/searchRequest/parking/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/lotSize/low') = 0

THEN 1

ELSE

CASE WHEN svo.lsz >= @request.value('(/searchRequest/lotSize/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/lotSize/high') = 0

THEN 1

ELSE

CASE WHEN svo.lsz <= @request.value('(/searchRequest/lotSize/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/soldPrice/low') = 0

THEN 1

ELSE

CASE WHEN svo.sp >= @request.value('(/searchRequest/soldPrice/low)[1]', 'bigint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/soldPrice/high') = 0

THEN 1

ELSE

CASE WHEN svo.sp <= @request.value('(/searchRequest/soldPrice/high)[1]', 'bigint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/daysInStatus/low') = 0

THEN 1

ELSE

CASE WHEN DATEDIFF(d, svo.statusdate, GETDATE()) >= @request.value('(/searchRequest/daysInStatus/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/daysInStatus/high') = 0

THEN 1

ELSE

CASE WHEN DATEDIFF(d, svo.statusdate, GETDATE()) <= @request.value('(/searchRequest/daysInStatus/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/propertyType') = 0

THEN 1

ELSE

CASE WHEN svo.property_type IN (SELECT Request.PropertyType.value('.', 'int')

FROM @request.nodes('/searchRequest/propertyType/type') Request(PropertyType)) THEN 1

ELSE 0

END

END = 1

AND

CASE

WHEN @firmCode IS NULL THEN 1

ELSE

CASE WHEN @firmCode IN (svo.ListBrokerCode1, svo.ListBrokerCode2, svo.ListBrokerCode3) THEN 1

ELSE 0

END

END = 1

AND

CASE

WHEN @agentUsername IS NULL THEN 1

ELSE

CASE WHEN @agentUserName IN (svo.ListAgentID1, svo.ListAgentID2, svo.ListAgentID3) THEN 1

ELSE 0

END

END = 1

AND ((@request.exist('/searchRequest/listingStatus') = 1

AND svo.status IN (SELECT Request.Status.value('.', 'int') FROM @request.nodes('/searchRequest/listingStatus/status') Request(Status))

)

OR

(@request.exist('/searchRequest/listingStatus') = 0

AND svo.status NOT IN (40,25,15,35)

)

)

AND (@request.exist('/searchRequest/openhouse') = 0

OR EXISTS (SELECT ohl.MlsNum

FROM open_house_list ohl (nolock)

WHERE

ohl.MlsNum = svo.MlsNum

AND

ohl.public_yn = 'yes'

AND

ohl.openhouse_type IN ('new', 'review')

AND (@request.exist('/searchRequest/openhouse/low') = 0

OR ohl.openhouse_date >= @request.value('(/searchRequest/openhouse/low)[1]', 'datetime')

)

AND (@request.exist('/searchRequest/openhouse/high') = 0

OR ohl.openhouse_date <= @request.value('(/searchRequest/openhouse/high)[1]', 'datetime')

)

)

)

AND (@request.exist('/searchRequest/saleType') = 0

OR EXISTS (SELECT lst.MlsNum

FROM ListingSaleType lst (nolock)

WHERE

lst.MlsNum = svo.MlsNum

AND

(@request.exist('/searchRequest/saleType/auction') = 0

OR lst.IsAuction = 1

)

AND

(@request.exist('/searchRequest/saleType/foreclosure') = 0

OR lst.IsForeclosure = 1

)

AND

(@request.exist('/searchRequest/saleType/default') = 0

OR lst.IsDefault = 1

)

AND

(@request.exist('/searchRequest/saleType/reo') = 0

OR lst.IsREO = 1

)

AND

(@request.exist('/searchRequest/saleType/short') = 0

OR lst.IsShortPay = 1

)

AND

(@request.exist('/searchRequest/saleType/standard') = 0

OR lst.IsStandard = 1

)

AND

(@request.exist('/searchRequest/saleType/probate') = 0

OR lst.IsProbate = 1

)

)

)

*****************

Productions STATISTICS
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 235 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 281 ms, elapsed time = 1649 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(201 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'mls_unified_svo_tbl'. Scan count 1, logical reads 935996, physical reads 0, read-ahead reads 935996, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 26359 ms,  elapsed time = 282784 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

*************************
QA sTATISTICS

--
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--
--SQL Server Execution Times:
--   CPU time = 46 ms,  elapsed time = 117 ms.
--
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 0 ms.
--SQL Server parse and compile time:
--   CPU time = 532 ms, elapsed time = 1708 ms.
--
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 0 ms.
--
--(201 row(s) affected)
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'mls_unified_svo_tbl'. Scan count 5, logical reads 935996, physical reads 553, read-ahead reads 935357, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--SQL Server Execution Times:
--   CPU time = 213608 ms,  elapsed time = 279405 ms.
--
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 0 ms.

******************
DEV Statistics

--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--
--SQL Server Execution Times:
--   CPU time = 47 ms,  elapsed time = 90 ms.
--
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 0 ms.
--SQL Server parse and compile time:
--   CPU time = 328 ms, elapsed time = 1050 ms.
--
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 0 ms.
--
--(201 row(s) affected)
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'mls_unified_svo_tbl'. Scan count 5, logical reads 91475, physical reads 6, read-ahead reads 91104, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--
--SQL Server Execution Times:
--   CPU time = 26202 ms,  elapsed time = 18614 ms.
--
--SQL Server Execution Times:
--   CPU time = 0 ms,  elapsed time = 0 ms.
0
Comment
Question by:DBA2000
  • 5
  • 5
  • 2
12 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 36952932
QUESTION 1
Can the ProductVersion account for this kind of astronomical perf diff?  What does the 4060 vs 4053 mean?


Yes, the productVersion can account for this type of performance difference.  In this case, the 4060 version is the Dev version (developers edition), and uses the same codebase as the Enterprise-level version.

The 4053 version is a security patch version for SP3.  It may or may not be Enterprise (more likely to be Standard edition), so it will not have the same optimizations.

However, other possible issues would be:
    Disk Alignment
    Different Hardware
    Database file placement
    Fragmentation
    Different RAID levels
    .... etc ....

Are the query plans the same on the DEV and Production servers?

QUESTION 2
What can possibly account for these differences

QUESTION 3
What can I do to optimize this code?  (after I find an explanation for this problem).
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36953006
>>What can possibly account for these differences<<
Have you updated stats?

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36953026
But the bigger question is why in the world are you exucuting an Xml Data Type Method such as Exists over and over again.  I have to confess, I have seen some extreme queries, but this has to be somewhere close to the top.  

Please, please re-consider refactoring that mess.  That has to affect not only performance, but also put any average SQL developer to sleep maintaining it.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36953062
Incidentally for anyone attempting to follow that code, here is a re-formatted version that should be a tad easier to read:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--
SET STATISTICS IO ON
SET STATISTICS TIME ON
--
CHECKPOINT
--
USE mls_new
go
--
DECLARE @request xml
DECLARE @latitudeMax float
DECLARE @latitudeMin float
DECLARE @longitudeMax float
DECLARE @longitudeMin float
DECLARE @agentUsername varchar(30)
DECLARE @firmCode varchar(30)
DECLARE @areas TABLE(
		AreaID int,
		Name varchar(200))
--
--
SELECT  @Request = '<searchRequest><header><requestType>ListingSearch</requestType></header><zipCode>91342</zipCode><propertyType><type>0</type></propertyType><listingStatus><status>5</status></listingStatus></searchRequest>'
--
--INSERT INTO ListingSearchIndex
-- (RequestID, SearchKey)
SELECT  470,
        svo.MlsNum
FROM    mls_unified_svo_tbl svo (NOLOCK)
WHERE   svo.LoadOnInternet = 1
        AND svo.std IS NOT NULL
        AND svo.snd IS NOT NULL
        AND (svo.Status IN (5, 30, 45)
             OR (svo.Status IN (10, 20)
                 AND svo.StatusDateDiffToday < 365
                )
            )
		-- DATEDIFF(day, svo.StatusDate, GETDATE()) < 365
        AND CASE WHEN @request.exist('/searchRequest/mlsNumber') = 0 THEN 1
                 ELSE CASE WHEN svo.MlsNum = @request.value('(/searchRequest/mlsNumber)[1]', 'varchar(20)') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/zipCode') = 0 THEN 1
                 ELSE CASE WHEN svo.zip = @request.value('(/searchRequest/zipCode)[1]', 'varchar(500)') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/city') = 0 THEN 1
                 ELSE CASE WHEN svo.city = @request.value('(/searchRequest/city)[1]', 'varchar(50)') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/address') = 0 THEN 1
                 ELSE CASE WHEN SUBSTRING(svo.address, 1, LEN(@request.value('(/searchRequest/address)[1]', 'varchar(100)'))) = @request.value('(/searchRequest/address)[1]',
                                                                                                                                               'varchar(100)')
                           THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/listingArea') = 0 THEN 1
                 ELSE CASE WHEN svo.ar IN (SELECT   AreaID
                                           FROM     @areas)
                                OR svo.city IN (SELECT  Name
                                                FROM    @areas) THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @latitudeMin IS NULL
                      OR @latitudeMax IS NULL
                      OR @longitudeMin IS NULL
                      OR @longitudeMax IS NULL THEN 1
                 ELSE CASE WHEN svo.Latitude BETWEEN @latitudeMin AND @latitudeMax
                                AND svo.Longitude BETWEEN @longitudeMin AND @longitudeMax THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/price/low') = 0 THEN 1
                 ELSE CASE WHEN svo.lp >= @request.value('(/searchRequest/price/low)[1]', 'bigint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/price/high') = 0 THEN 1
                 ELSE CASE WHEN svo.lp <= @request.value('(/searchRequest/price/high)[1]', 'bigint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/yearBuilt/low') = 0 THEN 1
                 ELSE CASE WHEN svo.yb >= @request.value('(/searchRequest/yearBuilt/low)[1]', 'smallint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/yearBuilt/high') = 0 THEN 1
                 ELSE CASE WHEN svo.yb <= @request.value('(/searchRequest/yearBuilt/high)[1]', 'smallint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/sqFootage/low') = 0 THEN 1
                 ELSE CASE WHEN svo.sf >= @request.value('(/searchRequest/sqFootage/low)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/sqFootage/high') = 0 THEN 1
                 ELSE CASE WHEN svo.sf <= @request.value('(/searchRequest/sqFootage/high)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/bed/low') = 0 THEN 1
                 ELSE CASE WHEN svo.br >= @request.value('(/searchRequest/bed/low)[1]', 'smallint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/bed/high') = 0 THEN 1
                 ELSE CASE WHEN svo.br <= @request.value('(/searchRequest/bed/high)[1]', 'smallint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/bath/low') = 0 THEN 1
                 ELSE CASE WHEN svo.ba >= @request.value('(/searchRequest/bath/low)[1]', 'decimal') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/bath/high') = 0 THEN 1
                 ELSE CASE WHEN svo.ba <= @request.value('(/searchRequest/bath/high)[1]', 'decimal') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/parking/low') = 0 THEN 1
                 ELSE CASE WHEN svo.pkg_num >= @request.value('(/searchRequest/parking/low)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/parking/high') = 0 THEN 1
                 ELSE CASE WHEN svo.pkg_num <= @request.value('(/searchRequest/parking/high)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/lotSize/low') = 0 THEN 1
                 ELSE CASE WHEN svo.lsz >= @request.value('(/searchRequest/lotSize/low)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/lotSize/high') = 0 THEN 1
                 ELSE CASE WHEN svo.lsz <= @request.value('(/searchRequest/lotSize/high)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/soldPrice/low') = 0 THEN 1
                 ELSE CASE WHEN svo.sp >= @request.value('(/searchRequest/soldPrice/low)[1]', 'bigint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/soldPrice/high') = 0 THEN 1
                 ELSE CASE WHEN svo.sp <= @request.value('(/searchRequest/soldPrice/high)[1]', 'bigint') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/daysInStatus/low') = 0 THEN 1
                 ELSE CASE WHEN DATEDIFF(d, svo.statusdate, GETDATE()) >= @request.value('(/searchRequest/daysInStatus/low)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/daysInStatus/high') = 0 THEN 1
                 ELSE CASE WHEN DATEDIFF(d, svo.statusdate, GETDATE()) <= @request.value('(/searchRequest/daysInStatus/high)[1]', 'int') THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @request.exist('/searchRequest/propertyType') = 0 THEN 1
                 ELSE CASE WHEN svo.property_type IN (SELECT    Request.PropertyType.value('.', 'int')
                                                      FROM      @request.nodes('/searchRequest/propertyType/type') Request (PropertyType)) THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @firmCode IS NULL THEN 1
                 ELSE CASE WHEN @firmCode IN (svo.ListBrokerCode1, svo.ListBrokerCode2, svo.ListBrokerCode3) THEN 1
                           ELSE 0
                      END
            END = 1
        AND CASE WHEN @agentUsername IS NULL THEN 1
                 ELSE CASE WHEN @agentUserName IN (svo.ListAgentID1, svo.ListAgentID2, svo.ListAgentID3) THEN 1
                           ELSE 0
                      END
            END = 1
        AND ((@request.exist('/searchRequest/listingStatus') = 1
              AND svo.status IN (SELECT Request.Status.value('.', 'int')
                                 FROM   @request.nodes('/searchRequest/listingStatus/status') Request (Status))
             )
             OR (@request.exist('/searchRequest/listingStatus') = 0
                 AND svo.status NOT IN (40, 25, 15, 35)
                )
            )
        AND (@request.exist('/searchRequest/openhouse') = 0
             OR EXISTS ( SELECT ohl.MlsNum
                         FROM   open_house_list ohl (NOLOCK)
                         WHERE  ohl.MlsNum = svo.MlsNum
                                AND ohl.public_yn = 'yes'
                                AND ohl.openhouse_type IN ('new', 'review')
                                AND (@request.exist('/searchRequest/openhouse/low') = 0
                                     OR ohl.openhouse_date >= @request.value('(/searchRequest/openhouse/low)[1]', 'datetime')
                                    )
                                AND (@request.exist('/searchRequest/openhouse/high') = 0
                                     OR ohl.openhouse_date <= @request.value('(/searchRequest/openhouse/high)[1]', 'datetime')
                                    ) )
            )
        AND (@request.exist('/searchRequest/saleType') = 0
             OR EXISTS ( SELECT lst.MlsNum
                         FROM   ListingSaleType lst (NOLOCK)
                         WHERE  lst.MlsNum = svo.MlsNum
                                AND (@request.exist('/searchRequest/saleType/auction') = 0
                                     OR lst.IsAuction = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/foreclosure') = 0
                                     OR lst.IsForeclosure = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/default') = 0
                                     OR lst.IsDefault = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/reo') = 0
                                     OR lst.IsREO = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/short') = 0
                                     OR lst.IsShortPay = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/standard') = 0
                                     OR lst.IsStandard = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/probate') = 0
                                     OR lst.IsProbate = 1
                                    ) )
            )

Open in new window

0
 

Author Comment

by:DBA2000
ID: 36956052
Bhess1:
Yes, the query plans are the same on DEV and PROD
As to the versions, I would not be able to do much because, in DEV we are using Seveloper ed, in Prod and QA, we are using Standard.

Acperkins:
First, thank you so much for going out of your way to reformat the query!
Yes, I have updated Stats on all instances

As to the use of the XML Data Type Method, ...Exists I received this from the Developed and QA is testing... I was asked to look into performance.  In a divided shop (Database Developer - DBA  and Admin DBA, which I am) I don't get to write the queries ... at any rate, no excuses, but I am not very familiar with what I can do with the XML ....

will you be kind enough to give me one sample of how I can change the code so that it uses a more effecient code.

Thank you in advance.
Tes
0
 

Author Comment

by:DBA2000
ID: 36956084
How can I replace the CASE statement in the WHERE clause with something more efficient? Does it make sense to save the XML string in a table, aand query the table and check the condition in the WHERE clause from the data in the table? If so, will you please show me a sample code to save the XML to table and do an equivalent code to replace the CASE ... in the WHERE clause.

Thank you,
Tes
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 36958128
After looking around for ways to speed up the data lookups, I would consider creating a @table with an XML field to hold the request AND a schema defined for the XML field.   This assumes, of course, that you can successfully define an XML schema for the request.  The difference in speed of access of XML data with, and without, a schema defined, is night and day.

Look at this question on stackoverflow.com, and pay specific attention to the question starting with
Stumbled across this question whilst having a very similar problem.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36959686
>>As to the use of the XML Data Type Method, ...Exists I received this from the Developed and QA is testing... <<
And there is nothing wrong with it, I use it extensively.  My point was that it may be best to shred all the values of the Xml into local variables (or a table as has been suggested) before you do the actual query.

If you have not come up with a solution, I will see if I can recode the query to see if it improves.
0
 

Author Comment

by:DBA2000
ID: 36963350
Hi, Acperkins

At this point, I am back to the books! I am reading about XML queries and learning how to shred the XML ...  there is a steep uphill learning curve.

I will appreciate it if you please recode the query for me ... it will save me days ...

Thanks,
Tes
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36966056
This should give you an idea:
DECLARE @request xml
DECLARE @latitudeMax float
DECLARE @latitudeMin float
DECLARE @longitudeMax float
DECLARE @longitudeMin float
DECLARE @agentUsername varchar(30)
DECLARE @firmCode varchar(30)
DECLARE @areas TABLE
    (AreaID int,
     Name varchar(200))

DECLARE @MisNumber varchar(20),
    @ZIPCode varchar(20),
    @City varchar(50),
    @Address varchar(100),
    @ListingArea varchar(20),   -- Change data type as appropriate
    @Low bigint,
    @High bigint,
    @YearBuiltLow smallint,
    @YearBuiltHigh smallint,
    @SqFootageLow integer,
    @SqFootageHigh integer,
    @BedLow smallint,
    @BedHigh smallint,
    @BathLow decimal,
    @BathHigh decimal,
    @ParkingLow integer,
    @ParkingHigh integer,
    @LotSizeLow integer,
    @LotSizeHigh integer,
    @SoldPriceLow bigint,
    @SoldPriceHigh bigint,
    @DaysInStatusLow integer,
    @DaysInStatusHigh integer,
    @HasPropertyType bit
		
DECLARE @PropertyTypes TABLE (PropertyType integer)

SELECT  @Request = '<searchRequest><header><requestType>ListingSearch</requestType></header><zipCode>91342</zipCode><propertyType><type>0</type></propertyType><listingStatus><status>5</status></listingStatus></searchRequest>'

SELECT  @MisNumber = T.C.value('(mlsNumber)[1]', 'varchar(20)'),
        @ZIPCode = T.C.value('(zipCode)[1]', 'varchar(20)'),
        @City = T.C.value('(city)[1]', 'varchar(50)'),
        @Address = T.C.value('(address)[1]', 'varchar(100)'),
        @Low = T.C.value('(price/low)[1]', 'bigint'),
        @High = T.C.value('(price/high)[1]', 'bigint'),
        @YearBuiltLow = T.C.value('(yearBuilt/low)[1]', 'smallint'),
        @YearBuiltHigh = T.C.value('(yearBuilt/high)[1]', 'smallint'),
        @SqFootageLow = T.C.value('(sqFootage/low)[1]', 'int'),
        @SqFootageHigh = T.C.value('(sqFootage/high)[1]', 'int'),
        @BedLow = T.C.value('(bed/low)[1]', 'smallint'),
        @BedHigh = T.C.value('(bed/high)[1]', 'smallint'),
        @BathLow = T.C.value('(bath/low)[1]', 'decimal'),
        @BathHigh = T.C.value('(bath/high)[1]', 'decimal'),
        @ParkingLow = T.C.value('(parking/low)[1]', 'int'),
        @ParkingHigh = T.C.value('(parking/high)[1]', 'int'),
        @LotSizeLow = T.C.value('(lotSize/low)[1]', 'int'),
        @LotSizeHigh = T.C.value('(lotSize/high)[1]', 'int'),
        @SoldPriceLow = T.C.value('(soldPrice/low)[1]', 'bigint'),
        @SoldPriceHigh = T.C.value('(soldPrice/high)[1]', 'bigint'),
        @DaysInStatusLow = T.C.value('(daysInStatus/low)[1]', 'int'),
        @DaysInStatusHigh = T.C.value('(daysInStatus/high)[1]', 'int'),
        @HasPropertyType = T.C.exist('/searchRequest/propertyType')
FROM    @Request.nodes('searchRequest') T (C)


INSERT  @PropertyTypes
        (PropertyType
        )
        SELECT  T.C.value('.', 'int')
        FROM    @Request.nodes('searchRequest/propertyType/type') T (C)



--INSERT INTO ListingSearchIndex
-- (RequestID, SearchKey)
SELECT  470,
        svo.MlsNum
FROM    mls_unified_svo_tbl svo (NOLOCK)
WHERE   svo.LoadOnInternet = 1
        AND svo.std IS NOT NULL
        AND svo.snd IS NOT NULL
        AND (svo.Status IN (5, 30, 45)
             OR (svo.Status IN (10, 20)
                 AND svo.StatusDateDiffToday < 365
                )
            )
		-- DATEDIFF(day, svo.StatusDate, GETDATE()) < 365
        AND (@MisNumber IS NULL
             OR svo.MlsNum = @MisNumber
            )
        AND (@ZIPCode IS NULL
             OR svo.zip = @ZIPCode
            )
        AND (@City IS NULL
             OR svo.city = @City
            )
        AND (@Address IS NULL
             OR svo.[address] = @Address
            )
        AND (@ListingArea IS NULL
             OR EXISTS ( SELECT 1
                         FROM   @areas
                         WHERE  AreaID = svo.ar
                                OR Name = svo.city )
            )
        AND (@latitudeMin IS NULL
             OR @latitudeMax IS NULL
             OR @longitudeMin IS NULL
             OR @longitudeMax IS NULL
             OR (svo.Latitude BETWEEN @latitudeMin AND @latitudeMax
                 AND svo.Longitude BETWEEN @longitudeMin AND @longitudeMax
                )
            )
        AND (@Low IS NULL
             OR svo.lp >= @Low
            )
        AND (@High IS NULL
             OR svo.lp <= @High
            )
        AND (@YearBuiltLow IS NULL
             OR svo.yb >= @YearBuiltLow
            )
        AND (@YearBuiltHigh IS NULL
             OR svo.yb <= @YearBuiltHigh
            )
        AND (@SqFootageLow IS NULL
             OR svo.sf >= @SqFootageLow
            )
        AND (@SqFootageHigh IS NULL
             OR svo.sf <= @SqFootageHigh
            )
        AND (@BedLow IS NULL
             OR svo.br >= @BedLow
            )
        AND (@BedHigh IS NULL
             OR svo.br <= @BedHigh
            )
        AND (@BathLow IS NULL
             OR svo.ba >= @BathLow
            )
        AND (@BathHigh IS NULL
             OR svo.ba <= @BathHigh
            )
        AND (@ParkingLow IS NULL
             OR svo.pkg_num >= @ParkingLow
            )
        AND (@ParkingHigh IS NULL
             OR svo.pkg_num <= @ParkingHigh
            )
        AND (@LotSizeLow IS NULL
             OR svo.lsz >= @ParkingLow
            )
        AND (@LotSizeHigh IS NULL
             OR svo.lsz <= @ParkingHigh
            )
        AND (@SoldPriceLow IS NULL
             OR svo.sp >= @SoldPriceLow
            )
        AND (@SoldPriceHigh IS NULL
             OR svo.sp <= @SoldPriceHigh
            )
        AND (@DaysInStatusLow IS NULL
             OR DATEDIFF(d, svo.statusdate, GETDATE()) >= @DaysInStatusLow
            )  -- If there is an index on statusdate this should be improved
        AND (@DaysInStatusHigh IS NULL
             OR DATEDIFF(d, svo.statusdate, GETDATE()) <= @DaysInStatusHigh
            )
        AND (@HasPropertyType = 0
             OR EXISTS ( SELECT 1
                         FROM   @PropertyTypes
                         WHERE  PropertyType = svo.property_type )
            )
        AND (@firmCode IS NULL
             OR @firmCode IN (svo.ListBrokerCode1, svo.ListBrokerCode2, svo.ListBrokerCode3)
            )
        AND (@agentUsername IS NULL
             OR @agentUserName IN (svo.ListAgentID1, svo.ListAgentID2, svo.ListAgentID3)
            )


-- The rest I will leave as an excercise.
        AND ((@request.exist('/searchRequest/listingStatus') = 1
              AND svo.status IN (SELECT Request.Status.value('.', 'int')
                                 FROM   @request.nodes('/searchRequest/listingStatus/status') Request (Status))
             )
             OR (@request.exist('/searchRequest/listingStatus') = 0
                 AND svo.status NOT IN (40, 25, 15, 35)
                )
            )
        AND (@request.exist('/searchRequest/openhouse') = 0
             OR EXISTS ( SELECT ohl.MlsNum
                         FROM   open_house_list ohl (NOLOCK)
                         WHERE  ohl.MlsNum = svo.MlsNum
                                AND ohl.public_yn = 'yes'
                                AND ohl.openhouse_type IN ('new', 'review')
                                AND (@request.exist('/searchRequest/openhouse/low') = 0
                                     OR ohl.openhouse_date >= @request.value('(/searchRequest/openhouse/low)[1]', 'datetime')
                                    )
                                AND (@request.exist('/searchRequest/openhouse/high') = 0
                                     OR ohl.openhouse_date <= @request.value('(/searchRequest/openhouse/high)[1]', 'datetime')
                                    ) )
            )
        AND (@request.exist('/searchRequest/saleType') = 0
             OR EXISTS ( SELECT lst.MlsNum
                         FROM   ListingSaleType lst (NOLOCK)
                         WHERE  lst.MlsNum = svo.MlsNum
                                AND (@request.exist('/searchRequest/saleType/auction') = 0
                                     OR lst.IsAuction = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/foreclosure') = 0
                                     OR lst.IsForeclosure = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/default') = 0
                                     OR lst.IsDefault = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/reo') = 0
                                     OR lst.IsREO = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/short') = 0
                                     OR lst.IsShortPay = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/standard') = 0
                                     OR lst.IsStandard = 1
                                    )
                                AND (@request.exist('/searchRequest/saleType/probate') = 0
                                     OR lst.IsProbate = 1
                                    ) )
            )

Open in new window

0
 

Author Comment

by:DBA2000
ID: 37239861
My sincere apology to everyone for "abandoning" the question; this, however, was due to situations far beyond my control.

I thank everyone, and particularly acperkins for your help.

THANKS!
Dan
0
 

Author Closing Comment

by:DBA2000
ID: 37239876
A much belated THANK U!!!!!!!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question