ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,586 through 6,600 (of 10,143 total)

RE: Query Optimization using Indexes

Ninja's_RGR'us (10/17/2011)
I'd be most happy to be proven wrong... 😉
Heh we both know that's not going to happen! BUT - I reckon I can get close to your solution...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 7:55 am

#1395340
RE: Query Optimization using Indexes

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 7:50 am

#1395333
RE: Query Optimization using Indexes

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 7:41 am

#1395325
RE: Are the posted questions getting worse?

Jeff Moden (10/16/2011)
GilaMonster (10/15/2011)
Jeff Moden (10/15/2011)
Heh... nah... you're a good friend... so I thawed the chops for ya. 😛
And even de-boned and par-cooked em to be really nice.
That was just...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 6:58 am

#1395289
RE: Query Optimization using Indexes

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 6:49 am

#1395280
RE: Query Optimization using Indexes

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 6:47 am

#1395279
RE: Query Optimization using Indexes

Comment out the index hint!
FROM orderlines ol -- with (index(Ix_Prodline)) <<-- keep this out of the query!!
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 6:43 am

#1395278
RE: Query Optimization using Indexes

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,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 5:47 am

#1395247
RE: Query Optimization using Indexes

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 5:02 am

#1395233
RE: Finding the column containing the minimum value

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),...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 3:52 am

#1395207
RE: Query Optimization using Indexes

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 3:12 am

#1395195
RE: Finding the column containing the minimum value

Gianluca Sartori (10/17/2011)
Cadavre (10/17/2011)
Gianluca Sartori (10/14/2011)
I couldn't resist!
Me either 😀
I already knew it was slow, but it was such a fun problem!
It's something you don't stumble upon very often.
As I...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 2:55 am

#1395191
RE: Query Optimization using Indexes

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...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 2:09 am

#1395173
RE: Finding the column containing the minimum value

Cadavre (10/17/2011)
Gianluca Sartori (10/14/2011)
I couldn't resist!
Me either 😀
========== BASELINE ==========
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 205 ms.
================================================================================
========== CROSS JOIN ==========
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 17, 2011 at 1:59 am

#1395172
RE: TESTINg against destiantion

Try LEFT JOINing [ODS].[AC_Snapshot] again (with a different alias).
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

October 14, 2011 at 10:20 am

#1394753

Viewing 15 posts - 6,586 through 6,600 (of 10,143 total)