C # imitya ukubulala cwaka yakho SQL Server indexes in Dapper

Iintambo ze-C# Zithi cwaka zikrwitsha ukusebenza kweDatabase yakho

Ukuba ungumphuhlisi we.NET usebenzisa iDapper ukufikelela kwidatha yakho, wenze ukhetho olukhulu lokusebenza kunye nokulula. I-Dapper yi-micro-ORM emangalisayo ekugcina ukufutshane nentsimbi, ukuphepha ukugqithiswa kunye nobunzima bezakhelo ezinkulu. Kodwa eli gunya liza noxanduva. Umkhwa obonakala umsulwa wokubhala iikhowudi, uxhaphakile kwizicelo ze-C#, ungonakalisi usetyenziso lwe-SQL Server yakho: usebenzisa umtya ongaphakathi kwimibuzo ye-SQL. Lo mkhwa ubulala ngokuthe cwaka ukusebenza kwezalathisi zakho ezicwangciswe ngononophelo, ezikhokelela kwimibuzo ebuvila kunye namava angalunganga omsebenzisi. Kumaqonga afana ne-Mewayz, apho ukuphathwa kwedatha okusebenzayo kubalulekile ekulawuleni imisebenzi yeshishini, oku kukubulala okusebenzayo ongenakukwazi ukukuhlawulela.

Umlingo weSalathiso kunye noMsindisi weParameterized

Kuqala, masiqonde ukuba kutheni izalathisi zibaluleke kangaka. Isalathiso sedatabase sifana nesalathiso esisencwadini; ivumela i-SQL Server ukuba ifumane idatha ngaphandle kokuskena iphepha ngalinye (okanye umqolo). Xa uqhuba umbuzo ngegatya elithi `PHI`, isilungisi sombuzo sijonga esona salathisi esinokusisebenzisa. Undoqo kulo mlingo kukuqikelelwa. Xa usebenzisa umbuzo weparameterized, unika isixhasi ipateni ecacileyo, engaguqukiyo ukusebenza nayo.

Nanku umahluko. Qwalasela le mizekelo mibini yeDapper:

// Oku KUBI - Ukudityaniswa komtya
var userId = "12345";
var sql = $"KHETHA * UKUSUKA Abasebenzisi APHO UserId = {userId}";
var user = uxhumano.Umbuzo(sql);

nxamnye

// Oku KULUNGILE - Umbuzo owenziwe ngeParametered
var sql = "KHETHA * UKUSUKA Abasebenzisi APHO UserId = @UserId";
var umsebenzisi = uxhumano. Umbuzo (sql, entsha {UserId = 12345});

Umzekelo wokuqala wenza umtya weSQL owahlukileyo kuwo wonke owahlukileyo `userId`. Ngokwembono ye-SQL Server, ibona umbuzo omtsha ngokupheleleyo ngexesha ngalinye: enye ye `UserId = 12345`, enye ye `UserId = 67890`, njalo njalo. Umzekelo wesibini uthumela efanayo umtya wombuzo ngalo lonke ixesha, utshintsha kuphela ixabiso leparameter. Oku kuhambelana sisiseko sokwenziwa kombuzo osebenzayo.

Njani iCwangciso lokuCwangcisa loMtya we-String Literals Sabotage Caching

Undoqo wengxaki ukwiCache yeCwangciso loMbuzo. Umncedisi weSQL uqokelela umtya wakho weSQL kwisicwangciso sophumezo-iplani yendlela yokufumana kwakhona idatha. Olu qulunqo luyabiza, ngoko ke i-SQL Server igcina ezi zicwangciso ukuze ziphinde zisetyenziswe. Ngemibuzo yeparameterized, isicwangciso sokuba `KHETHA * UKUSUKA Abasebenzisi APHO UserId = @UserId` ihlanganiswe kube kanye, igcinwe, kwaye isetyenziswe kwakhona kumnxeba ngamnye olandelayo, kungajongwanga elona xabiso le-ID. Esi sicwangciso sigciniweyo siyilelwe ukusebenzisa ngokufanelekileyo isalathiso esikuluhlu `UserId`.

