Click here to Skip to main content
15,903,203 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
Hi all,

I am working on website . For administrator, i need to generate report which contains data from 9-10 tables.

I have created stored procedure but it is working very slow, it takes 5 mins to get data in sql server 2005 so in my website, i get timeout exception.
As i said, i fetch data from 9-10 tables so i used inner join for that. is inner join slows down stored procedure?

For this, i tried to work with view. I craeated 5 to 6 views as per my requirement and used those views in one stored procedure. but still its very slow.


Please help me with above scenario..

Below is my stored procedure

SQL
CREATE Procedure getAdminReport   -- '91,93,826,823,83,86,712,85,82,88',12,12,2012,2012                              
(                                                              
@eid varchar(500),                                      
@from_month int,                                                 
@to_month int,                                                        
@from_year int,                                                          
@to_year int,      
@desg int= null                                                            
)                                                              
as                                                                                                     
                                        
CREATE TABLE #2                                         
(                                         
LIDList Int                                         
)                                         
                                        
-- Optional index on the temp table                                         
CREATE INDEX idx1 ON #2 (LIDList)                                         
INSERT INTO #2                                         
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,                                         
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList                                         
FROM tblToolsStringParserCounter                                         
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +                                         
@eid + ',' , ID - 1, 1) = ','                                         
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0                                         
                                        
                                        
select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,                      
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,                                                                  
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,                                                                     
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,                                                                  
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,          
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,                                                                         
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,                                                
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,          
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full                                              
from doc_business_data DB                                                                  
inner join educator_call_dtls EC on DB.call_id=EC.call_id                                                                  
inner join doctor_master DM on DM.doc_id=DB.doc_id                                                           
inner join educator_master ED on ED.eid=EC.eid                                                           
inner join educator_state_master city on city.state_city_id=ED.city_id                      
inner join educator_state_master region on region.state_city_id=city.parent_id                      
inner join educator_state_master st on st.state_city_id = region.parent_id                                                                      
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and                                    
(((year(EC.call_date) > @from_year) OR
Posted
Updated 1-Feb-13 2:15am
v3
Comments
Rob Branaghan 1-Feb-13 4:09am    
Dont use views. Views are slow....very slow....

You may perhaps want to try the Actual Execution Plan, so you can see what part of the query is taking the longest, and then you may have to add another index to the table, or switch the main table to a different table in the query.
Ankur\m/ 1-Feb-13 8:06am    
That is the way to go. i) Evaluate the execution plan. ii) Add necessary indexes.
That could have been an answer as well with some more details.
Rob Branaghan 1-Feb-13 4:11am    
Would it be possible to see the SQL, as it would be a lot easier to assist being able to see the SQL.
Meysam Toluie 1-Feb-13 4:11am    
Of course, Inner join slows down store procedures.
For suggesting you a solution I need to see your code.
Sweetynewb 1-Feb-13 4:53am    
but i need to use inner join to get data

1 solution

Dont use views. Views are slow....very slow....

You may perhaps want to try the Actual Execution Plan, so you can see what part of the query is taking the longest, and then you may have to add another index to the table, or switch the main table to a different table in the query.

With regards to your SQL I think one of the ways you could speed it up is placing your WHERE in the Inner Join.

SQL
CREATE Procedure getAdminReport   -- '91,93,826,823,83,86,712,85,82,88',12,12,2012,2012                              
(                                                              
@eid varchar(500),                                      
@from_month int,                                                 
@to_month int,                                                        
@from_year int,                                                          
@to_year int,      
@desg int= null                                                            
)                                                              
as                                                                                                     
                                        
CREATE TABLE #2                                         
(                                         
LIDList Int                                         
)                                         
                                        
