order by slows down query 1

[MrGandalf]

Hi,

I have an SQL that gives 6000 rows back as result. When I use an order by the execution time of the SQL is almost 3 minutes, without the order by 3 SECONDS. Does anyone have an explanation?

 

[vongrunt]

Are you using TOP clause in query?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[MrGandalf]

Nope

 

[vongrunt]

Post query here anyway...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[MrGandalf]

SELECT ip.INGANGSDAT AS ingangsdatum,ip.CLIENTNR AS clientnr, ip.INDICNR AS indicatienr, ip.PRODUCTNR AS productnr, ib.BESLUITNR AS besluitnr,
sp.SUBNUMMER AS subproductnummer, DATEPART(ww, ip.DATM_TOEW) AS toewijsweek, DATEPART(yyyy, ip.DATM_TOEW) AS toewijsjaar,
wp.PLAATSNR AS plaatsnr, wp.TOTAAL AS totaal, SPACE(25) AS omvang, p.TYPE, p.EENHPRIJS, ip.LEVER_PER, ip.TOTAAL_MIN, ip.KEER_PW,
ip.DATM_TOEW AS toewijsdatum, ip.AFSLUITDAT AS afsluitdatum, ib.DAT_GELDIG AS geldigheidsdatum,
CASE WHEN (ip.afsluitdat IS NULL) THEN 0 ELSE 1 END AS isAfgesloten, CASE WHEN (NOT ib.dat_geldig IS NULL AND ib.dat_geldig <= GETDATE()
+ 42) THEN 1 ELSE 0 END AS isVerlopen, RTRIM(LTRIM(ISNULL(sp.OMSCHR, p.ZOEKNAAM))) + SPACE(COL_LENGTH('product', 'zoeknaam')
- DATALENGTH(RTRIM(LTRIM(ISNULL(sp.OMSCHR, p.ZOEKNAAM))))) AS productnaam, z.ZOEKNAAM AS zorgaanbieder,
i.KD_BASMDL AS kodeBasismodel, CASE WHEN (Upper(k.m_v) = 'VRW') THEN 'Mw. ' ELSE 'Hr. ' END + k.ZOEKNAAM AS klantnaam,
k.ZOEKNAAM AS zoeknaam, k.GEB_DATUM AS geboortedatum, RTRIM(LTRIM(k.STRAAT))
+ ' ' + k.VOLL_HUISN + SPACE(25 - LEN(RTRIM(LTRIM(k.STRAAT)))) AS straatnaam, k.WOONPLAATS AS woonplaats, k.POSTCODE AS postcode,
CASE WHEN (i.kd_indmeth = 'FGI' AND (ib.op_wachtl = 'PGI' OR
ib.op_wachtl = 'BGI')) THEN 1 ELSE 0 END AS magDownloaden,
ip.CLIENTNR + ip.INDICNR + ip.BESLUITNR + ip.PRODUCTNR + ISNULL(sp.SUBNUMMER, SPACE(3)) + z.KODE AS pk
FROM zodata.INDIPRDC ip INNER JOIN
zodata.KLANT k ON k.CLIENTNR = ip.CLIENTNR INNER JOIN
zodata.ZORGLEVR z ON z.KODE = ip.ZORGLEVR INNER JOIN
zodata.INDIBESL ib ON ib.CLIENTNR + ib.INDICNR + ib.BESLUITNR = ip.CLIENTNR + ip.INDICNR + ip.BESLUITNR INNER JOIN
zodata.INDICS i ON i.CLIENTNR + i.INDICNR = ip.CLIENTNR + ip.INDICNR INNER JOIN
zodata.PRODUCT p ON p.KD_BASMDL + p.NUMMER = i.KD_BASMDL + ip.PRODUCTNR LEFT OUTER JOIN
zodata.SUBPROD sp ON UPPER(sp.KD_BASMDL) + UPPER(sp.NUMMER) + UPPER(sp.SUBNUMMER) = UPPER(i.KD_BASMDL)
+ UPPER(ip.PRODUCTNR) + UPPER(ip.KD_SUBPROD) LEFT OUTER JOIN
zodata.WL_PLAATS wp ON wp.CLIENTNR + wp.INDICNR + wp.BESLUITNR = ib.CLIENTNR + ib.INDICNR + ib.BESLUITNR AND
wp.KD_BASMDL + wp.PRODUCTNR = p.KD_BASMDL + p.NUMMER AND wp.MOMENT = 'A' AND wp.SUBPRODNR = ISNULL(sp.SUBNUMMER,
SPACE(3)) AND wp.BEGIN_DAT = ip.DATM_TOEW
WHERE (ip.ZORGLEVR = '071') AND (ip.DATM_TOEW >= CONVERT(DATETIME, '2000-07-21', 120)) AND (ip.DATM_TOEW <= CONVERT(DATETIME, '2005-08-04',
120)) AND (ip.ST_TOEWIJS = 'A')
order by ingangsdatum

 

[vongrunt]

Counter-question: what is the difference between for example:

INNER JOIN zodata.PRODUCT p ON p.KD_BASMDL + p.NUMMER = i.KD_BASMDL + ip.PRODUCTNR

... and:

INNER JOIN zodata.PRODUCT p ON p.KD_BASMDL = i.KD_BASMDL AND p.NUMMER =  ip.PRODUCTNR

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hmckillop]

Have you looked at the index and the query plan.
I would guess your index is based around the fields ipZORGLEVR, IP.DATM_TOEW and doesnt include ingangsdatum.

Maybe just a guess, but the query plan will tell more.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[DrSql]

How often you reindex those tables?
Think about dropping and recreate the index.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[MrGandalf]

Vongrunt: There is no difference in the join. I have written te SQL's in a way that it will work with a FOXPRO database and SQL database. That's why I use this syntax.

I tried the SQL with and without an index on ingangsdatum, that didn't mather. I found out that when I select less columns in the select statement, it is fast. When adding a few it is slow. It has nothing to do with the number of columns selected, but with the total width of the columns (I think).

Could it be that the size of the tempdb is the issue? Can I see how big it is?

 

[hmckillop]

Is this actual timings within Query Analyser, i.e. is it the actual query executing or is it the speed of returning results (check the time option when executing to see when the query has finished).

Have you looked at the query plan?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[MrGandalf]

This is the time within the Query Analyzer.

Query plan is same for the slow and fast query.

I found at that when using the query with less columns in the select statement(is fast-3 seconds), the temdb is not used. When adding columns(slow-3 minutes) the tempdb is growing to 400 mb. This could be what's slowing down.

Is it normal that adding a column can cause a query to give such a difference in execution time?

 

[vongrunt]

Some food for thought:

select T1.ContactName, T1.CustomerID, T2.PostalCode
into blah
from Northwind.dbo.Customers T1
cross join  Northwind.dbo.Customers T2

create index ix_blah on blah( CustomerID, PostalCode )

This code will produce some 8,000 rows in table "blah". Now, check execution plan for both queries:

-- nonclustered index seek + bookmark lookup =  kewl
select * from blah
where CustomerID = 'ANATR' and PostalCode = '05021'

-- table scan :E
select * from blah
where CustomerID+PostalCode = 'ANATR' + '05021'

-- drop table blah

I'm not claiming this behaviour is directly responsible for query slowdown... but indirectly - quite possible.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[MrGandalf]

Vongrunt, tx. By using AND's it's fast an doesn't require tempdb space.