Skip to main content
A
Anonymous
February 9, 2011
Question

Showing 'potential' records returned

  • February 9, 2011
  • 2 replies
  • 809 views

Hi

I'm wondering what is the most efficient way to show the number of records available when a link is clicked on. eg.

Hotels (32)

Lodges (12)

Camping (1)

As you can see from this link

http://www.iknow-yorkshire.co.uk/north_yorkshire/pickering/all_prices/guest_houses/eco_friendly/

they actually disable links where no records are available. This seems quite complex - they dim/disable links for a variety of categories where a click would render no results.

My question is this: What is the most efficient way of achieving this - query of queries? stored procedures? neither?

Thanks

Shaggy

    This topic has been closed for replies.

    2 replies

    J
    JMF3
    Participating Frequently
    February 9, 2011

    Right, COUNT() on the other side of a LEFT OUTER JOIN will return 0, not null, which is really nice.  At least, that's the way it works in MS SQL Server.

    A
    Anonymous
    February 9, 2011

    Thanks

    There are so many variables though and permutations, I will have to run quite an extensive query for each link - won't the overhead be very high?

    Thinking about the link provided - would it be less overhead to store the number of records available in the database.

    So for example, everytime a page is visited it stores the url and the count of the number of records returned in a separate table. Then we retrieve this value through a quick query for each link? is this any better than the solutions provided in terms of efficiency? or is left join count still the way to go?

    J
    JMF3
    Participating Frequently
    February 9, 2011

    If each lookup is going to be separately complex, then you will probably

    have to run some performance metrics on various solutions to truly tell

    what's going to work best. If it's going to bog down using JOINs, then,

    yes, a tracking table would certainly help.

    Fernis
    Fernis
    Inspiring
    February 9, 2011

    I would create a separate query for the potential rows returned. Just be sure not to return the full dataset and get the totals in ColdFusion - that's highly ineffective - , but just use SQL syntax to return the totals straight away. Then use another query to actually query the data records you need.

    Query of queries will not certainly help you, since In No Case you want to return the full potential result set (i.e. as many rows) from the database. Always save the I/O bandwith - return as little data from the SQL server as you can. (I'm not pro at this, but just using common sense)

    As a thumb rule, any SQL processed or analyzed outside your SQL  server is always the worse option for performance. And yes, stored  procedures are surely the fastest way to go, but not something you probably will bother to do in the development phase.

    ColdFusion 9's page fragment caching are also useful in this - you can  specify which variables will create a new cache instance.... what I mean  is, you can store the potential result totals in the cache for  individual search words. I have the feeling people are using CF9's caching features all too little...

    -Fernis

    J
    JMF3
    Participating Frequently
    February 9, 2011

    I would recommend a left join query with the COUNT aggregate function, like

    so (where 'primary' is the table with the links and 'secondary' is the

    table with the related records):

    SELECT primary.categoryName,

    primary.categoryID,

    COUNT(related.ID) AS numberOfRelatedRecords

    FROM primary LEFT OUTER JOIN

    secondary ON primary.ID = secondary.primaryID

    GROUP BY primary.categoryName, primary.categoryID

    Then just output that in CF:

    #categoryName#

    (#numberOfRelatedRecords#)

    </cfoutput

    D
    Dan_Bracuk
    Inspiring
    February 9, 2011

    If you want to use this approach, I suggest using whatever function your database has to return a 0 instead of null for the number of records.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices