Hmm. I use dynamic because the user set the @gosUrun. I take it from codebehind and send to sql procedure. Yesterday I wrote inner join and now its ok. But I didn't know your solution. I will try it asap. How is the performance of yours?
Kadir Avcı Software Developer | Freelance Web Designer web: www.kad1r.com twt: www.twitter.com/kad1r On Wed, Dec 2, 2009 at 1:49 PM, Jamie Fraser <[email protected]> wrote: > Why are you using SQL like that? You should avoid the use of dynamic SQL > like this as it performs comparatively poorly. > > If you are using SQL 2005 you can parameterise the TOP query (SELECT TOP > @topcount....) > > If you are using SQL 2000 or before (why?) you can use the SET ROWCOUNT > @topcount pattern. > > So you could rewrite your query as (assuming SQL2005 or later) > > > ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] > @subcatID nvarchar (500), > @gosUrun nvarchar(50) > AS > BEGIN > SELECT TOP (@gosUrun) > AVMWeb_Items.*, > (SELECT subcatname FROM AVMWeb_ItemSubCategorys WHERE > AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID) AS subcatname, > (SELECT brandname FROM AVMWeb_ItemBrands WHERE AVMWeb_ItemBrands.id = > AVMWeb_Items.brandID) AS brandname, > (SELECT TOp 1 deger FROM AVMWeb_Item_Resources WHERE > AVMWeb_Item_Resources.itemID = AVMWeb_Items.id) AS deger > FROM AVMWeb_Items WHERE AVMWeb_Items.subcatID = @subcatID > > And the second procedure as > > ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] > @subcatID nvarchar (500), > @gosUrun nvarchar(50) > AS > BEGIN > Select top @gosUrun > AVMWeb_Items.* , > AVMWeb_ItemSubCategorys.*, > AVMWeb_ItemBrands.*, > AVMWeb_Item_Resources.* > From AVMWeb_Items, AVMWeb_ItemSubCategorys, AVMWeb_ItemBrands, > AVMWeb_Item_Resources > Where AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID and > AVMWeb_ItemBrands.id = AVMWeb_Items.brandID and > AVMWeb_Item_Resources.itemID = AVMWeb_Items.id and > AVMWeb_Items.subcatID = @subcatID + ; > > When you say "second procedure is not working" what do you mean? It looks > to me like you should be using a combination of INNER and LEFT JOINs to > achieve what you want. > > > > On Tue, Dec 1, 2009 at 11:01 AM, Kadir Avci <[email protected]> wrote: > >> Hello. I have an sql procedure and when I run it the time is 10 seconds. >> I optimize it and now its take 1 second. But I have a problem. If item has >> two or more pictures second procedure is not working properly. >> How can I get only one item_resource in second procedure? >> >> *Here is my first sql procedure.* >> ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] >> @subcatID nvarchar (500), >> @gosUrun nvarchar(50) >> AS >> BEGIN >> DECLARE @sqlStr nvarchar(4000); >> set @sqlStr = 'Select top ' + @gosUrun + 'AVMWeb_Items.*, ' + >> '(Select subcatname from AVMWeb_ItemSubCategorys where >> AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID) as subcatname, ' + >> '(Select brandname from AVMWeb_ItemBrands where AVMWeb_ItemBrands.id = >> AVMWeb_Items.brandID) as brandname, ' + >> '(select top 1 deger from AVMWeb_Item_Resources where >> AVMWeb_Item_Resources.itemID = AVMWeb_Items.id) as deger ' + >> 'from AVMWeb_Items ' + >> 'where AVMWeb_Items.subcatID = ''' + @subcatID + ''''; >> exec (@sqlStr) >> END >> >> >> *Here is my second sql procedure.* >> ALTER PROCEDURE [dbo].[get_AVMWeb_Items_bySubCatID_ForCatContent] >> @subcatID nvarchar (500), >> @gosUrun nvarchar(50) >> AS >> BEGIN >> DECLARE @sqlStr nvarchar(4000); >> set @sqlStr = 'Select top ' + @gosUrun + >> 'AVMWeb_Items.* , ' + >> 'AVMWeb_ItemSubCategorys.*, ' + >> 'AVMWeb_ItemBrands.*,' + >> 'AVMWeb_Item_Resources.* ' + >> 'From AVMWeb_Items, AVMWeb_ItemSubCategorys, AVMWeb_ItemBrands, >> AVMWeb_Item_Resources ' + >> 'Where AVMWeb_ItemSubCategorys.id = AVMWeb_Items.subcatID and ' >> + >> 'AVMWeb_ItemBrands.id = AVMWeb_Items.brandID and ' + >> 'AVMWeb_Item_Resources.itemID = AVMWeb_Items.id and ' + >> 'AVMWeb_Items.subcatID = ''' + @subcatID + ''''; >> exec (@sqlStr) >> END >> >> >> >> Kadir Avcı >> Software Developer | Freelance Web Designer >> web: www.kad1r.com >> twt: www.twitter.com/kad1r >> > >
