Please optimize this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Please optimize this query

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2006-05-19 : 08:17:48

I have a stored Procedure which takes too much time. I what to optimize it. please help.
___________________________________________________________________
CREATE PROCEDURE sp_Get_shops_for_view_shop
@intPageNO numeric,
@intPageSize numeric,
@search varchar(8000),
@search1 varchar(8000),
@user_type_id as numeric
AS
declare @scheduler as varchar(200)
if @user_type_id=3
set @scheduler = ' inner join tbl_scheduler_shop SS on SS.shop_id = TS.shop_id '
else
set @scheduler = ' '
create table #tmpTbl(id numeric identity,
shop_id numeric,
program_id numeric,
location_id numeric,
shopper_id numeric,
title varchar(100),
start_date datetime,
end_date datetime,
target_end_date datetime,
location varchar(100),
address1 varchar(100),
city varchar(100),
State varchar(50),
Zip_code varchar(20),
shopper_name varchar(100),
verified bit,
Scheduler varchar(100),
will_shop datetime,
report_date datetime,
Shop_Status_Desc varchar(25),
payment_status_desc varchar(25),
report_status_desc varchar(50),
Employee_id numeric,
is_Active bit
)
declare @exeStm as varchar(5000)
set @exeStm = 'insert into #tmpTbl(shop_id ,
program_id,
location_id ,
shopper_id ,
title ,
start_date ,
end_date ,
target_end_date ,
location ,
address1 ,
city ,
State ,
Zip_code ,
shopper_name ,
verified,
Scheduler ,
will_shop ,
report_date ,
payment_status_desc ,
report_status_desc,
Employee_id,
Shop_Status_Desc,
is_Active
)
select TS.shop_id,
TS.program_id,
TL.location_id,
TSer.shopper_id,
shop_title,
shop_start_date,
TP.Program_end_date,
shop_end_date,
TL.Shopping_mall location,
TL.street_Address_1,
TL.City,
TL.state,
TL.zip_code,
TSer.First_name+'' ''+TSer.Last_name shopper_name,
isnull(verified,0) verified,
TE.First_name+'' ''+TE.last_name Scheduler,
TSA.Schedule_date will_complete,
TSA.submit_Date report_date,
TPS.payment_status_desc,
TSRS.report_status_desc,
TE.msp_employee_id,
TSS.status_desc,
is_Active
from tbl_shops TS inner join Tbl_programs TP
on TS.program_id = TP.program_id
inner join tbl_client TC
on TC.client_id = TP.client_id
left join tbl_client_locations TL
on TS.location_id = TL.location_id
inner join tbl_payment_status TPS
on TPS.payment_status_id = TS.payment_status_id
inner join tbl_shop_status_types TSS
on TS.shop_status_id = TSS.status_id '+ @scheduler +'
left join tbl_Scheduler_shop TSShop
on TS.shop_id = TSShop.shop_id
left join tbl_msp_employee TE
on Tsshop.msp_employee_id = TE.msp_employee_id
left join tbl_shopper_assignment TSA
on TSA.shop_id = TS.shop_id
left join Tbl_shopper TSer
on TSA.shopper_id = TSer.shopper_id
LEFT JOIN tbl_survey_report TSR
on TS.shop_id = TSR.shop_id
left join tbl_survey_report_status TSRS
on TSR.report_status_id = TSRS.report_status_id
left join tbl_payment_Status PS
on ps.payment_status_id = TS.payment_status_id
left join tbl_shop_payment TSP
on TS.shop_id = tsp.shop_id and TSer.shopper_id = TSP.shopper_id where ' +@search
execute (@exeStm)
declare @Rcount numeric
set @Rcount= @@rowcount
if @Rcount =0 and @search1<>''
begin
set @exeStm = 'insert into #tmpTbl(shop_id ,
program_id,
location_id ,
shopper_id ,
title ,
start_date ,
end_date ,
target_end_date ,
location ,
address1 ,
city ,
State ,
Zip_code ,
shopper_name ,
verified,
Scheduler ,
will_shop ,
report_date ,
payment_status_desc ,
report_status_desc,
Employee_id,
Shop_Status_Desc,
is_Active
)
select TS.shop_id,
TS.program_id,
TL.location_id,
TSer.shopper_id,
shop_title,
shop_start_date,
TP.Program_end_date,
shop_end_date,
TL.Shopping_mall location,
TL.street_Address_1,
TL.City,
TL.state,
TL.zip_code,
TSer.First_name+'' ''+TSer.Last_name shopper_name,
isnull(verified,0) verified,
TE.First_name+'' ''+TE.last_name Scheduler,
TSA.Schedule_date will_complete,
TSA.submit_Date report_date,
TPS.payment_status_desc,
TSRS.report_status_desc,
TE.msp_employee_id,
TSS.status_desc,
is_Active
from tbl_shops TS inner join Tbl_programs TP
on TS.program_id = TP.program_id
inner join tbl_client TC
on TC.client_id = TP.client_id
left join tbl_client_locations TL
on TS.location_id = TL.location_id
inner join tbl_payment_status TPS
on TPS.payment_status_id = TS.payment_status_id
inner join tbl_shop_status_types TSS
on TS.shop_status_id = TSS.status_id '+ @scheduler +'
left join tbl_Scheduler_shop TSShop
on TS.shop_id = TSShop.shop_id
left join tbl_msp_employee TE
on Tsshop.msp_employee_id = TE.msp_employee_id
left join tbl_shopper_assignment TSA
on TSA.shop_id = TS.shop_id
left join Tbl_shopper TSer
on TSA.shopper_id = TSer.shopper_id
LEFT JOIN tbl_survey_report TSR
on TS.shop_id = TSR.shop_id
left join tbl_survey_report_status TSRS
on TSR.report_status_id = TSRS.report_status_id
left join tbl_payment_Status PS
on ps.payment_status_id = TS.payment_status_id
left join tbl_shop_payment TSP
on TS.shop_id = tsp.shop_id and TSer.shopper_id = TSP.shopper_id where ' +@search1
execute (@exeStm)
set @RCount = @@rowcount
end
select @Rcount TOTAL_RECORDS
if (@intPageNO=0)
begin
select shop_id ,
program_id,
location_id ,
shopper_id ,
title ,
start_date ,
end_date ,
target_end_date ,
location ,
address1 ,
city ,
State ,
Zip_code ,
shopper_name ,
verified,
Scheduler ,
will_shop ,
report_date ,
payment_status_desc ,
report_status_desc,
Employee_id,
is_Active
from #tmpTbl order by shop_id
end
else
begin
declare @firstRec integer
declare @lastRec integer
set @firstRec = (@intPageNO - 1) * @intPageSize +1
set @lastRec = (@firstRec + @intPageSize)-1

