| Author |
Topic |
|
AAAV
Posting Yak Master
152 Posts |
 Posted - 2011-12-14 : 10:41:32
|
| A) I need to process 3 million rows. will i be able to run 3 threads of 1 million each in sql server 2008 at the same time?B) I need to run two cross apply queries which cacluates different values. will i be able to run them simultaniously?I am trying to reduce the execution time. Let me know what you all use to acheive that. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-14 : 11:16:32
|
| A, for increasing parallelism in execution, you can specify the level of parallelism by means of MAXDOP hintB, its possible to run a query with multiple cross applys together simultaneously------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2011-12-14 : 11:31:54
|
| B... interested in knowing thissuppose i have 2 queriesSelect * from tableA cross apply fnAselect * from tableA cross apply fnBLet me know how?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-14 : 11:34:43
|
| depending in your requirement it can be either of two1.selectfrom (Select * from tableA cross apply fnA)ainner join(select * from tableA cross apply fnB)bon b.relatedcol = a.relatedcol2.Select * from tableA across apply fnA facross apply fnB fb------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2011-12-14 : 12:43:17
|
| but will it run each of the cross apply in its own thread...i am trying to cut down the execution time |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-14 : 12:47:22
|
quote:
Originally posted by AAAV
but will it run each of the cross apply in its own thread...i am trying to cut down the execution time
you cant guarantee thatdidnt understand need of such a requirement thoughcurrently how long query is executing?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2011-12-14 : 12:57:51
|
| I have 3 million rowsThis is not the actual case but for examplei have 3 million productsfor each i run 7 cross apply functions (calculating different cost- each of which is in a function). I am thinkign if possible i can run costA and CostB together. Each cross apply outputs the result to a temp table. that way i can run CostA and CostB together CostC and CostD together and CostE and CostF together . CostG takes all the costA thro CostF into account hence it has to be done alone. My objective is...1) if i am able to run 2 costs at the same time fine... it might reduce the time2) if i am able to split the input rows in to 3 batches and 2 batches run at the same time then also the time must be reducedcorrect me if iam wrong...now it runs for roughly 5 hours. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-14 : 13:04:01
|
| why do you need 7 cross applys? why cant you merge the cost calculations together?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2011-12-14 : 13:24:09
|
| That is a good idea... but merging them will not reduce the time right? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-14 : 13:25:29
|
| it will as all logic will now be inside single cross apply------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2011-12-14 : 13:38:53
|
| will try now.... |
|
|
|
|
Multi threading