Comments by LegendaryMike (Top 3 by date)

HiMy file loading process involves loading large files (100K records) and matching each record separately using complex logic with existing data in several tables (aside from this problamtic one)
I've already gone through the algorythm and reduced the number of queries to the absolute minimum of what I have to (unless local caching is used). Local caching is problematic due to 2 facts:
1. large amount of data, that will grow in future to round 100-300M records
2. data being processed affects the data against which I compare. Meaning, after every operation I have to update the local cache



Now that I look at the query (the loading runs for several days now) it switched into a different plan but did not become any faster. Below is the exact query and the plan:




<!--?xml version="1.0" encoding="utf-16"?-->
<showplanxml xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.2" build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<batchsequence>
<batch>
<statements>
<stmtsimple statementcompid="1" statementestrows="1" statementid="1" statementoptmlevel="FULL" statementoptmearlyabortreason="GoodEnoughPlanFound" statementsubtreecost="0.00657384" statementtext="SELECT * FROM M4U_Operations WITH (NOLOCK) WHERE EntryID !=72003158 AND IsRemoved=0 AND CCCompanyID=4986771 AND ShvaTransmissionReference=7519002 AND PaymentNumber=10 AND Token=N'99999999999999' AND (ShvaVoucherID=600076OR ExternalTransactionID=N'10212980654')" statementtype="SELECT" queryhash="0xC5BB519BDC66FF50" queryplanhash="0xC618D02498890435">
<statementsetoptions ansi_nulls="true" ansi_padding="true" ansi_warnings="true" arithabort="true" concat_null_yields_null="true" numeric_roundabort="false" quoted_identifier="true">
<queryplan degreeofparallelism="1" memorygrant="1024" cachedplansize="88" compiletime="21" compilecpu="20" compilememory="792">
<relop avgrowsize="382" estimatecpu="4.18E-06" estimateio="0" estimaterebinds="0" estimaterewinds="0" estimaterows="1" logicalop="Inner Join" nodeid="1" parallel="false" physicalop="Nested Loops" estimatedtotalsubtreecost="0.00657276">
<outputlist>
<columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="EntryID"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="PaymentNumber"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="TerminalID"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="ShvaTransmissionReference"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="ShvaTerminalID"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="ShvaVoucherID"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="ShvaZField"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="CCCompanyID"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="CreditType"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="TransactionDate"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="TransmissionDate"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="CreditTransferDate"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="CCIdentificationType"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="CCBrand"> <columnreference database="[DB]" schema="[dbo]" table="[M4U_Operations]" column="ProcessedByRealCompany">