select shop_id ,
program_id,
location_id ,
shopper_id ,
title ,
start_date ,
end_date ,
target_end_date ,
location ,
address1 ,
city ,
State ,
Zip_code ,
shopper_name ,
verified,
Scheduler ,
will_shop ,
report_date ,
payment_status_desc ,
report_status_desc,
employee_id,
is_Active
from #tmpTbl where id >= @firstRec and id<=@lastRec order by shop_id
end
drop table #tmpTbl
GO
____________________________________________________________________
as one can see there are too much joins in the query! and also the search criteria. which is in string format and comes from my web applicaiton.

Muhammad Saifullah

Kristen
Test

22859 Posts

Posted - 2006-05-19 : 08:41:14

It would have been helpful if you'd posted is using [[]CODE] rather than [[]BLUE]!

#tmpTbl should have a Primary key

No point using numeric datatype for things that should be INT - I'm assuming the IDs can be INTs?

If you do need to use NUMERIC you should specific the Precision and Scale - do you really need 18 digits? If not you are wasting 4 bytes on each entry, and a lot more overhead than just using INTs

You are using dynamic SQL to optionally include an additional Inner Join, and to handle a generic WHERE clause. This should be coded into the actual SQL statement - otherwise the query will not be cached, and that will waste time when it is executed. (I expect you could use an OUTER JOIN and then disallow NULL PKs on it IF @user_type_id=3, and the WHERE clause can almost certainly be handled by parameterising it.

If the query has to be dynamic SQL it should be parameterized using sp_executesql so that there is a high probability that the query plan is cached.

But typically a generic parameterized query can be handled using:


WHERE     (@MyParam1 IS NULL OR MyColumn1 = @MyParam1)
      AND (@MyParam2 IS NULL OR MyColumn2 = @MyParam2)
      ....

set @Rcount= @@rowcount

should be the VERY NEXT statement after the statement it refers to. You have a DECLARE in between - which might be benign, but its not good practice.

You are also using a massive-overhead-approach to paging.

You are selecting ALL the output columns into a temporary table, and then deducing which rows are sensible for paging, and thus "throwing away" all the other columns you have just painfully extracted from the database. It would be far more efficient to only store the PKs of the rows in the Temp Table, and then join those to the necessary tables for the Output.

Using this approach you could also ONLY select the appropriate rows into the #TempTable in the first place (see Jeffs blog for a neat way to select a "page" of PKs from a larger target recordset by ONLY selecting enough rows to make up the specific page).

"I what to optimize it"

Well the good news is it looks to me as if there is plenty of scope for that!

Kristen

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-19 : 08:53:56

you can use sp_executesql rather than sql. remember, the temporary tables uses the temporary database and could also be the cause of the sluggish performance.

Another suggestion is to you paste the join code that creates the temporary table in index tuning wizard and see if there is any suggestion it will give (index/stats)? use the profiler to capture the statement and use that load to index tuning wizard and also, check the execution plan.

In general, you have to figure out if this query is the only one having problems or you might have other queries that starts to slow down. If you do, it might be something about indexes, statistics, file I/O, memory, etc.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources