As someone else has mentioned, SQL Server cursors are not super efficient.
Often, there is a non cursor alternative to what you are trying to do. For
example, if you have a identity column (or a column you know to be unique)
in the table you want to iterate over, you can use this trick to avoid a
cursor.
declare @CurId int
select @CurId = min(Table.IdentityCol) from Tables where (your where
criteria)
while (@CurId is not null)
begin
- do your stuff here, use the value of @CurId to grab the row you want to
work with
...
- then get the next id value
select @CurId = min(Table.IdentityCol) from Tables where (your where
criteria) AND Table.IdentityCol > @CurId
end
If you simply can't avoid a cursor, you could look at the links below. They
both have some good basic cursor performance suggestions.
http://www.sql-server-performance.com/cursors.asp
http://www.swynk.com/friends/achigrik/UseCursor.asp#part_2
HTH,
</rob>
-----Original Message-----
From: Vishal Narayan [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 26, 2002 1:08 AM
To: CF-Talk
Subject: Need to optimise query performance
We are running our website on CF4.5 with Win 2K, IIS5 and SQL 2000 DB on
another WIN2K machine.
I have a few highly database intensive pages that are placing a Iot of load
on my database server - executing them pushes up CPU utilisation to 100%
for a few seconds !
The resultsets are usually large, in thousands of rows. I have implemented
paging, i.e. showing only 20 records to a page, with previous and next
buttons at the bottom. I have created stored procedures to perform these
queries and retrieve the resultsets.
SELECT @CURSORSTR = 'DECLARE GETRES CURSOR
FOR
SELECT cnd_ID FROM candidatemaster r inner join category c on
(r.cat_id1=c.cat_id or r.cat_id2=c.cat_id or r.cat_id3=c.cat_id)
WHERE ((r.blocksearch=0) and (r.delete_flag=0) and (c.delete_flag=0))
AND (c.cat_id= ' @value')
AND (r.ctry_id = ' @countryid') )
Order by CND_UPDATE_DATE DESC
EXEC (@CURSORSTR)
OPEN GETRES
BEGIN
-- HERE I LOOP THROUGH THE CURSOR AND GET ONLY THE 20 CND_IDs TO BE SHOWN
CURRENTLY,
DEPENDING ON THE INPUT PARAM FOR PAGE NUMBER, AND ADD THEM TO A STRING
CALLED @cndlist --
END
CLOSE GETRES
DEALLOCATE GETRES
SELECT @SQLSTRING = 'Select *,ctry_name,ct1.cat_name as cat1, ct2.cat_name
as cat2, ct3.cat_name as cat3
from candidatemaster
inner join countrylist on candidatemaster.ctry_id=countrylist.ctry_id
inner join category ct1 on cat_id1=ct1.cat_id
left join category ct2 on cat_id2=ct2.cat_id
left join category ct3 on cat_id3=ct3.cat_id
where cnd_id in ('
EXEC (@SQLSTRING + @cndlist + ') Order by CND_UPDATE_DATE DESC')
In the template that calls the stored proc, we get the resultset as a
cfquery object called getres.
<CFSTOREDPROC PROCEDURE="sp_CatResSearch" DATASOURCE="#dns#"
username="#ua#" password="#pwd#">
--CFPROCPARAMS DECLARED HERE ---
<CFPROCRESULT NAME="getRes" RESULTSET="1">
</CFSTOREDPROC>
<cfoutput query="GetRes">
-- DISPLAY 20 RESULTS HERE --
</cfoutput>
One of the CFPROCPARAMS is the page number, which tells the stored
procedure which 20 records to retrieve.
I need to improve the performance of these pages and there are three ways I
can approach this problem :
1. Code level changes : is there anything wrong with the queries
themselves ? Can I do this more efficiently ? Note that in the stored proc,
I could have merged both the queries - query 1 can go into the where clause
of query 2. But I broke up the two so that I could get only the 20 cnd_Id's
that I require from the cursor created by query 1. The plus point is that
the second query needs to get only 20 records, as opposed to several
thousand. The drawback is that I have to loop through the cursor in query 1
to get the 20 cnd_Id's.
2. Design level changes : Should I simply put an upper limit on the number
of matches to be retrieved ? Query 1 retrieves several thousand cnd_id's
typically, and this number will only grow with time. Should I limit this ?
3. Performance tuning at DB level and database optimisation:
I've already tried the following :
- primary keys for all tables in use
- indexes on all columns used for joins
Is there anything else I can do towards performance tuning ? Could I use
views to reduce the load on my SQL 2000 DB ?
Would appreciate suggestions on any of the three approaches. Which one
should help most ?
Vishal.
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
