Is it Possible to Execute Two "Select" Statements in SP Parallely

Question

Post reply

Is it Possible to Execute Two "Select" Statements in SP Parallely

santoooo

SSCommitted

Points: 1999
More actions
July 18, 2008 at 12:06 am

#191176

Dear all,

Please confirm me, is it possible to execute two sql statements in SP paralley.

why i seeking this is ....

In my Stored Procedure.....

i am having 4 typical queris, each queries takes nearly 1 min time to execute,

i am storing all the query results in temp tables,

and finally i am using all 4 temp tables to get the required resultset

now the problem is it is taking 5 mins to execute the stored procedure..

because i queries are executing serially...right

Is it Possible to Execute queries in SP Parallely,

and finally after inserting all results in temp tables,, so that it will take less time...

just like threading concepts...

OR

Please suggest is there any other ways of doing to achieve this.

Thanks,

Santoshhhh!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

j nuñez SSCrazy Points: 2115 More actions · Answer 1

j nuñez

SSCrazy

Points: 2115

July 18, 2008 at 12:21 am

#844804

Could you give us some information about:

- Table structures

- Indexes

- The queries you are doing

- Their execution plans

Perhaps, we can help you an reduce their execution time.

santoooo SSCommitted Points: 1999 More actions · Answer 2

Hi,

thanQ for quick reply

could u please suggest is there any other options... rather than tuning the query,

coz, it is a very big SP and the execution plan also very big, and i am not able to save the execution plan.

I am using SQL 2000..

I am querying on the audit tables there are up 5 lakh records,

joining audit tables... based on some criteria.... thats y it is taking more time...

is there any concepts like threading in SQL 2000

Thanks

Santsohhh!!

Jez-448386 SSCarpal Tunnel Points: 4570 More actions · Answer 3

There is no way to run the queries in parallel within a stored proc.

There are a couple of things you could consider:

1. Create a DTS package with each query in a separate task each with their own connection to the database. You can call the DTS package from the Proc

2. Create a separate job for each query and execute these from the proc.

In either case, you would need to store the results of the processing in a permanent table. For the separate jobs, you would have to devise a mechanism to wait for all the jobs to complete before continuing which you would not need to do with the DTS package.

In both cases you would need to think about passing parameters into the separate queries.

Alternatively, you might want to tune the queries.

Jez

santoooo SSCommitted Points: 1999 More actions · Answer 4

HI, Jez

ok, thank you for reply

Could u please suggest me,

what care we should take while writing queris using joins and sub-queries.. etc.,

in case we have huge data in the database table(s)..

Please tell me some tuning tips so that i can apply on my query..

If we use views instead of table, is always better for quering.. right (please correct me if i am wrong)

Jez-448386 SSCarpal Tunnel Points: 4570 More actions · Answer 5

Tuning is a massive subject and no one solution fits every situation. There is lots of discussion on this site about tuning so have a browse around the site for topic.

A few pointers:

1. Indexes - obvious really but needs saying. Do your queries use the indexes on the tables or are they doing table scan? You can check this generating an execution plan in Query Analyser.

2. Sub queries - depending on how you use these, they are a possible cause of real performance problems. Search for articles by Jeff Moden on RBAR (Row by Agonising Row) to see whether you are using sub queries like this. Another problem of sub queries is that there is no indexing so if it is used in a join then it may not be too effecient. Consider creating # temporary tables with the data from the sub query, create an index on the temp table and use it in the query rather than a sub query. It might sound like the proc has to do more work but if you are working with hundreds of thousands of rows and you can create a decent index (e.g. clustered) on the join criteria then it will more than make up for the extra work.

When I have a query that doesn't perform well, I build the query in Query Analsyer one join at a time and determine at which point it starts going bad. I can then look at the join criteria and see what I can do to improve it.

Jez