Extraneous LOWER() calls in Membership provider
Extraneous LOWER() calls in Membership provider
Hi,
We are discovering that there are apparently extraneous calls to LOWER() in the Membership Provider's select statements. These slow the queries down tremendously and are completely unnecessary when the MySQL database is set to case insensitivity. Is there any way to remove the LOWER() calls via a config file?
We are running the very latest dotConnect for MySQL.
I need to point out that this is mission critical for us. Our Membership table has hundreds of thousands of rows and the LOWER() calls force a _full table scan_ of the membership table. This obviously slows things down tremendously. We have captured the SELECT calls and removing the LOWER() calls from the SELECT speeds things up by an order of magnitude. I cannot find a way to open a trouble ticket, so I guess this is the only way to do so
Thanks..
We are discovering that there are apparently extraneous calls to LOWER() in the Membership Provider's select statements. These slow the queries down tremendously and are completely unnecessary when the MySQL database is set to case insensitivity. Is there any way to remove the LOWER() calls via a config file?
We are running the very latest dotConnect for MySQL.
I need to point out that this is mission critical for us. Our Membership table has hundreds of thousands of rows and the LOWER() calls force a _full table scan_ of the membership table. This obviously slows things down tremendously. We have captured the SELECT calls and removing the LOWER() calls from the SELECT speeds things up by an order of magnitude. I cannot find a way to open a trouble ticket, so I guess this is the only way to do so
Thanks..
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Extraneous LOWER() calls in Membership provider
Yes, you are right. We are using the LOWER function in select queries. We will investigate the possibility to remove this function from our queries in the Membership provider and post here about the results.
Re: Extraneous LOWER() calls in Membership provider
Hello,
Does Devart know when it can resolve this issue?
I am working to convert a large Membership database (almost a million rows/users) from SQL to MySQL, using Devart's provider. Queries that are sub-second with MSFT's SQL Membership provider can literally take *minutes* using MySQL/Devart because LOWER() forces a full table scan for EACH user login. If I replace the underlying query to remove the LOWER(), it is fast. Without this fix, it appears dotConnect/MySQL cannot be used for an application that has many users.
Please let me know if you need help to reproduce this problem.
Thank you.
Does Devart know when it can resolve this issue?
I am working to convert a large Membership database (almost a million rows/users) from SQL to MySQL, using Devart's provider. Queries that are sub-second with MSFT's SQL Membership provider can literally take *minutes* using MySQL/Devart because LOWER() forces a full table scan for EACH user login. If I replace the underlying query to remove the LOWER(), it is fast. Without this fix, it appears dotConnect/MySQL cannot be used for an application that has many users.
Please let me know if you need help to reproduce this problem.
Thank you.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Extraneous LOWER() calls in Membership provider
We have already reproduced the issue.euph411 wrote:Please let me know if you need help to reproduce this problem.
We are working on the issue and will post here when we get any results. At the moment we cannot tell you when exactly the issue will be fixed.
Re: Extraneous LOWER() calls in Membership provider
Hi,
I thought I'd check in to see if there was any ETA to solve this issue? We remain unable to use Devart's provider in our production environment because it is too slow due to the Lower() table scan problem against thousands of user records.
Thank you!
I thought I'd check in to see if there was any ETA to solve this issue? We remain unable to use Devart's provider in our production environment because it is too slow due to the Lower() table scan problem against thousands of user records.
Thank you!
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Extraneous LOWER() calls in Membership provider
Sorry for the late response. The investigation is in progress. We will post here when we get any results. There is no ETA at the moment.
Re: Extraneous LOWER() calls in Membership provider
The new caseSensitive parameter (by default, false) is added to ASP.NET web providers configuration options to remove LOWER from the generated SQL queries. We will notify you when the new public build of dotConnect for MySQL is available for download.
Re: Extraneous LOWER() calls in Membership provider
Hi,
I have downloaded the latest code which you have sent an email to us about which should have this fix in it.
Your email said:
When will a build that actually works be produced?
I have downloaded the latest code which you have sent an email to us about which should have this fix in it.
Your email said:
The result was that I got a "trial expired" error. We do not have the capability to test on another machine nor in a virtual machine right now.The internal trial build with the fix: https://www.devart.com/pub/dcmysql_8_12_1206.exe . If you get "trial expired" message on your computer, please test it on a different (or virtual) workstation.
Notify us about the result.
When will a build that actually works be produced?
Re: Extraneous LOWER() calls in Membership provider
We have answered you by email.
Re: Extraneous LOWER() calls in Membership provider
New build of dotConnect for MySQL 8.12.1216 is available for download now: viewtopic.php?f=2&t=37638.