Today  i have an experince with sql server 2005 on our production server with 6000 records and almost 12000 records in category table.Inner join is goes wrong for me and sub query is run well in our production machine.If some thing wrong please correct me…
May be it gives a good idea to others to oraganize their query .
NB:I am using this query for creating text file  for Node import module of drupal.
query 1 never finished with in 1 minute:-
select distinct top 1 A.Idarticolo,Titolo,titoletto,autore,fonte,dbo.getOperatore(A.idoperatore) as IdOperatore,
(cast(day(data) as varchar(2))+’/’+cast(month(data) as varchar(2))+’/’+cast(year(data) as varchar(4)) + ‘ ‘ +ora)
datatime_,data,dbo.getTags(A.Idarticolo) as Tags from tab_articoli A inner join tab_merc_catearticoli B
on A.Idarticolo=B.Idarticolo where b.codi_cate_arti not in (63,58,61,48,49) order by data asc
query 1 modified with subquery:(finished in 0 seconds)
select distinct top 1 A.Idarticolo,Titolo,titoletto,autore,fonte,dbo.getOperatore(A.idoperatore) as IdOperatore,
(cast(day(data) as varchar(2))+’/’+cast(month(data) as varchar(2))+’/’+cast(year(data) as varchar(4)) + ‘ ‘ +ora)
datatime_,data,dbo.getTags(A.Idarticolo) as Tags from tab_articoli A where a.idarticolo not in(
select idarticolo from tab_merc_catearticoli  where codi_cate_arti not in (63,58,61,48,49)
)
order by data asc
functions :-

create FUNCTION getarticoli
(@idarticolo int)
RETURNS varchar(5000)
AS
BEGIN
declare @corelati varchar(5000)
SELECT @corelati= coalesce(@corelati + ‘$’, ”) + titolo from tab_articoli A inner join
TAB_MERC_ARTI_CORR B on
A.Idarticolo=B.IdArtCorr where a.Idarticolo=@Idarticolo
return @corelati
end


create FUNCTION getcategory
(@idarticolo int)
RETURNS varchar(1000)
AS
BEGIN
declare @CategoryList varchar(1000)
SELECT @CategoryList= coalesce(@CategoryList + ‘$’,”) + DESC_CATE_ARTI from TAB_MERC_CATE_ARTI A inner join  TAB_MERC_CATEARTICOLI B
on A.CODI_CATE_ARTI=B.CODI_CATE_ARTI where B.Idarticolo=@idarticolo
return @CategoryList
end

create FUNCTION getOperatore
(@Idoperatore int)
RETURNS int
AS
BEGIN
return case @Idoperatore
when 1 then 1
when 39 then 3
when 40 then 4
when 41 then 5
when 42 then 6
when 43 then 7
else 1
end
end
Alter FUNCTION getTags
(@idarticolo int)
RETURNS varchar(1000)
AS
BEGIN
declare @tagList varchar(1000)
SELECT @tagList= coalesce(@tagList + ‘$’,”) + Tag from Tab_TagCloud where Idarticolo=@idarticolo
return @tagList
end
print dbo.getTags(8817)

Advertisements