Huge query/temp db problem - 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

General SQL Server Forums

New to SQL Server Administration

Huge query/temp db problem

Author

Topic

itsarnie
Starting Member

18 Posts

Posted - 2010-01-28 : 07:16:51

Hi,
I have a query with 1 union. The query just keeps on running forever.

when I ran the 1st query,it fetched me 80000 records.
when I ran the 2nd query separatly,the query kept running for 2 hours and then threw error like Tempdb full.

Please suggest me how to make it work.

Thanks and regards
Arnie,

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 07:30:27

How many rows does Query 2 select?

Change the SELECT to just SELECT COUNT(*) to see the number of rows.

Is that the number of rows you are expecting? If lots more than you want then correct the logic

Might also be that there are sub-queries that are being evaluated. Check the Query Plan and see if there are Table Scans or Index Scans which should be Index Seeks - or maybe you can make them into Index Seeks by adding indexes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-28 : 08:31:24

UNION implies that a sort will occur to only return the unique records.
That is a very costly operation.

Please post your query here and maybe we can do something about it.

N 56°04'39.26"
E 12°55'05.63"

itsarnie
Starting Member

18 Posts

Posted - 2010-02-01 : 00:57:05

Here is the query...

I am not able to figure out the problem yet.It just keeps on running.

select distinct ECHANTILLON,LIBTEXTEDIAG, Langue, ordrecom,TEXTE,ORDRETEXTE,COUNTRY from
(select a.ECHANTILLON,a.LIBTEXTEDIAG,NULL Langue, a.ordrecom,NULL TEXTE,NULL ORDRETEXTE,SI.COUNTRY,
SI.countryname,c.deltaflag from F_TTEXTEDIAG a,
F_TECHANTILLON c, F_TPSUIVI CO, F_TESUIVI2 E, LA_EUR_SITE SI
where a.ECHANTILLON=c.ECHANTILLON
and c.SUIVI= CO.SUIVI and ltrim(rtrim(c.TYPEORG))=ltrim(rtrim(CO.TYPEORG))
and ltrim(rtrim(e.suivi)) = ltrim(rtrim(co.suivi))
and ltrim(rtrim(SI.SOURCEID)) collate database_default=ltrim(rtrim(E.clientexp))
and SI.COUNTRY in ('F')) a
where countryname='U11' and deltaflag='I'

union

SELECT b.ECHANTILLON,a.libtextetype,a.langue,a.ordrecom,a.TEXTE,b.ORDRETEXTE,SI.COUNTRY FROM F_TVERSTEXTE A,
F_TECHANDIAG b, F_TECHANTILLON c, F_TPSUIVI CO, F_TESUIVI2 E, LA_EUR_SITE SI
WHERE ltrim(rtrim(A.TEXTE)) =ltrim(rtrim(b.texte))
and ltrim(rtrim(b.ECHANTILLON))=ltrim(rtrim(c.ECHANTILLON))
and c.SUIVI= CO.SUIVI
and ltrim(rtrim(c.TYPEORG))=ltrim(rtrim(CO.TYPEORG))
and ltrim(rtrim(e.suivi)) = ltrim(rtrim(co.suivi))
and ltrim(rtrim(SI.SOURCEID)) collate database_default=ltrim(rtrim(E.clientexp))
and SI.COUNTRY in ('F')
and a.libtextetype is not null
and SI.countryname='U11'
and c.deltaflag='I' and a.langue='F'

and b.echantillon not in

(select distinct ECHANTILLON from
(select a.ECHANTILLON,SI.countryname,c.deltaflag from F_TTEXTEDIAG a,
F_TECHANTILLON c, F_TPSUIVI CO, F_TESUIVI2 E, LA_EUR_SITE SI
where a.ECHANTILLON=c.ECHANTILLON and c.SUIVI= CO.SUIVI
and ltrim(rtrim(c.TYPEORG))=ltrim(rtrim(CO.TYPEORG))
and ltrim(rtrim(e.suivi)) = ltrim(rtrim(co.suivi))
and ltrim(rtrim(SI.SOURCEID)) collate database_default=ltrim(rtrim(E.clientexp))
and SI.COUNTRY in ('F')) a
where countryname='U11' and deltaflag='I'
)

Thanks and regards
Arnie,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-01 : 02:58:31

Your NOT IN part is very unecessary, because you already use UNION (and distinct!) to get rid of duplicates.
Try this rewrite

select		a.ECHANTILLON,
		a.LIBTEXTEDIAG,
		NULL as Langue,
		a.ordrecom,
		NULL as TEXTE,
		NULL as ORDRETEXTE,
		SI.COUNTRY,
		SI.countryname,
		c.deltaflag
from		F_TTEXTEDIAG AS a
INNER JOIN	F_TECHANTILLON as c ON c.ECHANTILLON = a.ECHANTILLON
			AND c.deltaflag = 'I'
INNER JOIN	F_TPSUIVI as CO ON CO.TYPEORG = c.TYPEORG
			AND CO.SUIVI = c.SUIVI
INNER JOIN	F_TESUIVI2 as E ON e.suivi = co.suivi
INNER JOIN	LA_EUR_SITE as SI ON SI.SOURCEID = E.clientexp
where		SI.COUNTRY = 'F'
		AND SI.countryname = 'U11'

UNION

SELECT		b.ECHANTILLON,
		a.libtextetype,
		a.langue,
		a.ordrecom,
		a.TEXTE,
		b.ORDRETEXTE,
		SI.COUNTRY
FROM		F_TVERSTEXTE as A 
INNER JOIN	F_TECHANDIAG as b ON b.texte = A.TEXTE
INNER JOIN	F_TECHANTILLON as c ON c.ECHANTILLON = b.ECHANTILLON
			AND c.deltaflag = 'I'
INNER JOIN	F_TPSUIVI as CO ON CO.TYPEORG = c.TYPEORG
			AND CO.SUIVI = c.SUIVI
INNER JOIN	F_TESUIVI2 as E ON e.suivi = co.suivi
INNER JOIN	LA_EUR_SITE as SI ON SI.SOURCEID = E.clientexp
WHERE		SI.COUNTRY = 'F'
		and SI.countryname='U11' 
		and a.libtextetype IS NOT NULL
		and a.langue = 'F'

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources