Re: [sqlite] Significant Performance Problem with System.Data.SQLite.
Ed Tenholder wrote: > > I have never used a profiling tool. Have VS 2010 installed. > Depending on which edition of Visual Studio 2010 is installed, you may be able to simply use their managed code profiling tools. Thankfully, these tools are fairly straightforward to use and produce nice results. Other than that, I'm not sure which profiler I would recommend for managed code. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Significant Performance Problem with System.Data.SQLite.
I have never used a profiling tool. Have VS 2010 installed. A quick web search shows several available free tools. What do you recommend for VS 2010 and SQLite? Are you able to run the application with profiling enabled to determine which SQL queries and/or System.Data.SQLite methods are running slowly? This information, if available, would be extremely helpful in tracking down the root cause of the slowdown you are observing. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Significant Performance Problem with System.Data.SQLite.
Ed Tenholder wrote: > > (5) There are at most three SqlDataSource controls on any page, and it is > possible that each would have the same data connection open simultaneously, > but all for read access, except when inserting or deleting a record. The > performance problem occurs when only populating the grids with data. Why > work on one system, but not the other? Why work on both systems when using > earlier version ? > Are you able to run the application with profiling enabled to determine which SQL queries and/or System.Data.SQLite methods are running slowly? This information, if available, would be extremely helpful in tracking down the root cause of the slowdown you are observing. > > (6) I have tried this app with the x64 assemblies, and have no luck > whatsoever. The download page indicates that only the x32 assemblies will > work with VS. > Having it work with Visual Studio during development and on Windows Server after it has been deployed are two entirely different scenarios. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Significant Performance Problem with System.Data.SQLite.
Thanks for the quick reply. (1) The queries are specified in the SqlDataSource. Examples: "SELECT * FROM [Scores] WHERE ([Player] = @Player) ORDER BY [ScoreDate] DESC" No more than a few hundred rows in the table. (2) I will look into the LogPrep and TraceListener and see what I can do (3) The DB file is located natively to the app on each machine I've tried, in the /App_Data folder of the website. (4) The system event log contains no errors. I am experiencing no other problems on the server nor with any other asp.net apps running on IIS. The only change to make it work is to use an older version of System.Data.SQLite.dll (5) There are at most three SqlDataSource controls on any page, and it is possible that each would have the same data connection open simultaneously, but all for read access, except when inserting or deleting a record. The performance problem occurs when only populating the grids with data. Why work on one system, but not the other? Why work on both systems when using earlier version ? (6) I have tried this app with the x64 assemblies, and have no luck whatsoever. The download page indicates that only the x32 assemblies will work with VS. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Monday, January 20, 2014 8:33 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Significant Performance Problem with System.Data.SQLite. Ed Tenholder wrote: > > The asp.net app is several pages, each with from one to three datagrids that > are filled using ADO.NET. Very little actual code. Using GridView, > DetailsView, DropDownList, and SqlDataSource > (ProviderName="System.Data.SQLite") controls. > Do you know what kind of queries these controls are using? Using the LogPrepare connection flag can help reveal the queries. In that case, the connection string would look something like: SQLiteConnection connection = new SQLiteConnection( "Data Source=test.db;Flags=LogPrepare;"); Please note that you will need a TraceListener setup in order to be able to see the output generated by this connection flag. > > Anyway, the page loads and the grids populate nearly instantaneously on the > development machine under VS2012, but when deployed to WinServer 2012, it > can take more than 20 seconds to populate the grids. > Is the database file on a network share? How many connections are trying to access the database concurrently? Perhaps the server is missing some patches to the .NET Framework? Maybe the server has other issues (e.g. file-system corruption, hardware issues, etc) that cause problems running System.Data.SQLite? Is there any trouble seen in the event logs? > > Note: Both machines are x64 and using NF4.0.Also, could only get ANY > of many versions of System.Data.SQLite.dll that I tried on WINServer 2012 by > configuring the AppPool to allow 32 bit execution. I don't understand that > either. .dll is located in /Bin folder. > There are x64 builds of System.Data.SQLite that should work in a native 64-bit process on Windows Server. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Significant Performance Problem with System.Data.SQLite.
Ed Tenholder wrote: > > The asp.net app is several pages, each with from one to three datagrids that > are filled using ADO.NET. Very little actual code. Using GridView, > DetailsView, DropDownList, and SqlDataSource > (ProviderName="System.Data.SQLite") controls. > Do you know what kind of queries these controls are using? Using the LogPrepare connection flag can help reveal the queries. In that case, the connection string would look something like: SQLiteConnection connection = new SQLiteConnection( "Data Source=test.db;Flags=LogPrepare;"); Please note that you will need a TraceListener setup in order to be able to see the output generated by this connection flag. > > Anyway, the page loads and the grids populate nearly instantaneously on the > development machine under VS2012, but when deployed to WinServer 2012, it > can take more than 20 seconds to populate the grids. > Is the database file on a network share? How many connections are trying to access the database concurrently? Perhaps the server is missing some patches to the .NET Framework? Maybe the server has other issues (e.g. file-system corruption, hardware issues, etc) that cause problems running System.Data.SQLite? Is there any trouble seen in the event logs? > > Note: Both machines are x64 and using NF4.0.Also, could only get ANY > of many versions of System.Data.SQLite.dll that I tried on WINServer 2012 by > configuring the AppPool to allow 32 bit execution. I don't understand that > either. .dll is located in /Bin folder. > There are x64 builds of System.Data.SQLite that should work in a native 64-bit process on Windows Server. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Significant Performance Problem with System.Data.SQLite.
I've finally finished converting from SQLserver Express to SQLite for an ASP.NET app on my development machine. VS 2010 Professional. System.Data.SQLite version 1.0.90.0, and 1.0.89.0 Got everything working great in development, but did I ever have problems deploying to Win Server 2012. Finally figured out (I think) the MANY downloads of System.Data.SQLite and was able to get everything properly loading and executing, except on Win Server 2012, the application ran VERY slowly, but did work properly. The asp.net app is several pages, each with from one to three datagrids that are filled using ADO.NET. Very little actual code. Using GridView, DetailsView, DropDownList, and SqlDataSource (ProviderName="System.Data.SQLite") controls. It's really not an ASP.NET app, but an ASP.NET Website. Anyway, the page loads and the grids populate nearly instantaneously on the development machine under VS2012, but when deployed to WinServer 2012, it can take more than 20 seconds to populate the grids. After days of trying every configuration of DLLs that I could think of, I finally achieved success by merely replacing the System.Data.SQLite.dll file that is version 1.0.90.0 (bundled) with a .dll file from an app I wrote a few years ago:System.Data.SQLite.dll version 1.0.60.0.Now, the grids load instantaneously on both systems! I would like to find out what is wrong, as I would prefer to keep current with SQLite releases. Any suggestions on next steps? Note: Both machines are x64 and using NF4.0.Also, could only get ANY of many versions of System.Data.SQLite.dll that I tried on WINServer 2012 by configuring the AppPool to allow 32 bit execution. I don't understand that either. .dll is located in /Bin folder. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
