ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,706 through 3,720 (of 10,143 total)

RE: How to use scalar function without WHILE

Thanks Alan. I should point out that it's quite a long way from being a full Levenshtein implementation! I don't think it would be impossible - just too costly.
^{“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

January 28, 2014 at 6:17 am

#1683880
RE: Query just hangs!

Gail's suggestion using DENSE_RANK() is well worth a try when you get time.
^{“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

January 28, 2014 at 3:01 am

#1683832
RE: Perfromance Issue with the Query...

mote.ajit2 (1/27/2014)
Thanks for reply!
Earlier I was collecting data in some temporary table so that I can use it as input to query on main table. Now I have removed that...
^{“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

January 28, 2014 at 1:40 am

#1683817
RE: SQL Function to convert date time to string

;WITH SampleData AS (
SELECT MyDate = CAST('2014-01-27 05:59:59.997' AS DATETIME) UNION ALL
SELECT '2014-01-27 06:00:00.000' UNION ALL
SELECT '2014-01-27 06:00:00.003' UNION ALL
SELECT '2014-01-27 17:59:59.994' UNION ALL
SELECT '2014-01-27 17:59:59.997' UNION ALL
SELECT '2014-01-27 18:00:00.000')
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

January 27, 2014 at 2:06 am

#1683451
RE: Query just hangs!

They're very narrow triangles - having dealt with this in the near past. Nevertheless, with two of them ORred together, the optimiser will likely choke. Try splitting them up 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

January 27, 2014 at 1:51 am

#1683445
RE: Optimize Update Statement

Jeff Moden (1/25/2014)
wit_jp2001 (1/24/2014)
Thanks,
I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest ...
^{“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

January 27, 2014 at 1:45 am

#1683443
RE: Incluse Saturday and Sunday in calculation

DaveDB (1/24/2014)
This comment is very insulting.
"Perhaps the reason for my request for additional material is becoming clearer now.
The fact that accomplished professionals, who would usually be able to...
^{“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

January 24, 2014 at 9:30 am

#1683184
RE: Datediff() function

rabih_karam (1/24/2014)
as I understand,
"To calculate the number of days between date1 and date2, you can use either Day of year ("Y") or Day ("D"). When interval is Weekday ("W"), DateDiff...
^{“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

January 24, 2014 at 7:30 am

#1683127
RE: Optimize Update Statement

wit_jp2001 (1/24/2014)
Thanks,
I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. ...
^{“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

January 24, 2014 at 6:41 am

#1683116
RE: how to use union all in my procedure?

Does the stored procedure reference the Customers table?
Try to keep your code simple:
SELECT
h.company_no,
h.invoice_no,
h.invoice_locn,
h.invoice_type,
a.item_no as Kit_Ref_group,
b.item_no ,
max(CASE WHEN A.invoice_qty > B.invoice_qty THEN 0 ELSE A.invoice_qty END) as sales ,
b.invoice_qty - (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

January 24, 2014 at 6:35 am

#1683115
RE: Applying constraints to WHERE statement

Anytime.
Third row up in my sig block has links to a couple of outstanding articles on APPLY. Well worth a read.
^{“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

January 24, 2014 at 6:25 am

#1683110
RE: Applying constraints to WHERE statement

CROSS APPLY is an excellent tool for building complex WHERE clauses and would also help you to figure out if it's worthwhile building up an exclusion table as suggested. Once...
^{“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

January 24, 2014 at 6:02 am

#1683096
RE: Reducing deadlocks with update query hint or other method...

Dennis Post (1/24/2014)
If you have nolock, then you have the potential for dirty reads, duplicate rows and missed rows and it doesn't matter (for the latter 2) that each process...
^{“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

January 24, 2014 at 4:28 am

#1683076
RE: Optimize Update Statement

Grant Fritchey (1/24/2014)
Nice job Chris. I'd love to see what the execution plan of the original query looked like.
Thanks Grant. Me too, there are over 70 reads of the lookup.
^{“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

January 24, 2014 at 4:01 am

#1683071
RE: Optimize Update Statement

IgorMi, you can use http://extras.sqlservercentral.com/prettifier/prettifier.aspx.
I tend not to. If I'm about to spend a few minutes reading and understanding a chunk of code, reformatting it easily becomes part of the...
^{“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

January 24, 2014 at 4:00 am

#1683070

Viewing 15 posts - 3,706 through 3,720 (of 10,143 total)