October 30, 2012 at 4:57 am
Hi,
I am working on a software in which there a SP which is called very frequently, all most all users use this SP again and again.
The SP is about nothing but search data in a table with specified crietarea.
Following is basic working method of SP :
Eg. - Sp_getdata 'EmpName','abc','x' -- displays result of EmpName having 'abc'.
1.There is one main SP having 3 parameters which are i. Search Field Name ii. Field value iii. User
2.There are many sub-SPs which are called in this main SP according to which Field name is passed in main SP.
3.In each sub-SP there is a single Query which fatches data from table and inserts in different database table
4.After inserting data, Main SP deletes some unnecessary rows and throws result to user.
Here user can fetch data using two or more crietarea too.
Eg. - Sp_getdata 'EmpName,phone','abc,999999','x' -- displays result of EmpName having 'abc' and phone having '999999'.
Because of user can pass 2 or more crietarea i have to used this main sp and sub sp concept, and i have to use CURSOR in main SP.
what i do is :
1.i spit parameters with comma (,) and put it in database table
2.then using CURSOR of this parameter table i get data using sub-SPs one by one and result insert in different table.
3.at last delete some unnecessary rows and throw output to user.
this all things are within BEGIN TRANSACTION and COMMIT TRANSACTION block.
------------------------------------------------------------------------------
Problem is :
I think using this TRANSACTION block makes a queue and due to this I get
poor performance some times.
software stuck.
Now the question is :
1.Can i use temp tables instead on Database tables for better performance ?
2.does temp tables are created unique for each sp call. or will it make a queue on server ?
3.If i use temp table do i have to drop it each time or it will dropped automatically ?
October 30, 2012 at 8:06 am
From what you described I would imagine you have horrible performance. What does your splitter code look like? Cursors are notoriously horrendous performance hogs and I doubt you actually need one here. With no details about anything it is impossible to offer much in the way of help. Take a look at the first link in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2012 at 3:48 pm
I can't give specifics since you gave none. However, if I'm reading your post correctly, the problem you have is running multiple stored procedures, saving the results somewhere, and then using another procedure to process the results of the other procedures.
If that's the case, you can create a SINGLE procedure to do everything within the BEGIN TRANS/COMMIT TRANS block.
For each of your external SPs, insert the results into temp tables. It's best if you define the temp table first with the proper output columns and define a primary key.
INSERT INTO #TempTable1
EXEC myFirstSProc 123,'data string' -- i.e., whatever the parameters are
Do this for all the procedures pulling in data. Then you will have all the tables populated as necessary to do whatever it is you want to do to get your final result. This will probably do away with the need to do any string splitting or manipulation since you can just query and join these tables like any "real" table.
Then assign variables to the output of the master query and use these variables as the inputs to your final group of SPs that do the finishing work. The way your existing structure seems to be built, you may indeed need to use a cursor to loop through the master query's rows and call each sub-procedure in turn.
But it may be more efficient (if you have access to the other procedures) to pull the code out of those and incorporate it directly so that you can set up a MERGE statement to do any inserts/updates/deletes rather than having to do it row-by-row via external procedures.
Sorry if I haven't covered your requirements perfectly. All I could do is try to give you a generic outline of the process. If you provide some test data and a test schema then you may be able to get a more detailed response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply