Monday, 7 April 2014

Optimizing ASP.Net Identity Database Access

I have to say I’m a bit obsessive about issuing SQL requests as they take time, and so the fewer requests I can make the better.

Recently I’ve installed the latest version of ASP.NET Identity (2.0.0.0) and whilst running SQL Profiler I noticed something odd being executed for every instance of my DbContext class. What I was expecting was one SQL statement (that being the one I was explicitly issuing), however what I actually saw in the profiler was three…


(1) SELECT Count(*) FROM sys.databases WHERE [name]=N'VPC'


(2) exec sp_executesql N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@Table',N'@Table nvarchar(11)',@Table=N'AspNetUsers'


(3) SELECT
[Extent1].[PublisherId] AS [PublisherId],
[Extent1].[ApiKey] AS [ApiKey],
[Extent1].[Name] AS [Name],
[Extent1].[Description] AS [Description]
FROM [VPC].[Publisher] AS [Extent1]
ORDER BY [Extent1].[Name] ASC

The one I was expecting was #3, the other two were unexpected and also unwanted. So I had to find out where they were coming from.


When I start hunting I always think “it’s my fault, I’ve caused this somehow” – and to be honest it almost always is caused by my code. But I was pretty sure that this wasn’t my code – or at least, it wasn’t cause by my code directly. What followed was an hour or so of fruitless searching in my project – I began with an EF profiler, no change, then I added some extra debug, no change, wired up some logging for Owin to see if it was one of the bits of middleware added into the pipeline, still no change, none was telling me what I needed to know.


I then started hunting on the web, and happened to find just what I needed on www.symbolsource.org, specifically the code for the IdentityDbContext class (which was where I’d hazarded a guess the problem was). I was (as most people will do) deriving from IdentityDbContext<TUser> which I’d assumed was benign but it wasn’t. It contains the following code in the default constructor…

  public IdentityDbContext(string nameOrConnectionString)
: this(nameOrConnectionString, true)
{
}

And looking at the next constuctor...

  public IdentityDbContext(string nameOrConnectionString, bool throwIfV1Schema)
: base(nameOrConnectionString)
{
if (throwIfV1Schema && IsIdentityV1Schema(this))
{
throw new InvalidOperationException(IdentityResources.IdentityV1SchemaError);
}
}

Not much going on there - or is there?. If you look at the code for the IsIdentityV1Schema function it reveals all - it's checking if the database exists, and it's also doing the column name lookup.


So, to remove the two additional SQL statements from your code, you need to pass false as the second parameter to the IdentityDbContext constructor…

  public class DbApplicationRepository 
: IdentityDbContext, IDbApplicationRepository
{
public DbApplicationRepository(string connectionString)
: base(connectionString, false)
{
}

...
}

Hopefully this will help someone!

29 comments:

Unknown said...

Awesome, thanks, fixed the issue fore me!

Unknown said...

thanks for awesom post
asp.net training in jaipur

Nick said...

Thanks for posting this!

Richard Majece said...

I don't like to waste my time and I prefer optimising. For example, when I need to write argumentative essay, this writing info helps me a lot.

Jacob Foshee said...

You are not alone. These DB calls can be expensive, especially if they need to travel around the world. Thanks so much for sharing.

Kelly0989 said...

Don't stress out. Stop being so obsessive about your data dictionary sample. If you need some help just let me know, I have a few tips to share.

ahmed5987 said...

تتضاعف جميع قطع الأثاث في الصناديق عند الحاجة ، أو يمكن الاستمتاع بها فقط كأثاث جديد أو فريد. يمكنك أيضًا شراء خطط ومجموعات أثاث / صُنع لتصنعها بنفسك. أثاث النعش يأخذ أيضا أوامر مخصصة.شركة نقل عفش
شركة نقل اثاث من الرياض الى جدة

شركة نقل عفش من الرياض الى جدة
شركة نقل عفش بالجبيل

Nora Pittman said...

Crypto-currency as a modern form of the digital asset has received a worldwide acclaim for easy and faster financial transactions and its awareness among people have allowed them to take more interest in the field thus opening up new and advanced ways of making payments. Crypto.com Referral Code with the growing demand of this global phenomenon more,new traders and business owners are now willing to invest in this currency platform despite its fluctuating prices however it is quite difficult to choose the best one when the market is full. In the list of crypto-currencies bit-coins is one of the oldest and more popular Crypto.com Referral Code for the last few years. It is basically used for trading goods and services and has become the part of the so-called computerized block-chain system allowing anyone to use it thus increasing the craze among the public, Crypto.com Referral Code.

