Prestandajustering och dataoptimering för R

gäller för: SQL Server 2016 (13.x) och senare versioner

I den här artikeln beskrivs prestandaoptimeringar för R- eller Python-skript som körs i SQL Server. Du kan använda dessa metoder för att uppdatera R-koden, både för att öka prestanda och för att undvika kända problem.

Välja en beräkningskontext

I SQL Server kan du använda antingen local eller SQL beräkningskontext när du kör R- eller Python-skript.

När du använder den lokala beräkningskontexten utförs analysen på datorn och inte på servern. Om du hämtar data från SQL Server att använda i koden måste data därför hämtas via nätverket. Prestandan för den här nätverksöverföringen beror på storleken på de data som överförs, nätverkets hastighet och andra nätverksöverföringar som sker samtidigt.

När du använder SQL Server beräkningskontext körs koden på servern. Om du hämtar data från SQL Server bör data vara lokala för den server som kör analysen och därför introduceras inga nätverkskostnader. Om du behöver importera data från andra källor bör du överväga att ordna ETL i förväg.

När du arbetar med stora datamängder bör du alltid använda SQL-beräkningskontexten.

Faktorer

R-språket har begreppet faktorer, som är en särskild variabel för kategoriska data. Dataexperter använder ofta faktorvariabler i sin formel, eftersom hantering av kategoriska variabler som faktorer säkerställer att data bearbetas korrekt av maskininlärningsfunktioner.

Avsiktligt kan faktorvariabler konverteras från strängar till heltal och tillbaka igen för lagring eller bearbetning. R-funktionen data.frame hanterar alla strängar som faktorvariabler, såvida inte argumentsträngarnaAsFactors är inställt på False. Det innebär att strängar automatiskt konverteras till ett heltal för bearbetning och sedan mappas tillbaka till den ursprungliga strängen.

Om källdata för faktorer lagras som heltal kan prestandan bli lidande, eftersom R vid körning konverterar faktorheltal till strängar och sedan utför sin egen interna sträng-till-heltalskonvertering.

Om du vill undvika sådana körningskonverteringar bör du överväga att lagra värdena som heltal i tabellen SQL Server och använda argumentet colInfo för att ange nivåerna för kolumnen som används som faktor. De flesta datakällobjekt i RevoScaleR tar parametern colInfo. Du använder den här parametern för att namnge variablerna som används av datakällan, ange deras typ och definiera variablernas nivåer eller transformeringar för kolumnvärdena.

Följande R-funktionsanrop hämtar till exempel heltalen 1, 2 och 3 från en tabell, men mappar värdena till en faktor med nivåerna "apple", "orange" och "banana".

c("fruit" = c(type = "factor", levels=as.character(c(1:3)), newLevels=c("apple", "orange", "banana")))

När källkolumnen innehåller strängar är det alltid effektivare att ange nivåerna i förväg med hjälp av parametern colInfo . Följande R-kod behandlar till exempel strängarna som faktorer när de läss.

c("fruit" = c(type = "factor", levels= c("apple", "orange", "banana")))

Om det inte finns någon semantisk skillnad i modellgenereringen kan den senare metoden leda till bättre prestanda.

Dataomvandlingar

Dataexperter använder ofta transformeringsfunktioner skrivna i R som en del av analysen. Transformeringsfunktionen tillämpas på varje rad som hämtas från tabellen. I SQL Server tillämpas sådana transformeringar på alla rader som hämtas i en batch, vilket kräver kommunikation mellan R-tolken och analysmotorn. För att utföra omvandlingen flyttas data från SQL till analysmotorn och sedan till R-tolkprocessen och tillbaka.

Därför kan användning av transformeringar som en del av R-koden ha en betydande negativ effekt på algoritmens prestanda, beroende på mängden data som ingår.

Det är mer effektivt att ha alla nödvändiga kolumner i tabellen eller vyn innan du utför analysen och undviker transformeringar under beräkningen. Om det inte går att lägga till ytterligare kolumner i befintliga tabeller bör du överväga att skapa en annan tabell eller vy med de transformerade kolumnerna och använda en lämplig fråga för att hämta data.

Läser batchrader

