ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,701 through 2,715 (of 10,143 total)

RE: Dynamic filtering of stored procedure with IN parameter

SELECT *
FROM @table t
WHERE EXISTS (SELECT 1 FROM ufn_CSVToTable(@Filter, ',') s WHERE t.csv LIKE '%'+s.String+'%')
SELECT *
FROM @table t
WHERE EXISTS (
SELECT 1 FROM ufn_CSVToTable(@Filter, ',') s1
INNER...
^{“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 21, 2015 at 7:09 am

#1771796
RE: Dynamic filtering of stored procedure with IN parameter

Bhushan Kulkarni (1/21/2015)
Accept the two parameters as comma separated string.
Create a function that will split the comma separated string and returns table.
And use that function in where clause
More or less...
^{“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 21, 2015 at 5:34 am

#1771772
RE: Performence issue same sql query with different batchdate and same size data. Kindly provide solution

The execution plans won't be identical because the queries aren't identical.
Use a variable for your date filter and you can use the exact same query (and hence exact same execution...
^{“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 21, 2015 at 2:11 am

#1771712
RE: Recursive data but increasing date by one day

Jeff Moden (1/19/2015)
@chris-2,
Agreed. You're going with what was given and I'm trying to read minds. 😛
You've been around here for a little longer than I have 😉
^{“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 19, 2015 at 8:27 am

#1771139
RE: Recursive data but increasing date by one day

Jeff Moden (1/19/2015)
Although Chris' code does what you want, I'm not sure it does what you actually need. Yes, it works correctly (haven't run it but looks correct). If...
^{“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 19, 2015 at 7:47 am

#1771116
RE: Recursive data but increasing date by one day

richardmgreen1 (1/19/2015)
Thanks for that Chris.
I might be back for some extra help when I start changing it slightly to add in all my other fields (and quite possibly for an...
^{“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 19, 2015 at 7:31 am

#1771111
RE: Performance problem when joining to a view.

Hi Dan, have a look at the execution plan, the leftmost operator, SELECT. Rightclick to raise the properties tab. Look for something like "Reason for early termination..." and post 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 19, 2015 at 7:15 am

#1771108
RE: Recursive data but increasing date by one day

Your spec is a little ambiguous and quite tricky to follow. Hopefully this will give you some ideas. iTally is an IBG-style inline tally table which will generate just 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

January 19, 2015 at 6:13 am

#1771092
RE: Rank on repeating groups of bit field

This might not be the most efficient way of solving the puzzle, but it does generate the correct results - which I always put before performance or code sophistication:
DECLARE @SAMPLE_DATA...
^{“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 19, 2015 at 2:06 am

#1771050
RE: Are the posted questions getting worse?

GilaMonster (1/16/2015)
Brandie Tarvin (1/16/2015)
GilaMonster (1/15/2015)
Sean Lange (1/15/2015)
I am having a discussion with somebody who swears that using
EXISTS(select 1
is way faster than just select *. I know Gail has an...
^{“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 16, 2015 at 6:39 am

#1770737
RE: Need suggestion for Trigger

There will be a performance impact, whether or not it's significant is a different matter - but you could test this with little effort by timing an update to a...
^{“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 15, 2015 at 7:09 am

#1770446
RE: Need suggestion for Trigger

Note that the trigger you posted will work for single-row inserts only. Something like the following will handle batch inserts/updates:
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
IF TRIGGER_NESTLEVEL() >...
^{“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 15, 2015 at 6:33 am

#1770439
RE: After Trigger not able to update multiple records with unique constraints

Please post the trigger 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

January 15, 2015 at 6:23 am

#1770436
RE: Today's Random Word!

Ed Wagner (1/15/2015)
ChrisM@Work (1/15/2015)
BWFC (1/15/2015)
richxs (1/15/2015)
Giraffe DBA (1/14/2015)
SQLRNNR (1/14/2015)
Ed Wagner (1/14/2015)
djj (1/14/2015)
BWFC (1/14/2015)
Ed Wagner (1/14/2015)
Stuart Davies (1/14/2015)
TomThomson (1/13/2015)
Ed Wagner (1/13/2015)
SQLRNNR (1/13/2015)
regex
Pattern
Match
Point
Edge
Bono
Sonny
Cher
Babe (i've got you ...)
Ruth
Sultan
Swing
Swap
Meet
Potato
^{“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 15, 2015 at 5:41 am

#1770421
RE: Error in stored procedure

Koen Verbeeck (1/15/2015)
ChrisM@Work (1/15/2015)
or resolve it in the string:
+ '
FROM employee m
LEFT JOIN shiftdetails b ON m.empno = b.empno
where b.shifttype = ''' + @shifttype +
...
^{“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 15, 2015 at 5:12 am

#1770413

Viewing 15 posts - 2,701 through 2,715 (of 10,143 total)