Common people who are willing to purchase BTC can use an online wallet system for buying them safely in exchange of cash or credit cards and in a comfortable way from the thousands of BTC foundations around the world and keep them as assets for the future. Due to its popularity, many corporate investors are now accepting them as cross-border payments and the rise is unstoppable. With the advent of the internet and mobile devices,information gathering has become quite easy as a result the BTC financial transactions are accessible and its price is set in accordance with people’s choice and preferences thus leading to a profitable investment with Crypto.com Referral Code. Recent surveys have also proved that instability is good for BTC exchange as if there is instability and political unrest in the country due to which banks suffer then investing in BTC can surely be a better option. Again bit-coin transaction fees are pretty cheaper and a more convenient technology for making contracts thus attracting the crowd. The BTC can also be converted into different fiat currencies and is used for trading of securities, for land titles, document stamping, public rewards and vice versa.

Another advanced block-chain project is Ethereumor the ETH which has served much more than just a digital form of crypto-currency Crypto.com Referral Code and its popularity in the last few decades have allowed billions of people to hold wallets for them. With the ease of the online world,the ETH have allowed the retailers and business organizations to accept them for trading purposes, therefore, can serve as the future of the financial system.

rj said...

Archie 420 Dispensary is a trusted Cannabis dispensary base in Los Angeles California USA. It is one of the top dispensary in this part of the country. They do deliver Marijuana in the USA and to over 25 countries in the world. Blackberry kush weed for sales You can always visit their dispensary in Los Angeles using the address on their website. Place your order and get served by the best dispensary in the planet. Have fun.

freya maya said...

Crystal online pharmacy is a trusted online drug store with a wide range of products to suit the needs of our clients. Buy research chemicals online Crystal Pharmacy do strive to offer the best service and ship products world wide. All the products listed on our website are Ava in stock. Expect your order to be processed Immediately when you send us your request. We deal with varieties of drugs for our customers satisfaction. We cross barriers with our products and struggle hard to meet human satisfaction. When shopping with us, Be safe and secured and you will realize how swift we are with our services.

freya maya said...

Crystal online pharmacy is a trusted online drug store with a wide range of products to suit the needs of our clients. Buy research chemicals online Crystal Pharmacy do strive to offer the best service and ship products world wide. All the products listed on our website are Ava in stock. Expect your order to be processed Immediately when you send us your request. We deal with varieties of drugs for our customers satisfaction. We cross barriers with our products and struggle hard to meet human satisfaction. When shopping with us, Be safe and secured and you will realize how swift we are with our services.

Rubel hossen said...

Universal Gun sales is a trusted Firearm company base in Los Angeles California USA. It is one of the top Firearms Company in this part of the country. Buy Burris MTAC 30 riffle-scope online They do offer the best firearms deal in the USA and to over 25 countries in the world. You can always visit their shop in Los Angeles using the address on their website. Place your order and get served by the best Firearm Company in the planet. Have fun.

Friends said...

PG SLOT มีเกมหลากหลายให ้เลือก พรอ้ มกราฟฟิกแอนนเิมชนั่ สวยงามทสี่ ดุ ในยคุ นี้แจ็คพอตแตกง่าย pgslot โปรโมชนั่ พเิศษส าหรับทงั้สมาชก เกา่ และสมาชกิ ใหม่ ระบบฝาก-ถอนเงินออโต ้เงินเข ้าเร็วทันใจมีแอดมิ ดูแลตลอด 24 ชวั่ โมง ต ้องที่ pgslot.bid มั่นคงจ่ายจริง ไม่ว่าคุณจะถอนเงิ มากมายเท่าไหร่ ทางเว็บจ่ายเต็มไม่มีหัก pgslot ปั่นง่ายแตกง่าย

pg slot ผู ้ให ้บริการเว็บตรง ที่แตกง่ายที่สุด ปั่นสนุก ฝากเงินรับเครดิตฟร สูงสุด 15000 บาท สมัคร พีจีสล็อต หรือ PGSLOT กับเราเพื่อความมั่นคงของคุณเอง.

Anonymous said...

แจ็คพอตแตกเร็ว ได้เงินจริงเข้ามาได้แล้วที่เว็บไซต์ สล็อตPG เว็บไซต์ที่นักเดิมพันจะพบกับสุดยอดเกมทำเงิน ที่สร้างสร้างรายได้ให้นักเดิมพันได้จริง ไม่มีบริการฝากเงินขั้นต่ำ เป็นเกมจากเว็บตรงที่ไม่มีการผ่านเอเย่นต์ หรือตัวกลางใดๆ สามารถร่วมสนุกกันได้อย่างไม่ต้องกังวลา อีกทั้งเกมทุกเกมยังถูกสร้างและออกแบบมาเป็นอย่างดี พบกับของขวัญและค่าตอบแทนมากมายภายในเกม

PGSLOT said...

!! โบนัสแตกง่าย ลงเดิมพันง่าย จ่ายจริง พร้อมเลือกรับโปรโมชั่นเพียบและฟาดเรียบทุกเงินรางวัลใน เว็บสล็อต เว็บเกมสล็อตออนไลน์อันดับ 1 ในประเทศไทย เราพร้อมแจกโบนัสและแจ็คพอตใหญ่ให้ผู้เล่นลุ้นรับได้ตั้งแต่ครั้งแรกที่ลงเดิมพัน เรารับรองเลยว่าคุณจะได้รับสิทธิประโยชน์สูงสุด ฝากถอนได้ทันที ไม่มีอั้น เราคือสุดยอดเว็บเกมพนันออนไลน์ที่ฮิตมากที่สุดในปัจจุบัน ผู้เล่นสามารถเลือกเล่นเกมและสนุกสนานกับเราได้ทุกที่ทุกเวลา ฝากถอนได้รวดเร็วทันตา ผ่านแอพพลิเคชั่นสุดล้ำสมัยอย่าง True Money Wallet !!

koko said...

แหล่งรวมตัวของเหล่านักเดิมพันที่พบกันโดยมิได้นัดหมาย MEGA GAME เว็บเกมสล็อตออนไลน์ แหล่งรวบรวมความสนุกของเกมสล็อตไว้ให้กับนักเดิมพันทุกเพศ ทุกวัย ได้มารวมตัวกัน https://www.megagame.ai
เว็บเกมของเรามีเกมสล็อตมากมายให้เลือกเล่นกันอย่างจุใจ พร้อมกับโบนัส แตกง่าย แตกไว แจกให้แบบไม่มีอั้น เล่นง่ายจ่ายจริง เราคอยบริการความสนุกให้กับท่านตลอด 24 ชั่วโมง

AMB said...

เมื่อเลี้ยงน้องหมาน้องเเมวเเล้ว การที่สัตว์เลี้ยงขงเรามีคุณภาพก็จะช้วยให้ผู้คนรอบข้างมีความปลอดภัย dertdai เราได้รวบรวมเทคนิคการดูเเลสัตว์เลี้ยง ไม่วา่จะเป็นน้องหมา หรือน้องเเมว มาไว้ที่นี่เเล้ว !! ดูคลิก การดูเเลอย่างถูกวิธี เพิ่มความเข้าใจในการเลี้ยงอย่างถูกวิธีเชิญที่นี่เลย.

tiner said...

The ball fell into the wheel while it was spinning. Including this soccer ball will end up in one of the numbered boxes. before each rotation slotxo เว็บตรง

tiner said...

At night, the bonuses are scattered.
In playing online slots games at night, it can be considered as another channel. that you can make a profit from gambling in slots that night including most importantly, สล็อตแตกง่าย 2022

ANKIT said...

Are you searching for the best online reputation management services in india and USA ? Then you are in the right place. Yes, today I’m going to guide with ripoff report, a better solution to permanently remove cheaterland from SERP. Do you want to get rid of the remove ripoff report from search engine ? myreputationvault provides the best ripoff report removal services in india and usa. ensuring a quick solution to remove ripoff reports or negative reviews from Google or other search engines.

pgslot.game said...

สมัคร เว็บ pg slot โดยไม่ต้องใช้ gmail การสมัครสมาชิคของเว็บไซต์ ส่วนมากจะต้องใช้ e-mail ที่มันยุ่งยากมาก แต่สำหรับเว็บ PG SLOT ของเรานั้น ใช้เพียงแค่เบอร์โทรศัพท์เท่านั้นเอง pg slot

ravensky867 said...

GTA Online: 모바일홀덤 바둑이게임 Heist - All Scope Out Photo Locations

alaiazoey239 said...

Why The Trump Taj Mahal 실시간파워볼 파워볼 커뮤니티 Failed

Joan said...

Here we are Providing Best Template Solution at free of Cast For all kind of Business and Examples, YOu just need to Find to your desire Topic and Download the family tree templates

rj said...

Just 99 Web Design offers affordable web design packages for every business, from basic one-page websites to full eCommerce website design services. inexpensive web design companies

vapecigaqiop said...

heating masturbator cup has a one-touch pause and long-press boost function for more intense orgasms.

JAke Leonel said...

I praise every assessment produced in this article. Hope others will additionally enjoy to review it. Outstanding tips discussed with excellent option of words and expressions.
rotating masturbator cup
male masturbation cup

Angel17 said...

I find this post so informative. Thanks for sharing this. So useful! 30 yard dumpster rental Golden Gate FL

Hill Country Eye Center said...

Dr. Vincent A is widely recognized as the Best cataract surgeon in texas. With over 20 years of experience, she has earned a stellar reputation for her exceptional surgical skills and commitment to patient care. Dr. Vincent A is board-certified and has performed thousands of successful cataract surgeries, utilizing the latest advancements in technology. Patients praise her for her thorough consultations, clear communication, and compassionate approach.

Her practice, located in Houston, attracts individuals seeking top-notch eye care. Dr. Vincent A dedication to excellence, coupled with her impressive track record, solidifies her status as the go-to cataract surgeon in the Lone Star State.