Om du använder en SQL Server datakälla (RxSqlServerData) i koden rekommenderar vi att du försöker använda parametern rowsPerRead för att ange batchstorlek. Den här parametern definierar antalet rader som efterfrågas och sedan skickas till det externa skriptet för bearbetning. Vid körning ser algoritmen endast det angivna antalet rader i varje batch.

Möjligheten att kontrollera mängden data som bearbetas i taget kan hjälpa dig att lösa eller undvika problem. Om ditt dataset till exempel är mycket brett (har många kolumner) eller datasetet har några stora kolumner (till exempel fritext) kan du minska batchstorleken för att undvika att data flyttas från minnet.

Som standard är värdet för den här parametern inställt på 50000 för att säkerställa anständiga prestanda även på datorer med lite minne. Om servern har tillräckligt med tillgängligt minne kan det ge bättre prestanda om du ökar det här värdet till 500 000 eller till och med en miljon, särskilt för stora tabeller.

Fördelarna med att öka batchstorleken blir uppenbara för en stor datamängd och i en uppgift som kan köras på flera processer. Att öka det här värdet ger dock inte alltid det bästa resultatet. Vi rekommenderar att du experimenterar med dina data och algoritmer för att fastställa det optimala värdet.

Parallell bearbetning

För att förbättra prestandan för rx analysfunktioner kan du utnyttja möjligheten att SQL Server köra uppgifter parallellt med hjälp av tillgängliga kärnor på serverdatorn.

Det finns två sätt att uppnå parallellisering med R i SQL Server:

• Använd @parallel. När du använder den sp_execute_external_script lagrade proceduren för att köra ett R-skript anger du parametern @parallel till 1. Det här är den bästa metoden om R-skriptet inte använder RevoScaleR-funktioner, som har andra mekanismer för bearbetning. Om skriptet använder RevoScaleR-funktioner (vanligtvis prefix med "rx" utförs parallell bearbetning automatiskt och du behöver inte uttryckligen ange @parallel till 1.

  Om R-skriptet kan parallelliseras, och om SQL-frågan kan parallelliseras, skapar databasmotorn flera parallella processer. Det maximala antalet processer som kan skapas är lika med maxdOP-inställningen ( maximal grad av parallellitet ) för instansen. Alla processer kör sedan samma skript, men tar bara emot en del av data.

  Den här metoden är därför inte användbar med skript som måste se alla data, till exempel när du tränar en modell. Det är dock användbart när du utför uppgifter som batchförutsägelse parallellt. Mer information om hur du använder parallellitet med sp_execute_external_script finns i avsnittet Avancerad tips: parallell bearbetning i Använder R Code i Transact-SQL.

• Använd numTasks =1. När du använder funktionerna rx i en SQL Server beräkningskontext anger du värdet för parametern numTasks till det antal processer som du vill skapa. Antalet processer som skapas får aldrig vara mer än MAXDOP. Det faktiska antalet processer som skapas bestäms dock av databasmotorn och kan vara mindre än du begärde.

  Om R-skriptet kan parallelliseras, och om SQL-frågan kan parallelliseras, skapar SQL Server flera parallella processer när rx-funktionerna körs. Det faktiska antalet processer som skapas beror på en mängd olika faktorer. Dessa omfattar resursstyrning, aktuell användning av resurser, andra sessioner och frågekörningsplanen för frågan som används med R-skriptet.

Frågeparallellisering

I Microsoft R kan du arbeta med SQL Server datakällor genom att definiera dina data som ett RxSqlServerData-datakällobjekt.

Skapar en datakälla baserat på en hel tabell eller vy:

RxSqlServerData(table= "airline", connectionString = sqlConnString)

Skapar en datakälla baserat på en SQL-fråga:

RxSqlServerData(sqlQuery= "SELECT [ArrDelay],[CRSDepTime],[DayOfWeek] FROM  airlineWithIndex WHERE rowNum <= 100000", connectionString = sqlConnString)

För att säkerställa att data kan analyseras parallellt bör den fråga som används för att hämta data ramas in på ett sådant sätt att databasmotorn kan skapa en parallell frågeplan. Om koden eller algoritmen använder stora mängder data kontrollerar du att frågan som ges till RxSqlServerData är optimerad för parallell körning. En fråga som inte resulterar i en parallell körningsplan kan resultera i en enda beräkningsprocess.

Om du behöver arbeta med stora datauppsättningar använder du Management Studio eller en annan SQL-frågeanalysator innan du kör R-koden för att analysera körningsplanen. Vidta sedan alla rekommenderade åtgärder för att förbättra frågans prestanda. Ett index som saknas i en tabell kan till exempel påverka den tid det tar att köra en fråga. Mer information finns i Övervaka och justera prestanda.

Ett annat vanligt misstag som kan påverka prestanda är att en fråga hämtar fler kolumner än vad som krävs. Om en formel till exempel bara baseras på tre kolumner, men källtabellen har 30 kolumner, flyttar du data i onödan.

• Undvik att använda SELECT *!
• Ta dig tid att granska kolumnerna i datauppsättningen och identifiera endast de som behövs för analys
• Ta bort alla kolumner som innehåller datatyper som är inkompatibla med R-kod från dina frågor, till exempel GUIDS och rowguids
• Sök efter datum- och tidsformat som inte stöds
• I stället för att läsa in en tabell skapar du en vy som väljer vissa värden eller kastar kolumner för att undvika konverteringsfel

Optimera maskininlärningsalgoritmen

Det här avsnittet innehåller diverse tips och resurser som är specifika för RevoScaleR och andra alternativ i Microsoft R.

Optimeringar för RevoScaleR

Många RevoScaleR-algoritmer stöder parametrar för att styra hur den tränade modellen genereras. Även om modellens noggrannhet och korrekthet är viktig kan algoritmens prestanda vara lika viktig. För att få rätt balans mellan noggrannhet och träningstid kan du ändra parametrar för att öka beräkningshastigheten och i många fall förbättra prestandan utan att minska noggrannheten eller korrektheten.

• rxDTree

  rxDTree stöder parametern maxDepth , som styr djupet i beslutsträdet. När maxDepth ökas kan prestandan försämras, så det är viktigt att analysera fördelarna med att öka djupet jämfört med försämring av prestanda.

  Du kan också styra balansen mellan tidskomplexitet och förutsägelsenoggrannhet genom att justera parametrar som maxNumBins, maxDepth, maxCompleteoch maxSurrogate. Att öka djupet till mer än 10 eller 15 kan göra beräkningen mycket dyr.

• rxLinMod

  Prova att cube använda argumentet om den första beroende variabeln i formeln är en faktorvariabel.

  När cube är inställt på TRUEutförs regressionen med en partitionerad invertering, vilket kan vara snabbare och använda mindre minne än standardberäkning för regression. Om formeln har ett stort antal variabler kan prestandavinsten vara betydande.

• rxLogit

  cube Använd argumentet om den första beroende variabeln är en faktorvariabel.

  När cube är inställt på TRUEanvänder algoritmen en partitionerad invertering, som kan vara snabbare och använda mindre minne. Om formeln har ett stort antal variabler kan prestandavinsten vara betydande.

Mer information om optimering av RevoScaleR finns i följande artiklar:

• Metoder för att kontrollera modellanpassning i en förstärkt trädmodell: Uppskatta modeller med stokastisk gradientförstärkning

• Översikt över hur RevoScaleR flyttar och bearbetar data: Skriva anpassade segmenteringsalgoritmer i ScaleR

• Programmeringsmodell för RevoScaleR: Hantera trådar i RevoScaleR

• Funktionsreferens för rxDForest

• Funktionsreferens för rxBTrees

Använda MicrosoftML

Vi rekommenderar också att du tittar på det nya MicrosoftML-paketet , som tillhandahåller skalbara maskininlärningsalgoritmer som kan använda beräkningskontexter och transformeringar som tillhandahålls av RevoScaleR.

• Kom igång med MicrosoftML

• Så här väljer du en MicrosoftML-algoritm

Nästa steg

• Information om R-funktioner som du kan använda för att förbättra prestanda för din R-kod finns i Använda R-kodprofileringsfunktioner för att förbättra prestanda.

• Mer fullständig information om prestandajustering på SQL Server finns i Performance Center för Databasmotor för SQL Server och Azure SQL Database.