| Author |
Topic |
|
isfaar
Starting Member
11 Posts |
 Posted - 2008-02-08 : 07:58:55
|
| Hi,I have a task at hand to reduce the time taken for search query to execute. The query fetches records which will have to sorted by degrees away from the logged in user. I have a function which calculates the degrees, but using this in the search query slows the execution and takes about 10 secs to complete which is unacceptable.Please advice. Your help is much appreciatedThanksIsfaar |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-02-08 : 08:06:17
|
| One thing you could look at changing is: take the code in the UDF and place it in the main body of the query.Does this change your execution time?Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-02-08 : 10:14:15
|
| I think you should use recursion for this, not a function.In 2000, you can use an temporary table to accumulate the results of your recursive search. In 2005, you can implement recursion using Common Table Expressions.e4 d5 xd5 Nf6 |
|
|
|
isfaar
Starting Member
11 Posts |
Posted - 2008-02-08 : 13:21:30
|
| Can you please give an example of how to implement this.ThanksIsfaar |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-08 : 13:26:31
|
| refer this for recursion in sql 2000:-http://www.mssqltips.com/tip.asp?tip=938 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
isfaar
Starting Member
11 Posts |
Posted - 2008-02-09 : 16:31:22
|
| Hi Peso,I am using that very function mentioned in the articles listed above. I have used that function in the query to fetch users based on search criteria. Now this function gets executed for each record and slows down the response time. Do think of a way to make it more faster or maybe of a solution without the function. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-12 : 03:21:33
|
Yes. Why don't you give us the present code for review?Or do you want us to guess?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-13 : 02:04:47
|
I strongly believe it is not the function code that is wrong. It is the implementation, to calculate degrees away within a SELECT statement for possible all combinations.Why don't ou use a trigger and store all combinations in a separate table?And then select from that table when called for?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Avoid UDF For Degrees Away in Search Query