Xa usebenzisa umtya ongaphakathi, ixabiso ngalinye elilodwa livelisa umtya owodwa weSQL. Iseva yeSQL iphatha nganye njengombuzo omtsha kraca, iyinyanzele ukuba ichithe imijikelo ye-CPU ekuhlanganiseni kunye nokudala isicwangciso esitsha sokusebenza ngalo lonke ixesha. Oku kukhawuleza kukhukulise i-cache yesicwangciso ngokuphantse kufane, izicwangciso zokusetyenziswa kanye, ukukhupha ezinye izicwangciso eziluncedo kunye nokumosha inkumbulo. Okubaluleke ngakumbi, isilungisi sihlala singakwazi ukusebenzisa isalathiso esisiso kule mibuzo inye, ngamanye amaxesha ikhokelela kwiskeni setafile endaweni yokufuna. Isalathisi sakho sokusebenza okuphezulu siba sisihombiso esingenamsebenzi.

Impembelelo yoMsebenzi ongenakuyihoya

Iziphumo zale-anti-pattern zinzima kwaye zixakeke ngokuhamba kwexesha.

• Usetyenziso oluPhezulu lwe-CPU: Umbuzo othe rhoqo ukuqokelelwa konyusa i-CPU yeseva yedatha yakho.
• Amaxesha okuPhendula uMbuzo ocothayo: Imibuzo ithatha ixesha elide kuba iphosa i-cache kwaye inokwenza iskeni esipheleleyo setafile.
• Cwangcisa i-Cache Bloat: I-cache ivalwe ngezicwangciso zokusetyenziswa kanye, ilimaza ukusebenza kwayo yonke imibuzo kwiseva.
• Imingcipheko yoKhuseleko: Le ndlela ivula ucango lohlaselo lwenaliti ye-SQL, ubuthathaka obubalulekileyo obunqandwa yimibuzo ngokwendalo.

Kwinkqubo yokusebenza yeshishini efana ne-Mewayz, ejongana nedatha yemodyuli entsonkothileyo yeenkampani, le miba inokuphazamisa ukuphendula kwesicelo, ibe nefuthe ngokuthe ngqo kwimveliso yomsebenzisi kunye nokwaneliseka.

Ukulungisa Ingxaki: Yamkela iiParameters kwaye uphonononge iKhowudi Yakho

Isisombululo silula kwaye sihambelana neendlela ezingcono ekumele ukuba sele uzilandelayo. Soloko usebenzisa imibuzo parameterized kunye Dapper. I-Dapper yenza oku kube lula ngokumangalisayo ngokukuvumela ukuba udlule iiparamitha njengezinto ezingaziwayo okanye iiparamitha eziguqukayo. Oku akukhuseli kuphela isicelo sakho ngokuchasene nenaliti ye-SQL kodwa kukwaqinisekisa ukuba imibuzo yakho ikwi-cache-friendly kwaye inokunyusa ngokufanelekileyo izalathisi zakho.

Ukongeza, jonga rhoqo i-cache yesicwangciso se-SQL Server yakho. Jonga inani eliphezulu lemibuzo ethi "Adhoc", ehlala iluphawu oluxelayo lwale ngxaki. Sebenzisa izixhobo ezifana ne-SQL Server Management Studio (SSMS) ukuhlalutya ukusebenza kombuzo kunye nokuchonga izikena apho kufuneka kwenzeke khona. Ngokwamkela iparameterization kunye nokubeka iliso kwangaphambili, uvula amandla apheleleyo oluhlu lwakho lwedatha, uqinisekisa ukuba amaqonga afana neMewayz anokunikezela ngokukhawuleza, ukusebenza okuthembekileyo okufunwa ngamashishini anamhlanje.

