ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,401 through 5,415 (of 10,143 total)

RE: How do I join tables, but select top 1 from 1-many tbl?

Don. (10/12/2012)
Essentially I have a Contact table and a History table.
I'd like to get the join the tables, but only show the most recent record in History.
How would I 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 12, 2012 at 9:57 am

#1548747
RE: Find nearest date (looking in both directions)

Cadavre (10/12/2012)
ChrisM@Work (10/12/2012)
ps Craig, if you're in the Hob at 6.15, I'll buy you a pint 😉
That the one on Broad Street? I'm in the process of moving house, we...
^{“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 12, 2012 at 9:52 am

#1548743
RE: Find nearest date (looking in both directions)

Here are the timings from a slender 1 million row Periods table, with an ordinary index on startdate:
========== CADAVRE ==========
Table 'Worktable'. Scan count 1, logical reads 254686, physical reads 0,...
^{“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 12, 2012 at 9:29 am

#1548725
RE: Find nearest date (looking in both directions)

Cadavre (10/12/2012)
OK, I'm back in the office 🙂
...
Looking at the results suggests to me that I did something wrong in the testing 😀
If not though, then the huge difference is...
^{“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 12, 2012 at 8:28 am

#1548683
RE: Find nearest date (looking in both directions)

Cadavre (10/12/2012)
I'm not back yet, but will be leaving soon as it looks like they're nearly finished so can test in 30 minutes or so. But could your code not...
^{“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 12, 2012 at 7:25 am

#1548630
RE: HEELLLP with query please

mamzy.rahman (10/12/2012)
hi guys basically iv got a table here with a field called aims and a field called modules. i need to run a query to show if one aim...
^{“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 12, 2012 at 6:53 am

#1548604
RE: which is better to user the cross join or while loop ?

nitin.varshney (10/12/2012)
Hi
Please tell me which is better for the performance impact ?
1. Insert into #temp table Using the cross join with tables.
2. Insert into #temp table using the while...
^{“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 12, 2012 at 6:39 am

#1548597
RE: Find nearest date (looking in both directions)

Cadavre (10/12/2012)
I'll check when I get back from lunch. My gut feeling is that mine will be heavier on reads whilst yours is heavier on scans.
Not a clue about...
^{“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 12, 2012 at 6:35 am

#1548593
RE: Find nearest date (looking in both directions)

Cadavre (10/12/2012)
I'm on my lunch waiting in the car for something, so this is untested and coded together using the text editor on my phone.
Would this work for...
^{“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 12, 2012 at 6:28 am

#1548591
RE: Find nearest date (looking in both directions)

This is quite a bit different to Craig's, it would be interesting to discover the performance of them both:
SELECT s.*,
ID= CASE WHEN x.lag < y.lag THEN x.ID ELSE y.ID...
^{“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 12, 2012 at 6:26 am

#1548588
RE: Explicit Transaction to improve performance?

Can you post the code, Mahesh? Also, if you can, the actual execution plans?
^{“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 12, 2012 at 3:50 am

#1548525
RE: Query Analysis

deepika_goyal (10/11/2012)
Hello!!,
NO body has replied on thiss.
Please give some suggestion.
Thanks!!
Hello
You are likely to get a much faster response if you provide DDL for your sample data. Here's your 'master' table,...
^{“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 12, 2012 at 3:49 am

#1548524
RE: help with difficult situation

Here's a guess based upon the information folks have already extracted from the OP:
--test ddl
DROP TABLE #sales_log
CREATE TABLE #sales_log (member_id varchar(50),company_id varchar(50),purchase_dt datetime,purchase_amt money)
insert into #sales_log(member_id,company_id,purchase_dt,purchase_amt)
select 'Jen9921', '222141','1/2/2011','11.99' union...
^{“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 12, 2012 at 2:02 am

#1548476
RE: Force a query to use a specific execution plan

Yes, in a limited sense. Check out Plan Guides in BOL.
^{“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 9, 2012 at 10:23 am

#1547283
RE: How to join four tables using left outer join

Eugene Elutin (10/8/2012)
Doesn't look like SQL query to me. What curve-brackets are doing there?
It looks vaguely familiar - possibly the output of the Crystal query designer?
^{“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 9, 2012 at 1:25 am

#1547041

Viewing 15 posts - 5,401 through 5,415 (of 10,143 total)