Re: [sqlite] FW: Need Help with Golf Handicap Calculation
I thought LIMIT did the same thing as TOP. With both LIMIT (or TOP) and ORDER BY, my assumption (and my experience) is that the result is sorted, and then the first NN are returned. tenholde -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Sunday, February 09, 2014 6:04 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] FW: Need Help with Golf Handicap Calculation On Sat, 8 Feb 2014 19:47:44 + Ed Tenholder wrote: > Query: SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96 > FROM (SELECT * FROM (SELECT * FROM (SELECT > ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith" > ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20) > ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10) That's hard to read, so I'm relying on your description, > Logic: > > ? Select the oldest N scores (3 in the example above) > ? From that, select the 20 newest scores > ? From that, select the 10 lowest handicap-indexes: > (Score-Rating)*(113/Slope) ? Return the lowest ScoreDate and the > average of the handicap-indexes multiplied by .96 If you're learning SQL, you'll want to know that in the SQL standard LIMIT does not exist and ORDER BY is valid only to order the final output. I describe ranking rows in a general way at http://www.schemamania.org/sql/#rank.rows. HTH. --jkl ___ 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
[sqlite] FW: Need Help with Golf Handicap Calculation
From: Ed Tenholder Sent: Saturday, February 08, 2014 1:44 PM To: 'sqlite-users@sqlite.org' Subject: Need Help with Golf Handicap Calculation I’m just trying to learn SQL, and after lots of google searches and reading posts on this email list, I’ve gotten pretty close. Table: CREATE TABLE Scores (ScoreID Integer Primary Key,ScoreDate Text,Player Text,CourseName Text,TeeName Text,Score Integer,Rating Real,Slope Integer); Query: SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96 FROM (SELECT * FROM (SELECT * FROM (SELECT ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith" ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20) ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10) Result: MAX(ScoreDate)AVG((Score-Rating)*(113.0/Slope))*.96 2000-05-16 29.2436825396825 Logic: • Select the oldest N scores (3 in the example above) • From that, select the 20 newest scores • From that, select the 10 lowest handicap-indexes: (Score-Rating)*(113/Slope) • Return the lowest ScoreDate and the average of the handicap-indexes multiplied by .96 The first SELECT is there because I am going to execute this query iteratively, substituting for the “3”,from 1 to the count of total records (so I can create a chart of the change in handicap over time) The flaw is that the ScoreDate that is returned is the oldest date in the lowest 10 records, and what I need is the oldest date in the most recent 20 records (from the sub-query). I cannot figure out how to do this without breaking up the query using temp tables (which I can do, but I am interested in learning more about SQL and I’m sure there must be a way to do this (if you can solve Soduko puzzles!) Thanks for any help, Ed t. ___ 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.
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
[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