-- Optional index on the temp table                                         
CREATE INDEX idx1 ON #2 (LIDList)                                         
INSERT INTO #2                                         
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,                                         
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList                                         
FROM tblToolsStringParserCounter                                         
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +                                         
@eid + ',' , ID - 1, 1) = ','                                         
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0                                         
                                        
                                        
select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,                      
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,                                                                  
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,                                                                     
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,                                                                  
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,          
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,                                                                         
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,                                                
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,          
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full                                              
from doc_business_data DB                                                                  
inner join educator_call_dtls EC on DB.call_id=EC.call_id                                                                  
inner join doctor_master DM on DM.doc_id=DB.doc_id                                                           
inner join educator_master ED on ED.eid=EC.eid  
-- ##########################################################################################
-- CHANGES BELOW
AND ED.designation <> 'BM' 
AND ED.designation <> 'SM' 
AND ED.designation <> 'SuperAdmin'                                                          
-- ##########################################################################################
inner join educator_state_master city on city.state_city_id=ED.city_id                      
inner join educator_state_master region on region.state_city_id=city.parent_id                      
inner join educator_state_master st on st.state_city_id = region.parent_id                                                                      
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and                                    
(((year(EC.call_date) > @from_year) OR


What that should do is narrow down the number of records needed in the join. If you can do that with more of the joins this will help a lot!

If you imagine you have 1,000,000 rows, but only 350,000 of them are the ones you are interested in, only joining on the 350,000 will be faster!

Good Luck!
 
Share this answer
 
Comments
Sweetynewb 4-Feb-13 0:37am    
Thank you for suggestion. i am trying that. Did you saw my whole query?
Rob Branaghan 4-Feb-13 4:01am    
There appears to be some missing... There is an OR, but nothing more.
Sweetynewb 4-Feb-13 6:44am    
hmm. because of limitation may be.. ok i am posting whole query below (Please look into it)-

CREATE Procedure getBFEAdminReport_MAIN_HQ -- '74',12,12,2012,2012
(
@eid varchar(500),
@from_month int,
@to_month int,
@from_year int,
@to_year int,
@desg int= null
)
as

CREATE TABLE #2
(
LIDList Int
)

-- Optional index on the temp table
CREATE INDEX idx1 ON #2 (LIDList)
INSERT INTO #2
SELECT Convert(Int, NullIf(SubString(',' + @eid + ',' , ID ,
CharIndex(',' , ',' + @eid + ',' , ID) - ID) , '')) AS LIDList
FROM tblToolsStringParserCounter
WHERE ID <= Len(',' + @eid + ',') AND SubString(',' +
@eid + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + @eid + ',' , ID) - ID > 0


select EC.eid,(ED.first_name+' '+ED.last_name) as FullName,st.state_name,region.state_name as region,city.state_name as city,ED.designation,ED.employee_id,
sum(Case when DB.cd_type=0 Then 1 Else 0 End) as cnt_clinic,
sum(Case when DB.cd_type=1 Then 1 Else 0 End) as cnt_camp,
sum(Case when DB.activity_type=0 Then 1 Else 0 End) as cnt_spir,
sum(Case when DB.activity_type=1 Then 1 Else 0 End) as cnt_breat,
sum(Case when DB.activity_type=2 Then 1 Else 0 End) as cnt_vitalo,
sum(Case when DB.timing=0 Then 1 Else 0 End) as cnt_morning,
sum(Case when DB.timing=1 Then 1 Else 0 End) as cnt_eve,
sum(Case when DB.timing=2 Then 1 Else 0 End) as cnt_full
from doc_business_data DB
inner join educator_call_dtls EC on DB.call_id=EC.call_id
inner join doctor_master DM on DM.doc_id=DB.doc_id
inner join educator_master ED on ED.eid=EC.eid
inner join educator_state_master city on city.state_city_id=ED.city_id
inner join educator_state_master region on region.state_city_id=city.parent_id
inner join educator_state_master st on st.state_city_id = region.parent_id
where EC.eid in (select LIDList FROM #2) AND ED.designation <> 'BM' AND ED.designation <> 'SM' AND ED.designation <> 'SuperAdmin' and
(((year(EC.call_date) > @from_year) OR (year(EC.call_date)=@from_year AND month(EC.call_date) >=@from_mo
Rob Branaghan 5-Feb-13 4:27am    
That last bit in the where clause is a little confusing, why aren't you just using Year(EC.call_date) >= @from_year AND MONTH(EC.call_date) >= @from_month?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900