March 12, 2008 at 5:18 am
Hi,
I have created 1 stored procedure for down loading the products into excel file from the data base.In this stored procedure,fields are selecting from 4 data base tables.This stored procedure is a simple select statement, but selecting 72
fields from 4 database tables.The problem is that , this stored procedure is taking too much time for executing.
My data base having more than 2,00,000 products.Now this stored procedure is trying to down load maximum 5000 products at a time.In local application it is taking maximum 5 seconds. But in server, this is taking more than 12 minutes.
In future, my client want to down load lacks of products at a time. So please give me an advice to solve this problem
March 12, 2008 at 9:01 am
in general terms your question is too vague and we could make so many suggestions, most would probably be wrong.
I'd take a first guess at parallelism causing slowdown - but who knows!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 13, 2008 at 1:53 am
colin Leversuch-Roberts ,Thank you very much for your reply
Here I will paste my Stored procedure.This is little bit bulk.Can you check this and give me a valuable suggestion.And also
I have only 6 month of experience in sql, so please give me a good advice to improve its execution speed.
ALTER procedure [dbo].[CMRC_SP_ProductDetails](@merchantID INT)
AS
SELECT cmrc_products.modelnumber as sku,cmrc_Productrelationship.upc,cmrc_manufacturer.mname as brand,
cmrc_Productrelationship.model,cmrc_Productrelationship.type as master_prod_type,cmrc_Productrelationship.color,
cmrc_Productrelationship.size as prod_size,cmrc_Productrelationship.title as auto_title_preview,'' as manual_title,
cmrc_categories.CategoryName as category,
cmrc_products.unitcost as selling_price,cmrc_products.listprice as msrp,cmrc_products.thumbnail as thumb_image,
cmrc_products.productImage as hero_image,cmrc_products.longdesc as features_open,'' as features_bull_1,
'' as features_bull_2,'' as features_bull_3,'' as features_bull_4,'' as features_bull_5,'' as description,
'' as description_summary,cmrc_Productrelationship.moreInfo as more_info,
cmrc_products.keywords as metakeywords,cmrc_Productrelationship.meta as metadescription,
'' as lead_time_to_ship,'' as expedited_lead_time,
'' as shipping_origin,'' as warranty,'' as return_policy,
(case when cmrc_products.taxable=0 or cmrc_products.taxable is null then 'N' else 'Y' end) as taxable,
cmrc_tax.statecode as taxable_states,
cmrc_products.description as prod_length_inches,'' as prod_width_inches,'' as prod_height_inches,'' as prod_depth_inches,
'' as custom_dimensions,cmrc_products.weight as product_weight,
(case when cmrc_Productrelationship.freegroundshipping=0 then 'N' else 'Y' end) as free_ground_shipping,cmrc_Productrelationship.flatshipping as flat_shipping_fee,
(case when cmrc_Productrelationship.realtimeShipping =0 then 'N' else 'Y' end) as real_time_shipping,'' as shipping_weight_lbs,
(case when (cmrc_Productrelationship.AlaskaShipping=0 or cmrc_Productrelationship.AlaskaShipping is null) then 'N' else 'Y'end) as Alaska,
(case when (cmrc_Productrelationship.APO=0 or cmrc_Productrelationship.APO is null) then 'N' else 'Y' end) as APO,
(case when (cmrc_Productrelationship.USProtectorates=0 or cmrc_Productrelationship.USProtectorates is null) then 'N' else 'Y' end) as USProtectorates,
(case when cmrc_Productrelationship.Canada=0 or cmrc_Productrelationship.Canada is null then 'N' else 'Y' end) as Canada,
(case when cmrc_Productrelationship.International=0 or cmrc_Productrelationship.International is null then 'N' else 'Y' end)as International,
cmrc_Productrelationship.catkey1 as prod_type_key1,cmrc_Productrelationship.catkey2 as prod_type_key2,
cmrc_Productrelationship.catkey3 as prod_type_key3,cmrc_Productrelationship.catkey4 as prod_type_key4,
cmrc_Productrelationship.catkey5 as prod_type_key5,cmrc_Productrelationship.catkey6 as prod_type_key6,
cmrc_Productrelationship.catkey7 as prod_type_key7,cmrc_Productrelationship.catkey8 as prod_type_key8,
cmrc_Productrelationship.catkey9 as prod_type_key9,cmrc_Productrelationship.catkey10 as prod_type_key10,
cmrc_Productrelationship.merchantID as merchant_id,cmrc_Productrelationship.nodeid as merchant_catalog_id,
cmrc_Productrelationship.relatedskus as related_products,cmrc_products.stockquantity as inventory_count,
cmrc_products.active as availability,cmrc_Productrelationship.Subimage1 as add_image1,
cmrc_Productrelationship.subimage2 as add_image2,cmrc_Productrelationship.subimage3 as add_image3,
cmrc_Productrelationship.subimage4 as add_image4,cmrc_Productrelationship.subimage5 as add_image5,
cmrc_Productrelationship.subimage6 as add_image6,cmrc_Productrelationship.subimage7 as add_image7,
cmrc_Productrelationship.subimage8 as add_image8
from cmrc_manufacturer inner join cmrc_products on cmrc_products.mid=cmrc_manufacturer.mid
inner join cmrc_productrelationship on cmrc_productrelationship.prodid=cmrc_products.productid
inner join cmrc_categories_products on cmrc_categories_products.productid=cmrc_productrelationship.prodid left outer join
cmrc_categories on cmrc_categories.categoryid=cmrc_categories_products.categoryid LEFT outer JOIN cmrc_tax ON [CMRC_Products].[ProductID]=[CMRC_Tax].[ProductID]
where cmrc_productrelationship.merchantid=@merchantID
March 13, 2008 at 3:37 am
in general seeing the query still doesn't help - you'll need to examine the query plans from the two machines to see the differences.
You should make sure your stats are upnto date and indexes recently rebuilt etc. etc. on your prod box.
the book SQL Server 2000 performance tuning ISBN 0-7356-1270-6 is a good introduction to the whole subject and is still relevant for sql 2005.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply