ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,556 through 6,570 (of 10,143 total)

RE: Selecting particular rows

ByronOne (10/20/2011)
Chris
That works perfectly!!
Thanks so much...
You're welcome, thanks for the feedback.
^{“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 20, 2011 at 9:41 am

#1397507
RE: Selecting particular rows

Dev @ +91 973 913 6683 (10/20/2011)
I only want to bring back the first instance
This is confusing. How can you guarantee the order of rows in a heap?...
^{“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 20, 2011 at 9:32 am

#1397500
RE: Are the posted questions getting worse?

Ninja's_RGR'us (10/20/2011)
Brandie Tarvin (10/20/2011)
Grrrr. Me be troll. "PANTS! PANTSPANTSPANTS! I dare you! GRRRRRR".
And on a more serious note, I need help badly, please. This one has me totally confused and...
^{“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 20, 2011 at 8:55 am

#1397462
RE: Join using Charindex -- there's got to be a better way?

This should give you some ideas, Peter.
SELECT
DrugNameString = c.DrugName,
DrugName = x.Item
FROM (
SELECT ClaimID = 123, DrugName = 'Oral Solution Citalopram HBR' UNION ALL
SELECT 124, 'CITALOPRAM TABLETS'...
^{“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 20, 2011 at 8:52 am

#1397453
RE: Query Optimization using Indexes

Blimey! I've only ever found one bug in SQL Server, that was 5 years ago.
^{“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 20, 2011 at 7:54 am

#1397368
RE: Query Optimization using Indexes

Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
@ninja
But from now i m not investing my time in reading and replying to ur post...
😉
19K+ points. ±5 threads started so far. 3 of them still...
^{“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 20, 2011 at 7:47 am

#1397363
RE: Query Optimization using Indexes

Ninja's_RGR'us (10/20/2011)
Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
@ninja
I know in b/w 8 hours u r usually....see the posts read them...
its lot of work to do...its very time consuming to read all the posts..
I can...
^{“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 20, 2011 at 7:46 am

#1397360
RE: Query Optimization using Indexes

Ninja's_RGR'us (10/20/2011)
adlakha.22 (10/20/2011)
what should i do? 🙁
Keep working with Chris... as I said I'm not doing 8+ hours of free tuning work here for a single issue. ...
^{“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 20, 2011 at 7:26 am

#1397329
RE: Query Optimization using Indexes

adlakha.22 (10/20/2011)
what should i do? 🙁
Run the tests, post the results.
If there's no significant gain from my last suggestion, a gain which you're happy with, then Remi will take...
^{“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 20, 2011 at 7:22 am

#1397324
RE: Query Optimization using Indexes

Quite possibly Remi, hence the test - but it could be worth it. I'd expect some improvement.
^{“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 20, 2011 at 6:18 am

#1397273
RE: Query Optimization using Indexes

Well it's clear that a huge covering index would improve the performance of this query but the cost of maintaining it would be considerable. Here's a compromise.
CREATE NONCLUSTERED INDEX [IX_Testing]...
^{“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 20, 2011 at 6:08 am

#1397261
RE: Query Optimization using Indexes

So each one of the three takes around 40 to 50 seconds?
Last test: add this index, then run each query again, noting the time each query takes to run:
CREATE...
^{“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 20, 2011 at 5:21 am

#1397225
RE: Joining 2 result sets

;WITH First_record_set as (
select x.patientid,row_number() over (partition by x.patientid order by x.observationid) as rid,x.observationid,y.code
from xxx x
Inner Join yyy y on x.observationid = y.observationid
where y.code like 'glucose' and o.patientid =...
^{“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 20, 2011 at 4:50 am

#1397207
RE: Query Optimization using Indexes

adlakha.22 (10/20/2011)
@chris-2
query which u hv posted, took 58 sec to execte
and select qry executed in 3 sec(Select * from #Orderlines).
Not quite right - let's try again. Here are three...
^{“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 20, 2011 at 4:16 am

#1397188
RE: Query Optimization using Indexes

adlakha.22 (10/20/2011)
No there is no results...only messages indicating number of rows effected..
if we execute select * from #orderlines
then rows effected will display
Yes, that's exactly as expected. How long did each...
^{“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 20, 2011 at 3:52 am

#1397172

Viewing 15 posts - 6,556 through 6,570 (of 10,143 total)