Francisco Tapia
fhtapia at gmail.com
Thu Sep 23 12:08:46 CDT 2010
John, the problem with stating that cursors are "ok" to use as a general rule is that people DO start using them and in a heavy way (kind of like bound forms in Access ;-)) in all seriousness, Cursors Will deplete system resources and are generally not very scalable. So what initially started as a small dataset turns into a huge database from hell... additionally, in my personal experience we've removed cursors from nearly all our processes except for certain operations that demand them, the reason was that under certain MS conditions our server would crash for no apparent reason... when we changed a few jobs to run with set based operations and temp tables, we found that these processes not only ran faster, but our uptime increased with our systems making them more reliable. also note that I never said never... of course there are situations that demand a cursor, but those in my opinion are few and far between, this imho does not warrant the use of a cursor at least not yet. -Francisco http://bit.ly/sqlthis | Tsql and More... On Thu, Sep 23, 2010 at 8:29 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > >you should use cursors if there is no other viable way to solve your > problem. > > And why is that? > > My take is "you should NOT use cursors if it is going to severely impact > the server". > > I am all about learning new things Francisco, but it doesn't make sense to > me to do it the "hard > way" (which you solution is for SQL language babies like myself) when there > is a solution that I can > understand, program and debug easily. > > Cursors are just a tool, one which we understand. > > John W. Colby > www.ColbyConsulting.com > > On 9/23/2010 11:15 AM, Francisco Tapia wrote: > > i disagree... > > you should use cursors if there is no other viable way to solve your > > problem. In your scenario, you can certainly build up a subquery to join > to > > based on date / time stamp that would yield the previous job's location's > > postcode. you will need to somehow know which job was 1st 2nd etc. in > order > > for your join to work in either scenario (subquery or cursor). > > > > > > > > > > -Francisco > > http://bit.ly/sqlthis | Tsql and More... > > > > > > > > > > On Thu, Sep 23, 2010 at 8:07 AM, jwcolby<jwcolby at colbyconsulting.com > >wrote: > > > >> Paul, > >> > >> As long as this isn't doing tens of thousands of these at a shot I think > >> this is the perfect > >> application of a cursor. > >> > >> Cursors are slow but who cares if it is just a dozen at a time? > >> > >> John W. Colby > >> www.ColbyConsulting.com > >> > >> On 9/23/2010 10:53 AM, Paul Hartland wrote: > >>> To all, > >>> > >>> We have employees that can do more than one job in a day on our > database, > >>> when an employee is assigned to a job, the application takes the > persons > >>> home postcode and the jobs location postcode and stores them in a > table, > >> we > >>> are now creating an estimated mileage. I can do this based on the > >> postcodes > >>> supplied but it will be inaccurate as the start postcodes will always > be > >> the > >>> employees home, when the start postcode for the second job should > really > >> be > >>> the postcode of the previous job location. > >>> > >>> At the moment I have a select query which returns results for an > employee > >>> where they are doing more than one job in a day, result example below: > >>> > >>> PersonID Date StartCode EndCode > >>> 111111 29/09/2010 AA1 1AA BB1 1BB > >>> 111111 29/09/2010 AA2 2AA BB2 2BB > >>> 111111 29/09/2010 AA3 3AA BB3 3BB > >>> 111111 29/09/2010 AA4 4AA BB4 4BB > >>> > >>> I am thinking of putting these results into a cursor and looping > through > >> the > >>> records updating the startcode with the previous records endcode, so > that > >>> AA2 2AA will become BB1 1BB, AA3 3AA will become BB2 2BB etc > >>> > >>> Is this the best way to do this, or could someone point me in a better > >>> direction please. > >>> > >>> Many thanks in advance for all your help. > >>> > >> _______________________________________________ > >> dba-SQLServer mailing list > >> dba-SQLServer at databaseadvisors.com > >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > >> http://www.databaseadvisors.com > >> > >> > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >