# Why would adding LIMIT slow down a SQL query?

**URL:** <https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807>\
**Category:** Classic Servoy\
**Created:** [February 14, 2014, 12:19pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807 "2014-02-14T12:19:56Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![grahamg](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@grahamg](https://forum.servoy.com/u/grahamg)\
**Post date:** [February 14, 2014, 12:19pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807/1 "2014-02-14T12:19:56Z")

</div>

Could any of the SQL gurus please educate me on why limiting a SQL query to the last 100 records would cause an unacceptable slowdown in the Query results?

Documents table has about 400k records with ‘iddc’ being the Integer PK. The code below limits to 100 records - an identical Query without the limit (vServer, query, args, -1) produces much faster results.

```auto
		var query = "SELECT iddc FROM documents WHERE email_account = ? AND doc_type = ? ORDER BY doc_date desc";
		
		var args = new Array()
		args[0] = vEMaccount;		
		args[1] = vDocType;
		
		var dataset = databaseManager.getDataSetByQuery(vServer, query, args, 100);

```

 ![Screen Shot 2014-02-14 at 12.06.12.png](https://canada1.discourse-cdn.com/flex010/uploads/servoy/original/2X/4/49d0c0f92f45495a33206ab910ec26d97f6909d7.png)

---

<div class="post-metadata">

**Author:** ![lwjwillemsen](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lwjwillemsen/32/4987_2.png) [@lwjwillemsen](https://forum.servoy.com/u/lwjwillemsen)\
**Post date:** [February 14, 2014, 2:30pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807/2 "2014-02-14T14:30:17Z")

</div>

Hi Graham,

I guess you don’t have an index on the column doc\_date ?  
In that case the database process to obtain the sorted first 100 records could take some time…

Regards,

---

<div class="post-metadata">

**Author:** ![grahamg](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@grahamg](https://forum.servoy.com/u/grahamg)\
**Post date:** [February 14, 2014, 3:29pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807/3 "2014-02-14T15:29:06Z")

</div>

Thanks Lambert

I had to double-check but yes there is an index on doc\_date.

> CREATE INDEX doc\_date  
> ON documents  
> USING btree  
> (doc\_date DESC NULLS LAST);

---

<div class="post-metadata">

**Author:** ![lwjwillemsen](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/lwjwillemsen/32/4987_2.png) [@lwjwillemsen](https://forum.servoy.com/u/lwjwillemsen)\
**Post date:** [February 14, 2014, 4:51pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807/4 "2014-02-14T16:51:03Z")

</div>

Hmm, strange…

Have you called this query almost 2000 times with the same parameters ?  
In other words : what happens if you put indexes on columns email\_account and doc\_type ?  
Or : this query would perform better if you could use doc\_date (date range) in the where clause since  
doc\_date has an index.

On large tables we always put an index on some kind of date column and tell our customers to use  
that column in end-user queries. Our initial form foundset sort is also set to that date column.  
The servoy load foundset always works with limit ? and we see great performance gain when filtering and sorting on indexed columns.

Regards,

---

<div class="post-metadata">

**Author:** ![grahamg](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@grahamg](https://forum.servoy.com/u/grahamg)\
**Post date:** [February 14, 2014, 5:23pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807/5 "2014-02-14T17:23:03Z")

</div>

Yes doc\_type and email\_account also have indexes.

Both Queries are triggered by clicking on tab\_labels and will have slightly different parameters each time. The fast - unlimited - query is when Users select their Email\_Account + Inbox/Pending/Drafts etc., that will almost always have less than Servoy’s 200 record initial load.

The slow - limit=100 - query is for the Emails Sent/Received selections that have many thousands of records - however most of the time they initially only want to see the last day or two. I (mistakenly) thought that by reducing the limit it would be faster.

On next update will remove the LIMIT=100 but was curious to learn what I might be doing wrong.

Appreciate your time on this.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [February 14, 2014, 5:45pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807/6 "2014-02-14T17:45:57Z")

</div>

Hi Graham,

You say the one without the limit gives you already a small resultset (less than 200) and the one with the limit would normally return more than that.  
If so then it is not necessarily strange that the query is slower. It will have to select all the records matching your WHERE clause, then SORT it all before it can apply the LIMIT.  
How large is your resultset (before the limit) ?  
Also doing an EXPLAIN ANALYZE in a query editor will give more insight in how the 2 queries behave (I assume you are using PostgreSQL here).

---

<div class="post-metadata">

**Author:** ![grahamg](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@grahamg](https://forum.servoy.com/u/grahamg)\
**Post date:** [February 14, 2014, 7:01pm UTC](https://forum.servoy.com/t/why-would-adding-limit-slow-down-a-sql-query/17807/7 "2014-02-14T19:01:58Z")

</div>

Hi Robert

The original Query (without limit) for Inbox/Pending/Drafts will generally just result in 0-100 records.

Selecting all Sent/Received Emails for a persons Email\_Account would result in many thousands of records - but generally they only want to check on Sent/Received in last day or so. That gave me the idea of limiting to the last 100 as there is a comprehensive Search tab for checking further.

Yes - I’m using Postgres and will take a look at the Query Analyzer - thanks for the tip.

Have a good weekend.

Graham