March 13, 2008 at 4:10 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
This is the stored procedure, that I have written
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,
substring((dbo.GetTreeCategory(cmrc_categories.Parent_CategoryID)),1,(charindex(',',dbo.GetTreeCategory(cmrc_categories.Parent_CategoryID))-1)) as Department,
substring((dbo.GetTreeCategory(cmrc_categories.Parent_CategoryID)),(charindex(',',dbo.GetTreeCategory(cmrc_categories.Parent_CategoryID))+1),len(dbo.GetTreeCategory(cmrc_categories.Parent_CategoryID))) as Sub_Department,
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 7:03 am
Can you show the execution plan for this query? Output it as XML and attach it to a post.
You may want to look at SSIS as a mechanism for moving large amounts of data around.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 13, 2008 at 8:05 am
I'd definitely think SSIS if this is data movement, as in download or transfer to another server.
If not, get the plan, be sure things are indexed properly, and I might suck out the list to a temp table without the left join, then add in the left join as a second step.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply