Creating a table to be use with user searches

Question

Post reply

Creating a table to be use with user searches

dndaughtery

SSChampion

Points: 11290
More actions
November 10, 2014 at 12:49 pm

#312881

I have a situation where the developers are creating a dynamic string with each search and we are having some timeouts. They are passing in parameterized tsql but it is also loading CTE's and then joining the CTE's. I will paste an example of what gets passed in below. We have very large tables with millions of records. I'm thinking the best way to elimate these timeouts is by creating an indexed table which has all the data needed in the return set for the search. We do have some updates and new records added. Would using mirroring to automatically update and insert the new rows to the search table be the best way to go?
Below is a sample of a search:
DECLARE @ProviderXML XML = '<NewDataSet>
<Provider ID="1114957297" IDType="NPI"></Provider>
<Provider ID="1346359510" IDType="NPI"></Provider>
<Provider ID="1821106642" IDType="NPI"></Provider>
<Provider ID="1861528754" IDType="NPI"></Provider>
<Provider ID="1295856771" IDType="NPI"></Provider>
<Provider ID="1962515965" IDType="NPI"></Provider>
<Provider ID="1487767422" IDType="NPI"></Provider>
<Provider ID="1477660173" IDType="NPI"></Provider>
<Provider ID="1679677975" IDType="NPI"></Provider>
<Provider ID="1053321588" IDType="NPI"></Provider>
<Provider ID="1497874077" IDType="NPI"></Provider>
<Provider ID="1942257068" IDType="NPI"></Provider>
<Provider ID="1356397483" IDType="NPI"></Provider>
<Provider ID="1649389487" IDType="NPI"></Provider>
<Provider ID="1740293232" IDType="NPI"></Provider>
<Provider ID="1457568883" IDType="NPI"></Provider>
<Provider ID="1881707842" IDType="NPI"></Provider>
<Provider ID="1760552434" IDType="NPI"></Provider>
<Provider ID="1922117548" IDType="NPI"></Provider>
<Provider ID="1437279858" IDType="NPI"></Provider>
<Provider ID="1356450548" IDType="NPI"></Provider>
<Provider ID="1386765857" IDType="NPI"></Provider>
<Provider ID="1659492122" IDType="NPI"></Provider>
<Provider ID="1790888188" IDType="NPI"></Provider>
<Provider ID="1407965031" IDType="NPI"></Provider>
<Provider ID="1003923707" IDType="NPI"></Provider>
<Provider ID="1477658599" IDType="NPI"></Provider>
<Provider ID="1659488351" IDType="NPI"></Provider>
<Provider ID="1093725145" IDType="NPI"></Provider>
<Provider ID="1497767495" IDType="NPI"></Provider>
<Provider ID="1194832899" IDType="NPI"></Provider>
<Provider ID="1386751089" IDType="NPI"></Provider>
<Provider ID="1982717641" IDType="NPI"></Provider>
<Provider ID="1629181912" IDType="NPI"></Provider>
<Provider ID="1053447151" IDType="NPI"></Provider>
<Provider ID="1194829739" IDType="NPI"></Provider>
<Provider ID="1548379993" IDType="NPI"></Provider>
<Provider ID="1487667978" IDType="NPI"></Provider>
<Provider ID="1730293663" IDType="NPI"></Provider>
<Provider ID="1902900160" IDType="NPI"></Provider>
<Provider ID="1649384579" IDType="NPI"></Provider>
<Provider ID="1508914482" IDType="NPI"></Provider>
<Provider ID="1447260435" IDType="NPI"></Provider>
<Provider ID="1134278971" IDType="NPI"></Provider>
<Provider ID="1669485363" IDType="NPI"></Provider>
<Provider ID="1124122387" IDType="NPI"></Provider>
<Provider ID="1902844806" IDType="NPI"></Provider>
<Provider ID="1275662017" IDType="NPI"></Provider>
<Provider ID="1437288263" IDType="NPI"></Provider>
<Provider ID="1114031036" IDType="NPI"></Provider>
<Provider ID="1508970419" IDType="NPI"></Provider>
<Provider ID="1699889501" IDType="NPI"></Provider>
<Provider ID="1336253350" IDType="NPI"></Provider>
<Provider ID="1093822702" IDType="NPI"></Provider>
<Provider ID="1750494332" IDType="NPI"></Provider>
<Provider ID="1174637193" IDType="NPI"></Provider>
<Provider ID="1962518381" IDType="NPI"></Provider>
<Provider ID="1679689095" IDType="NPI"></Provider>
<Provider ID="1053427476" IDType="NPI"></Provider>
<Provider ID="1144336561" IDType="NPI"></Provider>
<Provider ID="1235245655" IDType="NPI"></Provider>
<Provider ID="1912011321" IDType="NPI"></Provider>
<Provider ID="1174636153" IDType="NPI"></Provider>
<Provider ID="1003946153" IDType="NPI"></Provider>
<Provider ID="1922112325" IDType="NPI"></Provider>
<Provider ID="1356354559" IDType="NPI"></Provider>
<Provider ID="1962515973" IDType="NPI"></Provider>
<Provider ID="1497868483" IDType="NPI"></Provider>
<Provider ID="1386674463" IDType="NPI"></Provider>
<Provider ID="1598894230" IDType="NPI"></Provider>
<Provider ID="1063541779" IDType="NPI"></Provider>
<Provider ID="1992819395" IDType="NPI"></Provider>
<Provider ID="1275640971" IDType="NPI"></Provider>
<Provider ID="1013920503" IDType="NPI"></Provider>
<Provider ID="1205945367" IDType="NPI"></Provider>
<Provider ID="1972682995" IDType="NPI"></Provider>
<Provider ID="1790737468" IDType="NPI"></Provider>
<Provider ID="1972607257" IDType="NPI"></Provider>
<Provider ID="1912014614" IDType="NPI"></Provider>
<Provider ID="1619070182" IDType="NPI"></Provider>
<Provider ID="1154424653" IDType="NPI"></Provider>
<Provider ID="1841393337" IDType="NPI"></Provider>
<Provider ID="1346343829" IDType="NPI"></Provider>
<Provider ID="1184731887" IDType="NPI"></Provider>
<Provider ID="1558475483" IDType="NPI"></Provider>
<Provider ID="1083720403" IDType="NPI"></Provider>
<Provider ID="1508972928" IDType="NPI"></Provider>
<Provider ID="1326154741" IDType="NPI"></Provider>
<Provider ID="1902920739" IDType="NPI"></Provider>
<Provider ID="1720107303" IDType="NPI"></Provider>
<Provider ID="1851425144" IDType="NPI"></Provider>
<Provider ID="1467570317" IDType="NPI"></Provider>
<Provider ID="1083738819" IDType="NPI"></Provider>
<Provider ID="1770601627" IDType="NPI"></Provider>
<Provider ID="1083733661" IDType="NPI"></Provider>
<Provider ID="1801910039" IDType="NPI"></Provider>
<Provider ID="1851370910" IDType="NPI"></Provider>
<Provider ID="1295706422" IDType="NPI"></Provider>
<Provider ID="1073581005" IDType="NPI"></Provider>
<Provider ID="1760450779" IDType="NPI"></Provider>
<Provider ID="1063489342" IDType="NPI"></Provider>
<Provider ID="1306814348" IDType="NPI"></Provider>
<Provider ID="1184692121" IDType="NPI"></Provider>
<Provider ID="1508019241" IDType="NPI"></Provider>
<Provider ID="1821039702" IDType="NPI"></Provider>
<Provider ID="1891726964" IDType="NPI"></Provider>
<Provider ID="1083637722" IDType="NPI"></Provider>
<Provider ID="1245207521" IDType="NPI"></Provider>
<Provider ID="1992788350" IDType="NPI"></Provider>
<Provider ID="1306882709" IDType="NPI"></Provider>
<Provider ID="1689652364" IDType="NPI"></Provider>
<Provider ID="1780658237" IDType="NPI"></Provider>
<Provider ID="1437326683" IDType="NPI"></Provider>
<Provider ID="1750445110" IDType="NPI"></Provider>
<Provider ID="1891042826" IDType="NPI"></Provider>
<Provider ID="1770510679" IDType="NPI"></Provider>
<Provider ID="1154357200" IDType="NPI"></Provider>
<Provider ID="1407888787" IDType="NPI"></Provider>
<Provider ID="1598795601" IDType="NPI"></Provider>
<Provider ID="1952458614" IDType="NPI"></Provider>
<Provider ID="1629005905" IDType="NPI"></Provider>
<Provider ID="1922096965" IDType="NPI"></Provider>
<Provider ID="1316984511" IDType="NPI"></Provider>
<Provider ID="1417157009" IDType="NPI"></Provider>
<Provider ID="1306873690" IDType="NPI"></Provider>
<Provider ID="1821340290" IDType="NPI"></Provider>
<Provider ID="1871536177" IDType="NPI"></Provider>
<Provider ID="1891959391" IDType="NPI"></Provider>
<Provider ID="1780614784" IDType="NPI"></Provider>
<Provider ID="1124292487" IDType="NPI"></Provider>
<Provider ID="1104024462" IDType="NPI"></Provider>
<Provider ID="1518999820" IDType="NPI"></Provider>
<Provider ID="1689763179" IDType="NPI"></Provider>
<Provider ID="1780760850" IDType="NPI"></Provider>
<Provider ID="1508896648" IDType="NPI"></Provider>
<Provider ID="1881620938" IDType="NPI"></Provider>
<Provider ID="1821033440" IDType="NPI"></Provider>
<Provider ID="1477808459" IDType="NPI"></Provider>
<Provider ID="1215939756" IDType="NPI"></Provider>
<Provider ID="1447242011" IDType="NPI"></Provider>
<Provider ID="1083616528" IDType="NPI"></Provider>
<Provider ID="1063414514" IDType="NPI"></Provider>
<Provider ID="1013194711" IDType="NPI"></Provider>
<Provider ID="QMP000002302586" IDType="QNXT"></Provider>
<Provider ID="C06101497 " IDType="QNXT"></Provider>
<Provider ID="QMP000002303162" IDType="QNXT"></Provider>
<Provider ID="C08317358 " IDType="QNXT"></Provider>
<Provider ID="C08317436 " IDType="QNXT"></Provider>
<Provider ID="QMP000002276492" IDType="QNXT"></Provider>
<Provider ID="C06101480 " IDType="QNXT"></Provider>
<Provider ID="QMP000002302693" IDType="QNXT"></Provider>
<Provider ID="QMP000002265938" IDType="QNXT"></Provider>
<Provider ID="QMP000002302967" IDType="QNXT"></Provider>
<Provider ID="QMP000002303786" IDType="QNXT"></Provider>
<Provider ID="QMP000002274104" IDType="QNXT"></Provider>
<Provider ID="QMP000002300117" IDType="QNXT"></Provider>
<Provider ID="QMP000003455788" IDType="QNXT"></Provider>
<Provider ID="QMP000002302601" IDType="QNXT"></Provider>
<Provider ID="C06101756 " IDType="QNXT"></Provider>
<Provider ID="C06101644 " IDType="QNXT"></Provider>
<Provider ID="QMP000002278098" IDType="QNXT"></Provider>
<Provider ID="QMP000002266039" IDType="QNXT"></Provider>
<Provider ID="C06101778 " IDType="QNXT"></Provider>
<Provider ID="QMP000002276626" IDType="QNXT"></Provider>
<Provider ID="QMP000002273980" IDType="QNXT"></Provider>
<Provider ID="QMP000002291649" IDType="QNXT"></Provider>
<Provider ID="QMP000002276526" IDType="QNXT"></Provider>
<Provider ID="QMP000003423798" IDType="QNXT"></Provider>
<Provider ID="C06101646 " IDType="QNXT"></Provider>
<Provider ID="C06101642 " IDType="QNXT"></Provider>
<Provider ID="C08317459 " IDType="QNXT"></Provider>
<Provider ID="QMP000002302957" IDType="QNXT"></Provider>
<Provider ID="C08457407 " IDType="QNXT"></Provider>
<Provider ID="C06101647 " IDType="QNXT"></Provider>
<Provider ID="QMP000002303494" IDType="QNXT"></Provider>
<Provider ID="C08317375 " IDType="QNXT"></Provider>
<Provider ID="QMP000002314346" IDType="QNXT"></Provider>
<Provider ID="C06101635 " IDType="QNXT"></Provider>
<Provider ID="QMP000002302646" IDType="QNXT"></Provider>
<Provider ID="C08317285 " IDType="QNXT"></Provider>
<Provider ID="C06906504 " IDType="QNXT"></Provider>
<Provider ID="QMP000002303787" IDType="QNXT"></Provider>
<Provider ID="C08467245 " IDType="QNXT"></Provider>
<Provider ID="C06101671 " IDType="QNXT"></Provider>
<Provider ID="C06101596 " IDType="QNXT"></Provider>
<Provider ID="C08317744 " IDType="QNXT"></Provider>
<Provider ID="C08412387 " IDType="QNXT"></Provider>
<Provider ID="C08412392 " IDType="QNXT"></Provider>
<Provider ID="QMP000002304146" IDType="QNXT"></Provider>
<Provider ID="QMP000002310880" IDType="QNXT"></Provider>
<Provider ID="QMP000002304160" IDType="QNXT"></Provider>
<Provider ID="QMP000003468103" IDType="QNXT"></Provider>
<Provider ID="C08317446 " IDType="QNXT"></Provider>
<Provider ID="QMP000002271341" IDType="QNXT"></Provider>
<Provider ID="C08317476 " IDType="QNXT"></Provider>
<Provider ID="C08317461 " IDType="QNXT"></Provider>
<Provider ID="QMP000002303767" IDType="QNXT"></Provider>
<Provider ID="C06101661 " IDType="QNXT"></Provider>
<Provider ID="C08317463 " IDType="QNXT"></Provider>
<Provider ID="C08328613 " IDType="QNXT"></Provider>
<Provider ID="C08317458 " IDType="QNXT"></Provider>
<Provider ID="C08317471 " IDType="QNXT"></Provider>
<Provider ID="C08317482 " IDType="QNXT"></Provider>
<Provider ID="C08317456 " IDType="QNXT"></Provider>
<Provider ID="QMP000002276562" IDType="QNXT"></Provider>
<Provider ID="C06101481 " IDType="QNXT"></Provider>
<Provider ID="C08317199 " IDType="QNXT"></Provider>
<Provider ID="C08317565 " IDType="QNXT"></Provider>
<Provider ID="QMP000002302705" IDType="QNXT"></Provider>
<Provider ID="C08317443 " IDType="QNXT"></Provider>
<Provider ID="QMP000002303230" IDType="QNXT"></Provider>
<Provider ID="QMP000002302614" IDType="QNXT"></Provider>
<Provider ID="QMP000002274985" IDType="QNXT"></Provider>
<Provider ID="QMP000002276739" IDType="QNXT"></Provider>
<Provider ID="C06101730 " IDType="QNXT"></Provider>
<Provider ID="C08317371 " IDType="QNXT"></Provider>
<Provider ID="C06101757 " IDType="QNXT"></Provider>
<Provider ID="C08317690 " IDType="QNXT"></Provider>
<Provider ID="C06101761 " IDType="QNXT"></Provider>
<Provider ID="C06101787 " IDType="QNXT"></Provider>
<Provider ID="C08317585 " IDType="QNXT"></Provider>
<Provider ID="C06101664 " IDType="QNXT"></Provider>
<Provider ID="C08467311 " IDType="QNXT"></Provider>
<Provider ID="C08317363 " IDType="QNXT"></Provider>
<Provider ID="C08317455 " IDType="QNXT"></Provider>
<Provider ID="C08317439 " IDType="QNXT"></Provider>
<Provider ID="C08317473 " IDType="QNXT"></Provider>
<Provider ID="C08317448 " IDType="QNXT"></Provider>
<Provider ID="C06101670 " IDType="QNXT"></Provider>
<Provider ID="QMP000002277219" IDType="QNXT"></Provider>
<Provider ID="C08317462 " IDType="QNXT"></Provider>
<Provider ID="QMP000002302380" IDType="QNXT"></Provider>
<Provider ID="C08317466 " IDType="QNXT"></Provider>
<Provider ID="QMP000002271878" IDType="QNXT"></Provider>
<Provider ID="C08317477 " IDType="QNXT"></Provider>
<Provider ID="C08317615 " IDType="QNXT"></Provider>
<Provider ID="QMP000002302329" IDType="QNXT"></Provider>
<Provider ID="QMP000002302901" IDType="QNXT"></Provider>
<Provider ID="QMP000002302665" IDType="QNXT"></Provider>
<Provider ID="QMP000002262072" IDType="QNXT"></Provider>
<Provider ID="QMP000002272387" IDType="QNXT"></Provider>
<Provider ID="QMP000002310005" IDType="QNXT"></Provider>
<Provider ID="QMP000002264691" IDType="QNXT"></Provider>
<Provider ID="QMP000002297512" IDType="QNXT"></Provider>
<Provider ID="QMP000003141250" IDType="QNXT"></Provider>
<Provider ID="QMP000002294491" IDType="QNXT"></Provider>
<Provider ID="QMP000002303330" IDType="QNXT"></Provider>
<Provider ID="QMP000002319818" IDType="QNXT"></Provider>
<Provider ID="C08317338 " IDType="QNXT"></Provider>
<Provider ID="C08412411 " IDType="QNXT"></Provider>
<Provider ID="QMP000002262073" IDType="QNXT"></Provider>
<Provider ID="QMP000002272378" IDType="QNXT"></Provider>
<Provider ID="QMP000002300362" IDType="QNXT"></Provider>
<Provider ID="C08383883 " IDType="QNXT"></Provider>
<Provider ID="C08318588 " IDType="QNXT"></Provider>
<Provider ID="QMP000002298285" IDType="QNXT"></Provider>
<Provider ID="QMP000002272392" IDType="QNXT"></Provider>
<Provider ID="QMP000003713719" IDType="QNXT"></Provider>
<Provider ID="C08319235 " IDType="QNXT"></Provider>
<Provider ID="C08318857 " IDType="QNXT"></Provider>
<Provider ID="C08319244 " IDType="QNXT"></Provider>
<Provider ID="C08318839 " IDType="QNXT"></Provider>
<Provider ID="QMP000002315579" IDType="QNXT"></Provider>
<Provider ID="C08317957 " IDType="QNXT"></Provider>
<Provider ID="C08318379 " IDType="QNXT"></Provider>
<Provider ID="QMP000003510087" IDType="QNXT"></Provider>
<Provider ID="QMP000003353553" IDType="QNXT"></Provider>
<Provider ID="C08319247 " IDType="QNXT"></Provider>
<Provider ID="QMP000003781108" IDType="QNXT"></Provider>
<Provider ID="C06104552 " IDType="QNXT"></Provider>
<Provider ID="QMP000003699719" IDType="QNXT"></Provider>
<Provider ID="C08319332 " IDType="QNXT"></Provider>
<Provider ID="QMP000002302664" IDType="QNXT"></Provider>
<Provider ID="QMP000003451622" IDType="QNXT"></Provider>
<Provider ID="C08317818 " IDType="QNXT"></Provider>
<Provider ID="QMP000003374200" IDType="QNXT"></Provider>
<Provider ID="QMP000003509390" IDType="QNXT"></Provider>
<Provider ID="C08319465 " IDType="QNXT"></Provider>
<Provider ID="C08318230 " IDType="QNXT"></Provider>
<Provider ID="C08385319 " IDType="QNXT"></Provider>
<Provider ID="QMP000003888733" IDType="QNXT"></Provider>
<Provider ID="C08384967 " IDType="QNXT"></Provider>
<Provider ID="C08384774 " IDType="QNXT"></Provider>
<Provider ID="C08384892 " IDType="QNXT"></Provider>
<Provider ID="C08384815 " IDType="QNXT"></Provider>
<Provider ID="QMP000003511216" IDType="QNXT"></Provider>
<Provider ID="1340729" IDType="MHC"></Provider>
<Provider ID="0630501" IDType="MHC"></Provider>
<Provider ID="6024938" IDType="MHC"></Provider>
<Provider ID="0174846" IDType="MHC"></Provider>
<Provider ID="C06104522" IDType="QNXT"></Provider>
<Provider ID="1972546281" IDType="NPI"></Provider>
<Provider ID="C08351207" IDType="QNXT"></Provider>
<Provider ID="1376592923" IDType="NPI"></Provider>
</NewDataSet>
',
@ServiceStartDate DATETIME='2012-01-01',
@ServiceTerminationDate DATETIME = '2014-01-01',
@ClaimStatus VARCHAR(100) = 'Approved';
DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @ProviderXML;
WITH
GroupNetworkCTE AS (
SELECT ID
FROM OPENXML (@hdoc, '/NewDataSet/Provider',1)
WITH ([ID] varchar(15), [IDType] varchar(15))
),
pcpMemberCTE
AS ( SELECT m.*
FROM HSCMemberPlanInfo m ( NOLOCK )
INNER JOIN GroupNetworkCTE g ON m.PCPQnxtID = g.ID
UNION
SELECT m.*
FROM HSCMemberPlanInfo m ( NOLOCK )
INNER JOIN GroupNetworkCTE g ON m.PCPMHCProviderID = g.ID
),
claimCTE
AS ( SELECT DISTINCT
ClaimID ,
MemberID ,
MemberName ,
ServiceProviderID ,
ServiceProviderName ,
BillingProviderID ,
BillingProviderName ,
RenderingNPI ,
ClaimSubmissionDate ,
ClaimProcessedDate ,
ServiceStartDate ,
ServiceTerminationDate ,
ClaimOption ,
TotalClaimAmount ,
ClaimAmountPaid ,
PaymentMethod ,
ClaimStatus ,
CheckNumber ,
CheckStatus ,
CheckIssueDate ,
PatientControlNumber ,
memberresponsibility ,
checkamount ,
PayeesName ,
PayeesAddress ,
ProviderAccountNo ,
ProvPortalClaimId,
CoInsuranceAmount,
CoPayAmount
FROM HSCClaims c ( NOLOCK )
WHERE (ServiceStartDate >= @ServiceStartDate) AND (ServiceTerminationDate <= @ServiceTerminationDate) AND (ClaimStatus = @ClaimStatus)
)
SELECT *
FROM
( SELECT c.*
FROM claimCTE c
INNER JOIN GroupNetworkCTE gn ON c.BillingProviderID = gn.ID
UNION
SELECT c.*
FROM claimCTE c
INNER JOIN GroupNetworkCTE gn ON c.ServiceProviderID = gn.ID
UNION
SELECT c.*
FROM claimCTE c
INNER JOIN pcpMemberCTE m ON c.memberID = m.SecondaryID
) AS t
OPTION(OPTIMIZE FOR UNKNOWN)
EXEC sp_xml_removedocument @hdoc;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

dndaughtery SSChampion Points: 11290 More actions · Answer 1

Turns out I was able to speed it up by looking at the query plan and adding some indexes.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

dndaughtery (11/11/2014)
Turns out I was able to speed it up by looking at the query plan and adding some indexes.

How fast is it now?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)