Printable View
In case of cursors where exactly it consumes time and how to avoid it
For example I have 3500 rows with 165 columns. I need to update all those rows . For this I have taken a SP and passed those all recs as XML parameter to that SP but it is taking around 20 mins time if i run in the query analyzer.
Suggest an Idea
Well all these things depends on your design. Why would you need XML file to update few rows? Having an XMl isn't the wisest of ideas as reading from file takes quite a bit of time.
If you do have the information in other tables, writing simple joins or reading from those tables would make more sense. If reading from the XML is a necessity, it is worth loading the XML data into a temporary table and then do the update fetching data from here. Instead of relying on a cursor - update and them again read from XML.
I might be able to suggest better if you give the exact scenario.
Cheers!
Kalayama
To avoid you can try using temp tables. Please let me know if i am wrong