Querying data to Home Page

  • Hi. I want to redesign our Web site's home page to pull data (i.e., last 5 headlines entered) from a table. I want to make sure I properly optimize the speed of this. I've done regular dynamic queries before, but I'm afraid going this route might slow down the experience for the users if we get hundreds of people querying the headlines table everytime they visit our home page. Is there a way to maybe save this operation as a function or stored procedure and call it when someone requests our home page? I'm admittingly not as knowledgable in SQL as others in this forum, so any help is appreciated. (I would imagine even the home page of this site uses a tactic similar to what I want to do -- dynamically populating a page that can handle hundreds of requests and still serve-up the page quickly.) Thanks.

    David

  • Yes, you can do it in a stored procedure and while the result will be dynamic, the implementation will not. You'll end up with something like this:

    create proc usp_GetHeadlines as

    set nocount on

    select top 5 headlinename, headlinedescription from headlines order by dateadded desc

    When we say dynamic sql here on the site we mean that we're building up the query string on the fly, either clientside or serverside - in either case performance is not as good as a static stored proc.

    Here on the site we do use procs extensively, but even though all the data is kept in the db a large number of our pages are static - we have a tool that rebuilds any page affected by a changed row (mostly adds and a few edits). Possibly not the "best" solution, but one we picked and it works pretty well.

    Ask questions anytime!

    Andy

  • The way that MS.com does it, or used to, was to write out XML files (flat) onto each web server. These are then transformed by the ASP into what you see. This is extremely fast as the XML will get cached by the webserver. Now, how do you make this dynamic? You make changes in the db. The changes are marked and a new XML flat file is written and distributed to the web servers.

    Here we write out a flat file for each article becuase we don't want to query the db for it every time. Most page do hit the db to record information, like page read, etc, but lots of info is somewhat static. This works well and probably has scaled the site more than if everything was a db call.

    Steve Jones

    steve@dkranch.net

  • I agree with Steve, I have a site another developer did that would query the list of the current months birthdays eveytime some came to the page and I really did not like this as I saw it as way too much overhead. Although I will take the suggestion a step further and suggest you build a job that outputs the XML file and disable the job and when an insert occurrs on the table I fire the follwoing to enable the job.

    EXEC msdb.dbo.sp_update_job @job_name = 'YourJobNameHere', @enabled = 1

    Now when I set up the job it is scheduled to run every 1 minute, so it will run within 1 minute of the trigger enabling. Then it performs it's first task and moves along to a final task of

    EXEC msdb.dbo.sp_update_job @job_name = 'YourJobNameHere', @enabled = 0

    which disables it until the trigger fires again re-enabling it.

    This way the job only runs when there is something to do. And you can take full advantage of changes to the table and the XML output serving your customers while overall keeping the access to your SQL server lower.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply