ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,131 through 5,145 (of 10,143 total)

RE: Grouping records by time interval

geert.de.vylder (3/4/2013)
Hi ChrisM@Work,
Thank you for your reply. In attachment you will find the executionplan. The Tussen_Tickets table has at this moment 31779 records.
The query is almost correct. I only had...
^{“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

March 4, 2013 at 8:44 am

#1593039
RE: How to uncommit the transaction and execute the next statement in the cursor?

prakashr.r7 (3/4/2013)
ChrisM@Work (3/4/2013)
prakashr.r7 (3/4/2013)
...
Yes...i am restricted, Chris 🙁 . This is how my functionality works. So i have to stick with it. no other option. Have to generate unique random...
^{“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

March 4, 2013 at 8:29 am

#1593032
RE: How to uncommit the transaction and execute the next statement in the cursor?

prakashr.r7 (3/4/2013)
...
Yes...i am restricted, Chris 🙁 . This is how my functionality works. So i have to stick with it. no other option. Have to generate unique random values.
Checking...
^{“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

March 4, 2013 at 8:02 am

#1593019
RE: How to uncommit the transaction and execute the next statement in the cursor?

prakashr.r7 (3/4/2013)
What ever the process, you can't generate unique random values more than allocated right, Chris ? I am just curious...
They're not guaranteed to be unique! Run this
declare @result...
^{“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

March 4, 2013 at 7:35 am

#1593009
RE: How to uncommit the transaction and execute the next statement in the cursor?

Have you looked at alternative means of populating @result and @VendorCode in the following,
with a view to making the entire process set-based?
INSERT INTO SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr,
PnxCodeValue,...
^{“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

March 4, 2013 at 7:06 am

#1592990
RE: SELECT list block with CASE?

No, but you can cheat:
DECLARE @X TINYINT = 0
;WITH SampleTable AS (
SELECT SOMECOLUMNS = 'SOMECOLUMNS', COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3',
COLUMN4 = 'COLUMN4', COLUMN5 = 'COLUMN5', COLUMN6...
^{“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

March 4, 2013 at 6:04 am

#1592972
RE: An issue with my Execution plan

SQL Mad Rafi (3/4/2013)
I have a view.
I made some changes (included few CASEs in SELECT list for a business requirement)
i did a relative cost check between these old 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

March 4, 2013 at 5:35 am

#1592968
RE: Retrieve a substring

SELECT RIGHT('Feb 18 2013 3:35PM',7)
^{“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

March 4, 2013 at 5:13 am

#1592961
RE: Getting minimum of top n rows without using subquery

winmansoft (3/4/2013)
...
If i have table with 1000 rows and say 10th row date is '03-01-12' and all other 100 dates are above '03-01-12' and 101th row date 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

March 4, 2013 at 4:57 am

#1592955
RE: DB Normalisation Help required

ManiDBLover (3/3/2013)
...
Requirement:
1. Both tables are having duplicate values.
...
Right now by accident or in the future by design?
...
2. One Software will be used in multiple computers and one computer...
^{“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

March 4, 2013 at 3:39 am

#1592936
RE: Using an amemded @From and @To date to get a new result

wafw1971 (3/1/2013)
Thanks Chris, I have figured it out now:
Declare @From Datetime
Declare @To Datetime
Set @From = '07 Jan 2013'
Set @To = '18 Mar 2013'
SELECT Datediff(Week, dateadd(dd, CASE WHEN...
^{“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

March 1, 2013 at 8:34 am

#1592416
RE: Grouping records by time interval

geert.de.vylder (3/1/2013)
Hi ChrisM@Work,
Thank you for your query and help. But this query needed 13 minutes to run. That's quiet long. How can the performance be improved?
Greetz,
Geert
Post the actual execution plan...
^{“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

March 1, 2013 at 6:42 am

#1592345
RE: Using an amemded @From and @To date to get a new result

What happens if your initial FROMdate or TOdate happen to be a sunday? This batch shows solutions either way:
-- Recalculating startdate
;WITH SampleData AS (
SELECT TheDate = CAST('06 Jan 2013' AS...
^{“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

March 1, 2013 at 6:34 am

#1592339
RE: Execute Stored Procedure Multiple Times

BSavoie (2/28/2013)
Thanks all for the great advice. Rewriting the sproc is not an option at this time. Since this is just a wrapper around the real work horse, I guess...
^{“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

March 1, 2013 at 4:22 am

#1592309
RE: Grouping records by time interval

I reckon this does it:
SELECT mx.Ticket_Closed_Date, mx.Interval, op.*
FROM ( -- create a matrix containing all intervals from Tijdsintervallen and all dates from Tussen_Tickets
SELECT *
FROM Tijdsintervallen
CROSS JOIN (
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

March 1, 2013 at 3:36 am

#1592294

Viewing 15 posts - 5,131 through 5,145 (of 10,143 total)