limiting the number of records returned

[SilentBob'secretfusion]

Hi all,

The problem I am having is that I built an online query system. The problem
is if the search critera is to open or loose, the coldfusion server hogs memory
and eventually jams. Too many records returned is my guess. My question is...
"How can I limit the number of records returned?"

Thanks.

[rmorgan]

Use the 'limit' in sql.

[MarkWright]

I would love to see your queries so I could be more sure of the reason, but in
any case, I thought I'd offer a suggestion. SELECT DISTINCTROW may be how you
want to start your query to limit results as well as rmorgan's suggestion.

[SilentBob'secretfusion]

Hmmm. I don't know the "limit". I will research.

Distinct will not do the duty. I have 50,0000 unique records in the database.

I was considering using top 100 in the sql statement.

Any other ideas would be appreciated. Can I time a query?

[Adam Cameron]

> Hmmm. I don't know the "limit". I will research.

LIMIT is MySQL syntax.

> I was considering using top 100 in the sql statement.

Yep, if it's MS-SQL, that'll do the trick. Seems every DB engine has a
different way of doing this sort of thing (Oracle is different again).

One thing to note: don't use the MAXROWS attribute in the <cfquery> tag as
a generic solution to this issue. Whilst it'll seem to work
across-the-board on all DB engines, it doesn't prevent the DB from
processing the entire recordset, it simply tells CF to say "yeah, OK,
that's enough" when it's receiving the recordset back from the DB server.
SO it lightens the work for CF, but still hits the DB with unnecessary
procssing.

It'd probably solve your CF-memory issue, but it's not the best way of
doing it. Best to limit the processing at the DB end of things.

--

Adam