ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,091 through 6,105 (of 10,143 total)

RE: PATINDEX and Regular Expression

SELECT *
FROM (SELECT ID = 1, somecolumn = '399,243.01' UNION ALL -- YES
SELECT ID = 2, '£399,243.01' UNION ALL -- NO
SELECT ID = 3, '39924301' UNION ALL --...
^{“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

July 9, 2012 at 5:18 am

#1510263
RE: Index on View in query plan.

Shcherbunov Neil (7/9/2012)
ChrisM@Work (7/9/2012)
Shcherbunov Neil (7/7/2012)
ChrisM@home (7/6/2012)
Not quite - NOEXPAND is the hint to use the view.
Yes, but ONLY if you reference view (not table!) in your query. See code...
^{“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

July 9, 2012 at 5:09 am

#1510260
RE: Index on View in query plan.

Shcherbunov Neil (7/7/2012)
ChrisM@home (7/6/2012)
Not quite - NOEXPAND is the hint to use the view.
Yes, but ONLY if you reference view (not table!) in your query. See code example in start...
^{“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

July 9, 2012 at 2:20 am

#1510209
RE: Need help with difficult Dynamic SQL query

zxxz (7/6/2012)
I just can't seem to get it right, can I? Thank you for your work; my experience with SQL does not go much beyond 'SELECT... FROM'. I simply tried...
^{“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

July 6, 2012 at 9:17 am

#1509756
RE: MERGE Statement performance

SQL* (7/6/2012)
hi all,
Merge statement is giving performance difference when We are updating the records using merge vs first delete the desired records then run the merge statement query (it 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

July 6, 2012 at 6:58 am

#1509663
RE: Today's Random Word!

Today's Random Worm!
Nature has a few more years' experience of horror than us.
^{“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

July 6, 2012 at 6:25 am

#1509642
RE: Generating Non-uniform Random Numbers with SQL

Jeff Moden (7/6/2012)
GPO (7/3/2012)
Don't suppose that picture of the fish is a veiled reference to poisson?
Whatever the case, I'll just bet it scales well. 😛
NOOOOOooooooooo!!!
^{“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

July 6, 2012 at 6:16 am

#1509632
RE: Index on View in query plan.

Shcherbunov Neil (7/6/2012)
So, after all I have only 2 questions:
1. Why query optimizer resist to use IX_v index so hard?? :blink:
2. Does it all mean that I just CAN'T force...
^{“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

July 6, 2012 at 5:45 am

#1509618
RE: Cese in where condition

muthuselvi.devarajan (7/6/2012)
Hi,
I used case as below but im getting error
select Pay.DetailId, clm.AssetId,
case when Pay.DetailId IS null then isnull(clm.NoteIds,0) else isnull(pay.NoteIds,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

July 6, 2012 at 4:51 am

#1509597
RE: Points balance

a2zwd (7/6/2012)
...
I am struck here
So are we. If you really want help with this, then take the time to read the forum etiquette article. It shows you how to post...
^{“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

July 6, 2012 at 4:29 am

#1509588
RE: Locate row with bad data

Bobby Glover (7/5/2012)
Probably an easy one but I have a table (TABLE A) which is nserting data into another table (TABLE B).
The data types on the tables are not 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

July 6, 2012 at 3:41 am

#1509550
RE: Update Query Help

vijayarani87.s (7/6/2012)
Currently my updte statement is updating only for the first metric ID in the lean table
Of course. Only one row from LEAN will be used to update one row...
^{“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

July 6, 2012 at 2:58 am

#1509529
RE: Generating Non-uniform Random Numbers with SQL

dwain.c (7/4/2012)
... The fish is just a fish. Poisson would probably be relieved.
That's no ordinary fish Dwain, that's a biggun, it stands out from the crowd - like your...
^{“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

July 6, 2012 at 2:07 am

#1509514
RE: What would be the best way to get the date from the following strings?

fawadafr (7/5/2012)
I came up with an easier way:
Check this out:
select productcode,
(replace(substring(p2.productname,patindex('%Availability: %',p2.productname)+14,100),'</span>','')) AS Availability
from products
I did, and it fails.
Test it against the sample data. Fix it, then add whatever...
^{“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

July 6, 2012 at 1:56 am

#1509511
RE: Query runs very slow due to FOR XML PATH usage

Hi Paul
4 minutes down to 20 seconds is reasonable. Can you post the actual plan with the new indexes in place please? Let's see if there's more room for 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

July 6, 2012 at 1:48 am

#1509510

Viewing 15 posts - 6,091 through 6,105 (of 10,143 total)