Imibuzo Ebuzwa Rhoqo

Iintambo ze-C# Zithi cwaka zikrwitsha ukusebenza kweDatabase yakho

Ukuba ungumphuhlisi we.NET usebenzisa iDapper ukufikelela kwidatha yakho, wenze ukhetho olukhulu lokusebenza kunye nokulula. I-Dapper yi-micro-ORM emangalisayo ekugcina ukufutshane nentsimbi, ukuphepha ukugqithiswa kunye nobunzima bezakhelo ezinkulu. Kodwa eli gunya liza noxanduva. Umkhwa obonakala umsulwa wokubhala iikhowudi, uxhaphakile kwizicelo ze-C#, ungonakalisi usetyenziso lwe-SQL Server yakho: usebenzisa umtya ongaphakathi kwimibuzo ye-SQL. Lo mkhwa ubulala ngokuthe cwaka ukusebenza kwezalathisi zakho ezicwangciswe ngononophelo, ezikhokelela kwimibuzo ebuvila kunye namava angalunganga omsebenzisi. Kumaqonga afana ne-Mewayz, apho ukuphathwa kwedatha okusebenzayo kubalulekile ekulawuleni imisebenzi yeshishini, oku kukubulala okusebenzayo ongenakukwazi ukukuhlawulela.

Umlingo weSalathiso kunye noMsindisi weParameterized

Kuqala, masiqonde ukuba kutheni izalathisi zibaluleke kangaka. Isalathiso sedatabase sifana nesalathiso esisencwadini; ivumela i-SQL Server ukuba ifumane idatha ngaphandle kokuskena iphepha ngalinye (okanye umqolo). Xa uqhuba umbuzo ngegatya elithi `PHI`, isilungisi sombuzo sijonga esona salathisi esinokusisebenzisa. Undoqo kulo mlingo kukuqikelelwa. Xa usebenzisa umbuzo weparameterized, unika isixhasi ipateni ecacileyo, engaguqukiyo ukusebenza nayo.

Njani iCwangciso lokuCwangcisa iString Literals Sabotage Caaching

Undoqo wengxaki ukwiCache yeCwangciso loMbuzo. Umncedisi weSQL uqokelela umtya wakho weSQL kwisicwangciso sophumezo-iplani yendlela yokufumana kwakhona idatha. Olu qulunqo luyabiza, ngoko ke i-SQL Server igcina ezi zicwangciso ukuze ziphinde zisetyenziswe. Ngemibuzo yeparameterized, isicwangciso sokuba `KHETHA * UKUSUKA Abasebenzisi APHO UserId = @UserId` ihlanganiswe kube kanye, igcinwe, kwaye isetyenziswe kwakhona kumnxeba ngamnye olandelayo, kungajongwanga elona xabiso le-ID. Esi sicwangciso sigciniweyo siyilelwe ukusebenzisa ngokufanelekileyo isalathiso esikuluhlu `UserId`.

Impembelelo yoMsebenzi ongenakuyihoya

Iziphumo zale-anti-pattern zinzima kwaye zixakeke ngokuhamba kwexesha.

Ukulungisa Ingxaki: Yamkela iiParameters kwaye uphonononge iKhowudi Yakho

Isisombululo silula kwaye sihambelana neendlela ezingcono ekumele ukuba sele uzilandelayo. Soloko usebenzisa imibuzo parameterized kunye Dapper. I-Dapper yenza oku kube lula ngokumangalisayo ngokukuvumela ukuba udlule iiparamitha njengezinto ezingaziwayo okanye iiparamitha eziguqukayo. Oku akukhuseli kuphela isicelo sakho ngokuchasene nenaliti ye-SQL kodwa kukwaqinisekisa ukuba imibuzo yakho ikwi-cache-friendly kwaye inokunyusa ngokufanelekileyo izalathisi zakho.