(SQL) SQL (2015)

Materialized View has accelerated SQL request a thousands times.

In my current project I'm faced with very long SQL request, a 50 records SQL server produced more than one minutes:



My View was huge:


   1:  ALTER View [dbo].[GetInvestUaList]
   2:  with schemabinding
   3:  as
   4:  select
   5:    t.id,
   6:    t.cpv,
   7:    tenderID,
   8:    title,
   9:    isnull(s.Name, s3.Name) StateText,
  10:    t.status,
  11:    description,
  12:    t.value_amount,
  13:    value_currency,
  14:    tenderPeriod_startDate,
  15:    tenderPeriod_endDate,
  16:    auction_date,
  17:    auctionPeriod_startDate,
  18:    procuringEntity_name procuringEntity,
  19:    procuringEntity_id,
  20:    t.dateModified,
  21:    dgfID,
  22:    procurementMethodType,
  23:    tenderAttempts,
  24:    cdb,
  25:    list_mark,
  26:    minimalStep_amount,
  27:    CASE
  28:      WHEN l.id IS NULL THEN 0
  29:      ELSE 1
  30:    END in_list,
  31:    CASE
  32:      WHEN pl.id IS NULL THEN 0
  33:      ELSE 1
  34:    END in_popular_list,
  35:    case
  36:      when (
  37:        (
  38:          CPV = '06000000-2'
  39:          or left(CPV, 2) = '04'
  40:        )
  41:        and (j_content not like '%PA01-7%')
  42:      ) then 1
  43:      else 0
  44:    end as RealEstateSale,
  45:    case
  46:      when (left(CPV, 2) = '06') then 1
  47:      else 0
  48:    end as LandParcel,
  49:    case
  50:      when (
  51:        (
  52:          procurementMethodType = 'sellout.english'
  53:          or procurementMethodType = 'sellout.insider'
  54:        )
  55:        and cdb = '2'
  56:      ) then 1
  57:      else 0
  58:    end as Privatization,
  59:    case
  60:      when left(CPV, 2) = '07' then 1
  61:      else 0
  62:    end as Receivables,
  63:    case
  64:      when list_mark = 'bankrupt' then 1
  65:      else 0
  66:    end as BankruptsProperty,
  67:    case
  68:      when (
  69:        (
  70:          j_content like '%QB29-3%'
  71:          or j_content like '%PA01-7%'
  72:        )
  73:        and status not in (
  74:          'cancelled',
  75:          'complete',
  76:          'unsuccessful',
  77:          'delete',
  78:          'deleted'
  79:        )
  80:      ) then 1
  81:      else 0
  82:    end as Leasehold,
  83:    case
  84:      when (list_mark = 'nadra') then 1
  85:      else 0
  86:    end as SubsoilUse,
  87:    case
  88:      when (
  89:        CPV ! = '34621100-7'
  90:        and left(CPV, 2) = '34'
  91:      ) then 1
  92:      else 0
  93:    end as Vehicles,
  94:    case
  95:      when (
  96:        CPV LIKE('1491%')
  97:        or CPV LIKE('1463%')
  98:      ) then 1
  99:      else 0
 100:    end as ScrapMetal,
 101:    case
 102:      when (
 103:        (
 104:          CPV LIKE('1491%')
 105:          or CPV LIKE('1463%')
 106:        )
 107:        and list_mark = 'timber'
 108:      ) then 1
 109:      else 0
 110:    end as Wood,
 111:    case
 112:      when (j_content like '%DA04-3%') then 1
 113:      else 0
 114:    end as CommercialTrades,
 115:    case
 116:      when (
 117:        (j_content like '%DA04-3%')
 118:        and list_mark = 'railwayCargo'
 119:      ) then 1
 120:      else 0
 121:    end as RailCarHire,
 122:    case
 123:      when (
 124:        (
 125:          procuringEntity_id = '43333684'
 126:          or procuringEntity_id = '43332555'
 127:          or procuringEntity_id = '43350888'
 128:          or procuringEntity_id = '43348711'
 129:          or procuringEntity_id = '43350935'
 130:          or procuringEntity_id = '43334913'
 131:          or procuringEntity_id = '43337207'
 132:          or procuringEntity_id = '43337359'
 133:          or procuringEntity_id = '43333459'
 134:          or procuringEntity_id = '43332675'
 135:          or procuringEntity_id = '43350542'
 136:          or procuringEntity_id = '43350097'
 137:          or procuringEntity_id = '43332958'
 138:          or procuringEntity_id = '43336287'
 139:          or procuringEntity_id = '43335608'
 140:        )
 141:        and (j_content like '%DA04-3%')
 142:      ) then 1
 143:      else 0
 144:    end as Customs,
 145:    case
 146:      when (
 147:        (procuringEntity_id = '37472392')
 148:        and(j_content like '%DA04-3%')
 149:      ) then 1
 150:      else 0
 151:    end as StateReserve,
 152:    case
 153:      when (
 154:        (j_content like '%DA04-3%')
 155:        and list_mark = 'legitimatePropertyLease'
 156:      ) then 1
 157:      else 0
 158:    end as LegitimatePropertyLease,
 159:    case
 160:      when (
 161:        (j_content like '%DA04-3%')
 162:        and list_mark = 'dgf'
 163:      ) then 1
 164:      else 0
 165:    end as Dgf
 166:  FROM
 167:    (
 168:      select
 169:         tt.[id]
 170:        ,tt.[tenderID]
 171:        ,tt.[token]
 172:        ,tt.[id_member]
 173:        ,tt.[id_user]
 174:        ,tt.[dateModified]
 175:        ,tt.[title]
 176:        ,tt.[status]
 177:        ,tt.[description]
 178:        ,tt.[procurementMethodType]
 179:        ,tt.[value_amount]
 180:        ,tt.[value_currency]
 181:        ,tt.[tenderPeriod_startDate]
 182:        ,tt.[tenderPeriod_endDate]
 183:        ,tt.[procuringEntity_name]
 184:        ,tt.[procuringEntity_id]
 185:        ,tt.[procuringEntity_address_region]
 186:        ,tt.[j_content]
 187:        ,tt.[CPV]
 188:        ,tt.[public_id]
 189:        ,tt.[to_handle]
 190:        ,tt.[auctionPeriod_startDate]
 191:        ,tt.[public_date]
 192:        ,tt.[is_test]
 193:        ,tt.[id_opr]
 194:        ,tt.[guarantee_amount]
 195:        ,tt.[dgfID]
 196:        ,tt.[regions]
 197:        ,tt.[auction_date]
 198:        ,tt.[can_public]
 199:        ,tt.[place_fee]
 200:        ,tt.[cdb]
 201:        ,tt.[rentSquare]
 202:        ,tt.[interest]
 203:        ,tt.[merchandisingObject]
 204:        ,tt.[tenderAttempts]
 205:        ,tt.[transfer]
 206:        ,tt.[contractID]
 207:        ,tt.[list_mark]
 208:        ,tt.[minimalStep_amount]
 209:        ,tt.[image]
 210:        ,tt.[additional_documentation]
 211:        ,tt.[adminDescription]
 212:      from
 213:        dbo.tender tt 
 214:        LEFT JOIN dbo.member m on m.id = tt.id_member
 215:      where
 216:         tt.[status] = 'active_tendering' 
 217:    ) AS t
 218:    LEFT JOIN dbo.dics s on s.id_object = 'tender' and t.status = s.id
 219:    LEFT JOIN dbo.TenderInList l ON t.id = l.id_tender
 220:    LEFT JOIN dbo.TenderInPopularList pl ON t.id = pl.id_tender
 221:    LEFT JOIN dbo.dics s3 on s3.id_object = 'tender_cdb3' and t.status = s3.id
 222:  GO

And after I have materialized it, it working instantly.




Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: http://www.vb-net.com/MaterializedView/Index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>