| Author |
Topic |
|
cognos79
Posting Yak Master
241 Posts |
 Posted - 2008-04-08 : 14:59:50
|
| I wrote a query and I use cursor in the query. When I run the query on dev box it takes 3 mins. I moved the query to EPM database box and it takes forever to run. Usually EPM database query performance is much better. How do I start debugging the poor performance? How can I check if the query is creating any table locks?Purpose of query: I get all the Companies (20000) and loop thru each company in the cursor and do calculations.Thanks... |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-08 : 15:13:44
|
| Post your query. Maybe we can help tune it. See the execution plan and see CPU I/O cost,indexes and etc |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-08 : 15:29:36
|
| >> Purpose of query: I get all the Companies (20000) and loop thru each company in the cursor and do calculations.yuk.I can see this coming down to a few seconds.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2008-04-08 : 15:34:10
|
| what I dont understand is: on dev box with 15000 companies it takes 3 mins and whereas on EPM database with same number of companies it takes forever....So does it have to do with server settings? rather than the cursor? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-08 : 15:35:55
|
| Coz nothing is running on Dev Box except your testing |
|
|
|
QAZAFI
Yak Posting Veteran
50 Posts |
Posted - 2008-04-08 : 19:38:51
|
| Are you using Temporary tables in that calculation, if this is the case then try to avoid using temp table, alternatively you can use table type variable, If still you have to use temporary table put cluster index on that.If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server. Alternatively you can use WHILE LOOPS. If you are doing some updates then you have to consider some other factors like transaction etchope that will help |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2008-04-09 : 10:22:19
|
| 1. I am already using table variables.2. I am using cursor with READ-ONLY option. I will change it to FAST-FORWARD3. I didnt use while loop anytime. I will try it now. I will keep posted my results after I make the above changes.Do while loop have better performance than using cursors??? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-09 : 10:25:09
|
1) This is only advisable if no more than 1-2 pages are stored in table variable. If more data is stored, the table variable flushes to tempdb and you get really bad performance.2) As suggested before, please post code you use, and maybe we can come up with a set-based solution3) Not much difference.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-09 : 10:25:33
|
| "Do while loop have better performance than using cursors???[/quote]"Yes |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2008-04-09 : 10:40:51
|
| I dont think I can post my code here as per the work rules...Peso...How do I know if the table variables stores data more than 2 pages? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-09 : 10:50:06
|
Sum the storage space for each record (sum of each column and size).Divide 8000 with the previous number. Now you get the number of records you can store in each page.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2008-04-09 : 10:54:38
|
| I have a table variable like this:declare @Students table ( StudentId varchar(15) ,StudentName varchar(50) )"Sum the storage space for each record (sum of each column and size)."what is sum of each column here? can you explain more in detail as per my table declaration? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-09 : 12:23:30
|
Each record takes 65 bytes of storage.8000 bytes (1 page) * 0.8 (default fill factor) / 65 = 98 records per page.This means that if you store 200 records or more, the probability is higher that a temp table is a better choice than a table variable.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2008-04-09 : 15:05:25
|
| Thanks a lot guys...I got the performance issue fixed once I added a new index on a table. |
|
|
|
|
query performance