There is of course a cost associated with maintaining an index - another subject.

Here's a few changes. Check out how long it takes to run, then I guess it's the...

Heh we both know that's not going to happen! BUT - I reckon I can get close to your solution...

Ninja's_RGR'us (10/17/2011)

---

You guys play with this all you want. You can grapple seconds left and right.

You want quantum leap improvement? You make that index with all the included...

adlakha.22 (10/17/2011)

---

This query is executed in 55 sec.

and at the same time i again execute the query with Ix_new that was taking 30 sec,(may be there was a n/w problem...

Jeff Moden (10/16/2011)

---

GilaMonster (10/15/2011)

---

And even de-boned and par-cooked em to be really nice.

That was just...

Ninja's_RGR'us (10/17/2011)

---

Unless you want to include pretty much every other columns in that table, that looks like the most-ish plan you can expect.

Might be worth it, might now. I...

Now try this:

IF OBJECT_ID('tempdb..#prodline') IS NOT NULL DROP TABLE #prodline

SELECT *

INTO #prodline

FROM (

SELECT prodline = '####' UNION ALL

SELECT '100' UNION ALL

SELECT '101' UNION ALL

SELECT '3M' UNION ALL

SELECT '80NIPA' UNION ALL

SELECT...

Comment out the index hint!

FROM orderlines ol -- with (index(Ix_Prodline)) <<-- keep this out of the query!!

Take the index hint out of the query. Index hints are very rarely used because the optimiser will almost always choose the best index for the job. In this case,...

adlakha.22 (10/17/2011)

---

Thx for reply chris

Now Query is Grouped By, 5 columns and it took 60 sec to execute complete join Query.

As u suggest i have created index with columns in...

Another method 3x faster than original stoned snail CROSS APPLY:

SELECT t.id, t.v1, t.v2, t.v3, t.v4, t.v5, t.v6, x.ColIndex

FROM @Tab1 t

CROSS APPLY (

SELECT rn = ROW_NUMBER() OVER (ORDER BY v),...

adlakha.22 (10/17/2011)

---

@chris-2 I did't get u on this

"to establish which if any can be dispensed with by using MAX() around the column in the output list, whilst retaining the original...

As I...

No problem, it's worth testing to see the results. Try trimming the columns from the GROUP BY one at a time to establish which if any can be dispensed with...

Cadavre (10/17/2011)

---

Me either 😀

========== BASELINE ==========

SQL Server Execution Times:

CPU time = 203 ms, elapsed time = 205 ms.

================================================================================

========== CROSS JOIN ==========

...