I know that I am stepping into this conversation very late, but it seems that this is an ideal situation for DTS. You can set up a job that will get a list of ContractIDs, break the list into smaller sets, open multiple connections to the database, then have each connection process a contract.
Earlier in the thread, Kanwar said that DTS is not feasible. I would be quite interested to know why he feels that way.
-km
-----Original Message-----
From: Schlieper Paul [mailto:mssqldba-ezmlmshield-x97176758.[Email address protected]
Sent: Friday, October 29, 2004 10:16 AM
To: LazyDBA Discussion
Subject: RE: SUSPECT: RE: parallel execution of query
Actually, he wanted to run 2 queries simultaneously (asynchronously), originating from within the same stored procedure.
Unless row-by-row is the only way to go (and as Kirsty rightly points out, there's *nearly* always a more efficient way), this sounds like something that's begging for a set-based approach, especially when he says that the contract ids are retrieved from another table at run-time. My point, if you can select one from another table, you can select as many as apply.
P
-----Original Message-----
From: Mat.Elkington
[mailto:mssqldba-ezmlmshield-x24589033.[Email address protected]
Sent: Friday, October 29, 2004 11:11 AM
To: LazyDBA Discussion
Subject: SUSPECT: RE: parallel execution of query
Oh yeah don't get me wrong - I wouldn't advise the use of cursors under all circumstances by any means. I much prefer temporary tables and honest-to-god straightforward queries where possible.
Just struck me that in this instance that might fit the bill...
-----Original Message-----
From: Pollock Kirsty
[mailto:mssqldba-ezmlmshield-x56391755.[Email address protected]
Sent: 29 October 2004 16:03
To: LazyDBA Discussion
Subject: RE: parallel execution of query
As I understand it, Kanwar's problem is perfomance, so he wants to run individual updates/inserts in parallel - each one takes a long time.
This is why several of us are urging a look at basic strategy, as this is often where huge savings can be made.
I say this very carefully, but ... *most* cursor-based solutions are slower than the equivalent set-based solutions and in some cases by very large factors. It is very rare that a database programming task cannot be solved using set-based operations - at least such has been my experience, and I've had done my fair share of optimisation.
I'm NOT looking to get into an OT discussion on that topic, just mentioning it, since performance seems to be the major issue with the problem as stated.
> -----Original Message-----
> From: Mat.Elkington
> [mailto:mssqldba-ezmlmshield-x1006649.[Email address protected]
> Sent: 29 October 2004 15:08
> To: LazyDBA Discussion
> Subject: RE: parallel execution of query
>
>
> So again, why not use a cursor...?
>
> -----Original Message-----
> From: Pollock Kirsty
> [mailto:mssqldba-ezmlmshield-x76470629.[Email address protected]
> Sent: 29 October 2004 14:56
> To: LazyDBA Discussion
> Subject: RE: parallel execution of query
>
>
> I still don't see why that mandates doing one contract at a time as
> opposed to a set-based operation.
>
> > -----Original Message-----
> > From: Singh Kanwar
> > [mailto:mssqldba-ezmlmshield-x33555071.[Email address protected]
> > Sent: 29 October 2004 14:27
> > To: LazyDBA Discussion
> > Subject: RE: parallel execution of query
> >
> > i do not know all the contractIDs in the advance, it has to
> be picked
> > up from other table at the time of execution, because the
> contractids
> > keep changing. because of nature of work, DTS is not feasable.
> >
> > Thanks
> >
> > KP
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.783 / Virus Database: 529 - Release Date: 10/25/2004
MS Sql Server LazyDBA home page