SQL: Speed LIMITs (Using LIMIT for Speed in Your Queries)

Returning a tiny recordset when you or your users login? Your web application may be only one word away from untapped speed!

All standard SQL databases offer simple ways to limit returned results. We're not talking about WHERE clauses. I'd like to introduce you to the LIMIT clause. LIMIT comes after WHERE and ORDER BY clauses, naturally, since it's the "limiting" factor in your query.

In this tutorial we'll see a LIMIT clause not only bring us speed when returning results, but some additional security working with your database. This tutorial stays within the Dreamweaver interface and assumes you have working knowledge of defining a site, and have already established a connection to your database. To pratice along with this tutorial have some tables and data to play with. Now, on to the LIMIT-ing SQL!

1. Our starting SQL code will be:

   SELECT * FROM tbl_Users WHERE username = 'jsmith'

   

   Our starting code looks harmless at first, but as always with database design - it's always about preparation! Now your database/application will, of course, have more than just this data for users; the example table is shown for purpose of this tutorial.

2. A common reason to use LIMIT

   Let's say your users have an “Update/Edit Profile” dynamic page you made for them or edit/update anything for that matter, it's usually one thing at a time. You probably made it with Dreamweavers' UPDATE Recordset Server-Behavior. Your form in that page needs a recordset defined for the default (existing) data that's going to be displayed and updated. Normally that type of SQL query would look something like this:

   SELECT * FROM tbl_Users WHERE userID='?'

   The '?', of course, would end up being their userID number or something similar. Dreamweaver likes to default this as “colname” when you change to “Advanced...” SQL mode. While we have our recordset window open, let's click the “Advanced...” button to edit our generic SQL code.

   

   Now, the default SQL written is Dreamweaver asking for all records that have that userID. There shouldn't really be duplicate users or userID's when you tracking user data, so generally returning more than one record is totally unnecessary - hence using LIMIT here dictates that you want only a specified amount of records returned. Let's LIMIT this recordset to just one record – add the highlighted code into the SQL textarea:

   SELECT * FROM tbl_Users WHERE userID = colname LIMIT 1

   

   Remember, Dreamweaver default variables used in your generated SQL statement are titled “colname” so you may change that as you see fit, remember to change it in both the SQL & Variables box.

   By adding the LIMIT 1 we have ensured only one record will be pulled down. This is less work for your database because it won't be expecting to return more than 1 row. You're probably thinking, “Doesn't the WHERE clause limit it already?” You're right in this scenario that the WHERE clause is already limiting the returned data, but using LIMIT dictates to our database we require just the one row – resulting in a faster display of the page to our viewers.

   That was a small example. When you get into speeding up your application then you will definitely notice that using LIMIT, when necessary, is a boon for your users and your database overhead. Read on, to see further uses of LIMIT in your SQL.

3. Dreamweaver adds LIMIT to your SQL when you apply a Repeat Region with a set number of records to show at a time.

   If you are curious, then you want to watch what code Dreamweaver creates – split view is easiest to see what gets made. Start with the code example above, remove the LIMIT clause for now. Then apply, to that recordset,   a Repeat Region Server-Behavior with a limited number of “regions to show”. Dreamweaver automagically adjusts your recordset code to utilize the LIMIT clause in the SQL that has been written.

   

4. Let's compare the code before we apply a limited Repeat Region:

   

5. Now compare with code added after Repeat Region with a set number of records to show at a time:

   

   You'll clearly see it added a lot of code before and after your recordset. In particular, check out the highlighted row of code. This row of code appends the LIMIT clause to our original SQL query - at the end where it should be of course. To the right of the LIMIT clause, you'll notice the “%d, %d” , these are placeholders for digits (integers/numbers). Technically the LIMIT clause can use two numbers, think of it as recordset bookends – the first becomes the starting record number, and the other is the max limit of records. When you build sites with Dreamweavers' Recordset Navigation, so you can click page-to-page of records, it literally shifts through chunks of the recordset within it's limit. The starting record number keeps changing down, or up, as you go through a recordset while only returning the allowed max limit records to show.

   As your database is small you may not notice speed gains at first, but once the system has many rows, then using LIMIT will give your database that “fresh new car” smell everyday.

For additional SQL/Server-Side tips & tricks with limiting or paging recordsets:

• Complex Sorting and Paging ASP.Net and DWMX by Charles Stratton (intermediate-advanced)
• Premium Article: SQL: Paging by Rudy Limeback (beginner-intermediate)
• Premium Article: PHP Recordset Paging by Gareth Downes-Powell (intermediate)
• Premium Article: Paging and caching large record sets in ASP by Robert Stuttaford (intermediate)

Database documentation available online:

• MySQL: Online MySQL documentation with user comments
• Microsoft SQL Server: www.microsoft.com/sql/ (requires download)

If you are still learning to work with databases, these should be helpful:

• PHP
  • PHP and MySQL in Dreamweaver MX by Allan Kent (beginner)
  • PHP and Dreamweaver MX Continued by Allan Kent (beginner)
  • Premium Article: PHP Basics - Part 7: Connecting to a MySQL Database by Gareth Downes-Powell (beginner)
• ASP/ASP.NET
  • Creating Dynamic Web Sites with .Net and Dreamweaver MX by Matthew David (beginner)
• ColdFusion
  • Build Master/Detail page in ColdFusion by Omar Elbaga (beginner)